Подзапросы и JOINы

Подзапрос (subquery) часто, но не всегда, может быть переписан с помощью JOIN.

Переписывание подзапросов как JOIN

Подзапрос, использующий IN может быть переписан с ключевым словом DISTINCT, например:

SELECT * FROM table1 WHERE col1 IN (SELECT col1 FROM table2);

может быть переписан как:

SELECT DISTINCT table1.* FROM table1, table2 WHERE table1.col1=table2.col1;

NOT IN или NOT EXISTS запросы также могут быть переписаны. Например, эти два запроса возвращают одинаковый результат:

SELECT * FROM table1 WHERE col1 NOT IN (SELECT col1 FROM table2);
SELECT * FROM table1 WHERE NOT EXISTS (SELECT col1 FROM table2 WHERE table1.col1=table2.col1);

и оба могут быть переписаны как:

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;

Подзапросы, которые могут быть переписаны как LEFT JOIN, иногда более эффективны.

Использование подзапросов вместо JOIN

Однако существуют ситуации, в которых подзапросы предпочтительнее JOIN:

• Когда вам нужны дубликаты, но не ложные дубликаты. Предположим, что Table_1 имеет три строки — {1,1,2} — и Table_2 имеет две строки — {1,2,2}. Если вам нужно вывести строки из Table_1, которые также находятся в Table_2, только этот подзапрос-базированный SELECT оператор даст правильный ответ (1,1,2):

SELECT Table_1.column_1 
FROM   Table_1 
WHERE  Table_1.column_1 IN 
  (SELECT Table_2.column_1 
    FROM   Table_2);

• Этот оператор SQL не сработает:

SELECT Table_1.column_1 
FROM   Table_1,Table_2 
WHERE  Table_1.column_1 = Table_2.column_1;

• потому что результатом будет {1,1,2,2} — и дублирование 2 является ошибкой. Этот оператор SQL также не сработает:

SELECT DISTINCT Table_1.column_1 
FROM   Table_1,Table_2 
WHERE  Table_1.column_1 = Table_2.column_1;

• потому что результатом будет {1,2} — и удаление дублирующейся 1 тоже является ошибкой.

• Когда внешнее выражение не является запросом. Оператор SQL:

UPDATE Table_1 SET column_1 = (SELECT column_1 FROM Table_2);

• не может быть выражен с помощью соединения, если не используются некоторые редкие возможности SQL3.

• Когда соединение выполняется над выражением. Оператор SQL:

SELECT * FROM Table_1 
WHERE column_1 + 5 =
  (SELECT MAX(column_1) FROM Table_2);

• сложно выразить с помощью соединения. На самом деле, единственный способ, который мы можем придумать, это оператор SQL:

SELECT Table_1.*
FROM   Table_1, 
      (SELECT MAX(column_1) AS max_column_1 FROM Table_2) AS Table_2
WHERE  Table_1.column_1 + 5 = Table_2.max_column_1;

• который по-прежнему включает в себя скобочный запрос, поэтому преобразование ничего не дает.

• Когда вы хотите увидеть исключение. Например, предположим, что вопрос звучит так: какие книги длиннее, чем «Капитал» Маркса? Эти два запроса фактически почти одинаковы:

SELECT DISTINCT Bookcolumn_1.*                     
FROM   Books AS Bookcolumn_1 JOIN Books AS Bookcolumn_2 USING(page_count) 
WHERE  title = 'Das Kapital';

SELECT DISTINCT Bookcolumn_1.* 
FROM   Books AS Bookcolumn_1 
WHERE  Bookcolumn_1.page_count > 
  (SELECT DISTINCT page_count 
  FROM   Books AS Bookcolumn_2 
  WHERE  title = 'Das Kapital');

• Разница между этими двумя операторами SQL заключается в том, что если есть два издания «Капитала» (с различным количеством страниц), то пример с самосоединением вернёт книги, которые длиннее самого короткого издания «Капитала». Это может быть неправильным ответом, так как исходный вопрос не спрашивал «… длиннее, чем книга под названием «Капитал»» (он, кажется, содержит ложное предположение о том, что существует только одно издание).