Предикат соответствия

Функция

Определение условия соответствия проверяемой записи c результатом табличного подзапроса.

Спецификация
       
< предикат соответствия >::=
< проверяемая запись >::=
< тип совпадения >::=
[ UNIQUE ] [ SIMPLE | PARTIAL | FULL ]
Синтаксические правила
  1. Количество элементов в < проверяемой записи > должно совпадать с количеством элементов в < сличаемых записях >.

  2. Типы данных элементов в < проверяемой записи > должны быть совместимы с типами соответствующих элементов < сличаемых записей >.

  3. Сравнение пар соответствующих элементов производится аналогично < предикату сравнения >.

  4. Опция UNIQUE запрещает использовать для проверки совпадения дубликаты < сличаемых записей >.

  5. Опции SIMPLE (обычное совпадение), PARTIAL (частичное) и FULL (полное) задают условия совпадения записей, имеющих NULL-значения.

Общие правила
  1. < Предикат соответствия > возвращает TRUE, если < проверяемая запись > совпадает по заданному критерию с одной из < сличаемых записей >, в противном случае – FALSE (таблица 1).

Таблица 1. Возвращаемые значения < предиката соответствия >
Тип совпаденияПроверяемая записьОпция UNIQUEРезультат
SIMPLE или не заданСодержит только NULL-значенияЗаданаTRUE
Не заданаTRUE
Содержит реальные и NULL-значенияЗаданаTRUE
Не заданаTRUE
Содержит реальные значенияЗаданаTRUE, если среди < сличаемых записей > есть уникальная запись, совпадающая с < проверяемой записью >, иначе FALSE
Не заданаTRUE, если среди < сличаемых записей > есть, возможно, не уникальная запись, совпадающая с < проверяемой записью >, иначе FALSE
PARTIALСодержит только NULL-значенияЗаданаTRUE
Не заданаTRUE
Содержит реальные и NULL-значенияЗаданаTRUE, если среди < сличаемых записей > есть уникальная запись, реальные значения которой совпадают с соответствующими реальными значениями < проверяемой записи >, иначе FALSE
Не заданаTRUE, если среди < сличаемых записей > есть, возможно, не уникальная запись, реальные значения которой совпадают с соответствующими реальными значениями < проверяемой записи >, иначе FALSE
FULLСодержит только NULL-значенияЗаданаTRUE
Не заданаTRUE
Содержит реальные и NULL-значенияЗаданаFALSE
Не заданаFALSE
Содержит реальные значенияЗаданаTRUE, если среди < сличаемых записей > есть уникальная запись, полностью совпадающая c < проверяемой записью >, иначе FALSE
Не заданаTRUE, если среди < сличаемых записей > есть, возможно, не уникальная запись, полностью совпадающая c < проверяемой записью >, иначе FALSE

Примеры
  1. create or replace table t1 (i1 int, i2 int, i3 int);
    create or replace table t2 (i1 int, i2 int, i3 int);
    
    insert into t1 (i1,i2,i3) values (1,2,3);
    insert into t1 (i1,i2,i3) values (1,2,4);
    insert into t1 (i1,i2,i3) values (1,1,3);
    insert into t1 (i1,i2,i3) values (1,2,3);
    insert into t1 (i1,i2,i3) values (1,2,5);
    
    insert into t2 (i1,i2,i3) values (1,2,3);
  2. Эти 3 запроса идентичны:

    select count(*) from t1 where (1,2,3) match (select * from t2);
    select count(*) from t1 where (1,2,3) match (values (1,2,3));
    select count(*) from t1 where (1,2,3) match (table t2);
    |       5|
  3. select count(*) from t1 where (t1.i1,t1.i2,t1.i3) match (select * from t2);
    |       2|
  4. select count(*) from t1 where (t1.i1,t1.i2,t1.i3) not match (select * from t2);
    |       3|
  5. select count(*) from t1 where (t1.i1,t1.i2,t1.i3) match full (select * from t2);
    |       2|
  6. Эти 3 запроса идентичны:

    select count(*) from t1 where (t1.i1,t1.i2,t1.i3) match partial (select * from t2);
    
    select count(*) from t1 where (t1.i1,t1.i2,t1.i3) match partial (values (cos(0),2,sqrt(9)));
    
    select count(*) from t1 where (t1.i1,t1.i2,t1.i3) match partial (table t2);
    |       2|
  7. select count(*) from t1 where (t1.i1,t1.i2,t1.i3) match unique (select * from t2);
    |       2|
  8. select count(*) from t1 where (1,NULLIF(0,0),3) match FULL (select * from t2);
    |       0|
  9. select count(*) from t1 where (1,NULLIF(0,0),3) match simple (select * from t2);
    |       5|
  10. select count(*) from t1 where (1,NULLIF(0,0),3) match partial (select * from t2);
    |       5|
  11. create or replace table emp(emp_no int, dept_no int, emp_surname char(50), emp_name char(50));
    insert into emp values (1,1,'ИВАНОВ','ИВАН');
    insert into emp values (2,NULL,'ПЕТРОВ','ПЕТР');
    insert into emp values (3,1,'ПУПКИН','ВАСИЛИЙ');
    insert into emp values (4,1,'ИВАНОВ','СЕРГЕЙ');
    insert into emp values (5,2,'СИДОРОВ','АНТОН');

Запрос вернет данные о служащих, которые:

  • либо не приписаны к какому-нибудь отделу;

  • либо в своем отделе имеют однофамильцев.

select emp_surname, dept_no
  from emp
 where (dept_no, emp_surname) match unique simple
    (select emp1.dept_no, emp1.emp_surname
       from emp emp1
      where emp1.emp_no <  > emp.emp_no);