Предикат внешнего соединения в стиле ORACLE

Функция

Определение внешнего соединения в стиле СУБД ORACLE.

Спецификация
       
< предикат внешнего соединения в стиле ORACLE >::=
< соединение слева >::=
< соединение справа >::=
< двустороннее соединение >::=
Синтаксические правила
  1. При отсутствии скобок соединение таблиц выполняется слева направо.

  2. Посредством внешнего соединения нельзя соединять одну и ту же таблицу (c оператором (+)) более чем c одной таблицей в одном предложении SELECT (будет выдано сообщение 1079 – «Неверная операция JOIN»). С другой стороны, несколько разных таблиц (c оператором (+)) могут быть соединены с одной таблицей.

    Допустимая конструкция:

    …where tab1(+)=tab and tab2(+)=tab

    Недопустимая конструкция:

    …where tab1=tab(+)  and tab2=tab(+)…
  3. Две таблицы не могут быть соединены друг с другом одновременно и через простое, и через внешнее соединения, т.е. конструкция вида:

    SELECT * FROM TAB1 T1, TAB1 T2
     WHERE T1.I = T2.I(+) AND T1.J = T2.J;

    является недопустимой.

  4. Рекомендуется не использовать в одном условии и внешнее соединение в стиле Oracle, и вызов хранимой процедуры (это может вызвать конфликт в механизме обработки запросов СУБД ЛИНТЕР).

  5. Столбец с (+) может стоять под скалярной функцией или в выражении, но он обязательно должен быть один в своей части предиката.

    create table departments(d_id int, d_name char(20));
    insert into departments values (1, 'Sales');
    insert into departments values (2, 'IT-technologies');
    insert into departments values (3, 'Finance');
    insert into departments values (4, 'Management');
    insert into departments values (5, 'Design');
    --
    create table persons(p_id int, p_name char (20), d_id int);
    insert into persons values (1, 'John',  3);
    insert into persons values (2, 'Mary',  2);
    insert into persons values (3, 'Kate',  4);
    insert into persons values (4, 'Jack',  2);
    insert into persons values (5, 'Peter', 7);
    insert into persons values (6, 'Ann',   5);
    --
    SELECT p.p_name, d.d_name
      FROM persons p, departments d
     WHERE p.d_id = d.d_id(+) + 1;
Общие правила
  1. Внешнее соединение расширяет результат простого соединения: к строкам, возвращаемым простым соединением, добавляются строки из одной таблицы, которые не совпадают со строками другой таблицы.

  2. Результатом < значимого выражения >, относящегося к конструкции (+), должно быть значение (возможно, модифицированное) столбца таблицы. Другое < значимое выражение > внешнего соединения может быть произвольным.

    SELECT distinct auto.personid
      FROM auto, person 
     WHERE auto.make (+) like 'FO%'
       AND color='BLACK';
    |  20|
    |  53|
    | 102|
    …
    create or replace table tab1 (i int);
    insert into tab1 values(1);
    insert into tab1 values(2);
    insert into tab1 values(3);
    insert into tab1 values(4);
    create or replace table tab2 (i int);
    insert into tab2 values(2);
    insert into tab2 values(4);
    insert into tab2 values(5);
    insert into tab2 values(7);
    
    
    SELECT tab1.i, tab2.i
      FROM tab1, tab2 
     WHERE tab1.i(+) = length(to_char(tab2.i, '99'))
       AND tab2.i  > length(to_char(tab2.i, '99'));
    |1 |2 |
    |1 |4 |
    |1 |5 |
    |1 |7 |
  3. Если таблица, помеченная оператором (+), не содержит ни одной строки, совпадающей со строкой другой таблицы, то в результирующей строке генерируются пустые значения для столбцов этой таблицы. Простое соединение не возвращает таких строк.

    SELECT tab1.i, tab2.i
      FROM tab1, tab2 
     WHERE tab1.i(+)=length(to_char(tab2.i,'99'))+5;
    |NULL |2 |
    |NULL |4 |
    |NULL |5 |
    |NULL |7 |

    Внешнее соединение по константному выражению (для соблюдения синтаксиса используется конструкция вычитания значений одного и того же столбца):

    create or replace table tab1 (i int, j int);
    create or replace table tab2 (ref_tab1 int, k int);
    insert into tab1 values (1,1);
    insert into tab2 values (1,2);
    insert into tab2 values (1,3);
    
    select tab1.i, tab2.ref_tab1, tab2. k
      from tab1, tab2
     where tab1.i=1
       and tab1.i=tab2.ref_tab1(+)
       and 0 + tab1.i-tab1.i=tab2.k(+);
    | 1 | NULL| NULL|