Соединения таблиц в предложении SELECT

Естественные соединения

Для естественного закрытого соединения действует формулировка, подобная следующей:

SELECT e.ename, d.dname 
FROM   emp e 
       NATURAL INNER JOIN
       dept d
;

Фактически она работает как INNER JOIN … USING по всем столбцам с совпадающими именами. Эта формулировка соблазнительна в силу своей простоты, однако в SQL она не поощряется некоторыми специалистами как упускающая контроль над фактическим набором столбцов соединнения (ведь имена столбцов могут совпасть случайно и безотносительно к намерению разработчика БД служить средством соединения). А в реляционной модели такая формулировка соответствует единственно допустимой форме соединения и не имеет проблем потери контроля над способом соединения.

В то же время операция NATURAL INNER JOIN имеет относительную самостоятельность, хотя несколько превратно, но все же унаследованную от реляционной теории; ее можно использовать, если следить за именами столбцов соединяемых таблиц. Если столбцы таблиц вовсе не имеют совпадающих имен (в SQL!), то эта операция превращается в декартово произведение. Например, в таблице SALGRADE в схеме SCOTT три столбца: GRADE, LOSAL и HISAL и пять строк. Вот что даст естественное соединение этой таблицы с DEPT:

SQL> SELECT dname FROM dept NATURAL INNER JOIN salgrade;
DNAME
--------------
ACCOUNTING
ACCOUNTING
ACCOUNTING
ACCOUNTING
ACCOUNTING
RESEARCH
RESEARCH
RESEARCH
RESEARCH
RESEARCH
SALES
SALES
SALES
SALES
SALES
OPERATIONS
OPERATIONS
OPERATIONS
OPERATIONS
OPERATIONS
20 rows selected.

В таких случаях ответ будет совпадать с результатом действия другой операции, CROSS JOIN:

SELECT dname FROM dept CROSS JOIN salgrade;

Если же наоборот, все столбцы естественно соединяемых таблиц совпадают, операция фактически превращается в пересечение строк таблиц, как в INTERSECT. Например:

SQL> SELECT dname, loc FROM dept a NATURAL INNER JOIN dept b;
DNAME          LOC
-------------- -------------
ACCOUNTING     NEW YORK
RESEARCH       DALLAS
SALES          CHICAGO
OPERATIONS     BOSTON

Обратите внимание на неочевидное обстоятельство. Если в последнем запросе отказаться от псевдонимов, отсева повторений не происходит; значения считаются разными:

SQL> SELECT dname, loc FROM dept NATURAL INNER JOIN dept;
DNAME          LOC
-------------- -------------
ACCOUNTING     NEW YORK
ACCOUNTING     NEW YORK
ACCOUNTING     NEW YORK
ACCOUNTING     NEW YORK
RESEARCH       DALLAS
RESEARCH       DALLAS
RESEARCH       DALLAS
RESEARCH       DALLAS
SALES          CHICAGO
SALES          CHICAGO
SALES          CHICAGO
SALES          CHICAGO
OPERATIONS     BOSTON
OPERATIONS     BOSTON
OPERATIONS     BOSTON
OPERATIONS     BOSTON
16 rows selected.

Заметьте, что особый эффект последней формулировки исчезает, когда соединяются две разные таблицы, пусть с одинаковой структурой:

SQL> CREATE TABLE dept1 AS SELECT * FROM dept;
Table created.
SQL> SELECT * FROM dept NATURAL INNER JOIN dept1;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Естественными могут быть и полуоткрытые соединения:

SELECT e.ename, d.dname
FROM   emp e
       NATURAL LEFT OUTER JOIN
       dept d
;
SELECT e.ename, d.dname 
FROM   emp e 
       NATURAL RIGHT OUTER JOIN
       dept d 
;

Оговорки употребления, сделанные для естественного внутреннего соединения (лаконичность формулировки и риски человеческих ошибок), распространяются и на эти случаи.

Дополнительные примеры формулировок

Специальный синтаксис записи соединения не препятствует наличию в предложении SELECT фразы отбора строк WHERE:

SELECT e.ename, d.dname
FROM   emp e 
       FULL OUTER JOIN
       dept d
       USING ( deptno )
WHERE  deptno  <> 10 
  AND  d.dname <> 'RESEARCH'
;

Пример многократного (здесь — двойного) соединения:

SELECT m.ename, d.dname, d.loc 
FROM   emp m
       INNER JOIN
       ( emp e 
         INNER JOIN
         dept d 
         USING ( deptno )
        )
        ON ( e.empno = m.mgr )
;

Последнее равносильно запросу

SELECT m.ename, d.dname, d.loc 
FROM   emp m 
       INNER JOIN
          emp e 
          INNER JOIN
          dept d 
          USING ( deptno )
       ON ( e.empno = m.mgr )
;

Следующая формулировка отличается только внешним видом, но может показаться более понятной программисту:

SELECT m.ename, d.dname, d.loc
FROM   emp e
         INNER JOIN
            dept d
            USING ( deptno )
                INNER JOIN
                   emp m  
                   ON ( e.empno = m.mgr )
;

Применение в одном запросе многократных соединений омрачается ухудшением читаемости и падением скорости обработки. Читаемость запроса можно пытаться "спасти" с помощью аппарата view и разложением на подзапросы (и то и другое рассматривается ниже), а скорость обработки в Oracle обычно заметно падает при числе соединений пять и более.

Наконец, наличие соединения во фразе FROM не препятствует указанию других источников данных обычным образом. Следующий запрос не имеет практического смысла и приводится только для показа самой возможности:

SELECT *
FROM   dept
       NATURAL INNER JOIN
       dept1
     , emp
WHERE  loc <> 'NEW YORK'
;

Заметьте, однако, что здесь появилось декартово произведение, так что трудность приведения содержательного примера возникла не случайно. С формальной же точки зрения вместо перечисления через запятую тут можно (и более предпочтительно) применить CROSS JOIN.

Вольности синтаксиса: ключевые слова INNER и OUTER необязательны и не влияют на смысл операции соединения в SQL; круглые скобки для логического условия после ON необязательны.

Фирма Oracle рекомендует использовать для записи соединений рассмотренный синтаксис (и, например, не рекомендует для полуоткрытых соединений применять обозначение '( + )'). Эту рекомендацию можно дополнить советами использовать ON вместо USING (там, где это возможно) и применять NATURAL JOIN с крайней осмотрительностью.

В целом же приведенный синтаксис для соединений — вероятно, одно из самых удачных решений в SQL, особенно если учесть вдобавок сферу его употребления: ведь в 99% случаев, если выразиться фигурально, запрос более чем к одной таблице в SQL будет ничем иным, как соединением (или в оставшемся 1% случаев — декартовым произведением!).