Предикат вхождения
Функция
Определение условия вхождения.
Спецификация
::=
::=
::=
::=
::=
::=
Синтаксические правила
-
<Множество1>
не должно быть пустым.SELECT make, count(*) FROM Auto WHERE (color) IN ('BLACK','BLUE','GREEN') GROUP BY make; SELECT make, count(*) FROM Auto WHERE (make, color) IN (values('FORD','GREEN')) GROUP BY make; |FORD | 5|
create or replace table tst (search_make char(20),search_color char(10)); insert into tst (search_make, search_color) values ('FORD', 'RED'); select * from auto where (make,color) in (table tst);
-
Если
<множество1>
состоит только из одного<значимого выражения>
, ограничивающие его круглые скобки не обязательны.select model from auto where color in ('BLACK', 'WHITE') and cylnders in (6,8,12);
-
<Множество2> может быть пустым.
SELECT count(*) FROM Auto WHERE (color) not IN (); | 1000|
-
Типы данных элементов <множества1> и <множества2> должны быть совместимы.
SELECT Name FROM Person WHERE PersonID IN (SELECT PersonID FROM Auto);
-
Если <множество1> содержит только один элемент, то количество элементов <множества 2> может быть произвольным.
-
Если <множество2> не пусто, то <подзапрос сравнения> должен возвращать одну запись выборки данных, при этом количество столбцов записи выборки данных этого подзапроса должно совпадать с количеством элементов <множества 2>.
select count(*) from auto where (select $$$S31 from $$$USR where $$$s34 like 'SYS_') in (select $$$S31 from $$$USR); | 0|
Недопустимый запрос:
SELECT make, count(*) FROM Auto WHERE (color) IN (values('RED','GREEN')) GROUP BY make;
-
Тип данных <множества1> должен соответствовать типам данных <множества2>. Сравнение одного NULL-значения с другим NULL-значением выдает
FALSE
, поэтому такие записи в выборку данных не попадают.select count(*) from auto where (make, year) in ( 'FORD', 70); create table tab1(i int autoinc, c char(10), dt date); insert into tab1 (c, dt) values( 'abcdef', '01.01.2003'); insert into tab1 (c, dt) values( 'bcdefa', '02.01.2003'); insert into tab1 (c, dt) values( 'bcdefab', null); select * from tab1; |1|abcdef|01.01.2003:00:00:00.00| |2|bcdefa|02.01.2003:00:00:00.00| |3|cdefab|NULL |
select * from tab1 where (i,c, dt) in (select i,c, dt from tab1 where c like'_cde%'); |2 |bcdefa |02.01.2003:00:00:00.00 | (записи с NULL-значениями в выборку данных не включаются) select * from tab1 where (i,c, dt) in (select i,c, dt from tab1); |1|abcdef |01.01.2003:00:00:00.00 | |2|bcdefab|02.01.2003:00:00:00.00 |
-
<Подзапрос вхождения>
может возвращать любое число записей выборки данных, при этом количество столбцов записи выборки данных этого подзапроса должно совпадать с количеством элементов<множества1>
.select count(*) from auto where (1, 2, personid) in (select 1, 2, 7 union select 1, 2, 240 union select 1, 2, 500); | 3|
SELECT make, count(*) FROM Auto WHERE (color) IN (values('RED'),('GREEN')) GROUP BY make;
-
<Список вхождения>
может содержать одновременно<спецификацию значения>
и<значимое выражение>
.select personid, depndnts from person where cast personid as real in (9e0, cast (depndnts+2) as real - 1e0) and personid < 11; |2| 1| |9| 2|
Общие правила
-
Пусть
<множество1>={x1}
,<множество2>={y1,y2,…yn}
. Значение предиката равноTRUE
в том и только в том случае, когдаx1
равно одному из значений{y1,y2,…yn}
, т.е.(x1=yi)
. -
Пусть
<множество1>={x1,x2, ...xn}
,<множество2>={y1,y2,…yn}
. Значение предиката равноTRUE
, когда:-
размерность
<множества1>
и<множества2>
одинакова; -
xi
равно строгоyi
, т.е. все элементы<множества1>
содержатся в<множестве2>
, причём в том же порядке, в каком они расположены в<множестве1>
(упорядоченное пересечение множеств не пусто).Сравните: select count(*) from auto where (1, 2, personid) in (select 1, 2, 7 union select 1, 2, 240 union select 1, 2, 500); | 3|
select count(*) from auto where (1, 2, personid) in (select 1, 2, 7 union select 1, 240, 2 union select 500, 1, 2); | 1|
-
-
Если единственный элемент
<множества1>
не совпадает с каким-либо элементом<множества2>
(x1
не равенyi
), значение предиката равноFALSE
(пересечение множеств пусто). -
Если группа элементов
<множества1>
не совпадает с группой элементов<множества2>
, т.е. один или несколькоxi
не равны соответствующимyi
, значение предиката равноFALSE
(пересечение множеств пусто). -
Если
<множество2>
пусто, значение предиката всегда равноFALSE
.select count(*) from auto where model in(); | 0|
select count(*) from auto where model not in(); | 1000|
-
<Запрос вхождения>
фактически выполняется отдельно для каждого значения-кандидата основного запроса, и значения, которые он сформирует, будут составлять список значений<множества2>
для этой записи.Примеры:
-
select count(*) from person where (30, personid) in (select age, personid from person);
Подзапрос (select age, personid from person) выполняется столько раз, сколько записей в таблице person (в данном примере – 1000 раз).
-
select count(*) from person where (30) in (select age from person); | 1000|
Предикат (30) in (select age from person) всегда возвращает TRUE (поэтому в count(*) попадают все записи таблицы person), т.к. подзапрос (select age from person) предиката не связан с основным запросом какими-либо условиями.
-
Подсчитать количество офис-менеджеров старше 30 лет, владеющих автомобилями от концерна FORD:
select count(*) from auto where auto.personid in (select distinct personid from person where age > 30 and job='OFFICE MANAGER' and auto.make='FORD'); | 3|
-
-
Конструкция:
<значимое выражение> NOT IN …
имеет тот же результат, что и конструкция
NOT <значимое выражение> IN
… .Эти конструкции эквивалентны:
select name, count(*) from person where age not in (30,40,50,60) group by name; select name, count(*) from person where not age in (30,40,50,60) group by name;