Предикат вхождения

Функция

Определение условия вхождения.

Спецификация
 
Синтаксические правила
  1. <Множество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);
  2. Если <множество1> состоит только из одного <значимого выражения>, ограничивающие его круглые скобки не обязательны.

    select model from auto where color in ('BLACK', 'WHITE') and cylnders in (6,8,12);
  3. <Множество2> может быть пустым.

    SELECT  count(*) FROM Auto WHERE (color) not IN ();
    |            1000|
  4. Типы данных элементов <множества1> и <множества2> должны быть совместимы.

    SELECT Name FROM Person WHERE PersonID IN (SELECT PersonID FROM Auto);
  5. Если <множество1> содержит только один элемент, то количество элементов <множества 2> может быть произвольным.

  6. Если <множество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;
  7. Тип данных <множества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 |
  8. <Подзапрос вхождения> может возвращать любое число записей выборки данных, при этом количество столбцов записи выборки данных этого подзапроса должно совпадать с количеством элементов <множества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;
  9. <Список вхождения> может содержать одновременно <спецификацию значения> и <значимое выражение>.

    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. Пусть <множество1>={x1}, <множество2>={y1,y2,…yn}. Значение предиката равно TRUE в том и только в том случае, когда x1 равно одному из значений {y1,y2,…yn}, т.е. (x1=yi).

  2. Пусть <множество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|
  3. Если единственный элемент <множества1> не совпадает с каким-либо элементом <множества2> (x1 не равен yi), значение предиката равно FALSE (пересечение множеств пусто).

  4. Если группа элементов <множества1> не совпадает с группой элементов <множества2>, т.е. один или несколько xi не равны соответствующим yi, значение предиката равно FALSE (пересечение множеств пусто).

  5. Если <множество2> пусто, значение предиката всегда равно FALSE.

    select count(*) from auto where model in();
    |            0|
    select count(*) from auto where model not in();
    |            1000|
  6. <Запрос вхождения> фактически выполняется отдельно для каждого значения-кандидата основного запроса, и значения, которые он сформирует, будут составлять список значений <множества2> для этой записи.

    Примеры:

    1. select count(*) from person
       where (30, personid) in (select age, personid from person);

      Подзапрос (select age, personid from person) выполняется столько раз, сколько записей в таблице person (в данном примере – 1000 раз).

    2. 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) предиката не связан с основным запросом какими-либо условиями.

    3. Подсчитать количество офис-менеджеров старше 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|
  7. Конструкция:

    <значимое выражение> 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;