EXISTS vs JOIN и использование условия EXISTS

Ниже приведен пример кода:

CREATE TABLE #titles(
    title_id       varchar(20),
    title          varchar(80)       NOT NULL,
    type           char(12)          NOT NULL,
    pub_id         char(4)               NULL,
    price          money                 NULL,
    advance        money                 NULL,
    royalty        int                   NULL,
    ytd_sales      int                   NULL,
    notes          varchar(200)          NULL,
    pubdate        datetime          NOT NULL
 )
 GO

 insert #titles values ('1', 'Secrets',   'popular_comp', '1389', $20.00, $8000.00, 10, 4095,'Note 1','06/12/94')
 insert #titles values ('2', 'The',       'business',     '1389', $19.99, $5000.00, 10, 4095,'Note 2','06/12/91')
 insert #titles values ('3', 'Emotional', 'psychology',   '0736', $7.99,  $4000.00, 10, 3336,'Note 3','06/12/91')
 insert #titles values ('4', 'Prolonged', 'psychology',   '0736', $19.99, $2000.00, 10, 4072,'Note 4','06/12/91')
 insert #titles values ('5', 'With',      'business',     '1389', $11.95, $5000.00, 10, 3876,'Note 5','06/09/91')
 insert #titles values ('6', 'Valley',    'mod_cook',     '0877', $19.99, $0.00,    12, 2032,'Note 6','06/09/91')
 insert #titles values ('7', 'Any?',      'trad_cook',    '0877', $14.99, $8000.00, 10, 4095,'Note 7','06/12/91')
 insert #titles values ('8', 'Fifty',     'trad_cook',    '0877', $11.95, $4000.00, 14, 1509,'Note 8','06/12/91')
 GO


CREATE TABLE #sales(
    stor_id        char(4)           NOT NULL,
    ord_num        varchar(20)       NOT NULL,
    ord_date       datetime          NOT NULL,
    qty            smallint          NOT NULL,
    payterms       varchar(12)       NOT NULL,
    title_id       varchar(80)
)
 GO
insert #sales values('1', 'QA7442.3', '09/13/94', 75, 'ON Billing','1')
insert #sales values('2', 'D4482',    '09/14/94', 10, 'Net 60',    '1')
insert #sales values('3', 'N914008',  '09/14/94', 20, 'Net 30',    '2')
insert #sales values('4', 'N914014',  '09/14/94', 25, 'Net 30',    '3')
insert #sales values('5', '423LL922', '09/14/94', 15, 'ON Billing','3')
insert #sales values('6', '423LL930', '09/14/94', 10, 'ON Billing','2')


SELECT    title, price
FROM      #titles
WHERE     EXISTS
(SELECT   *
FROM      #sales
WHERE     #sales.title_id = #titles.title_id
AND       qty >30)


    SELECT    t.title, t.price
    FROM     #titles t
    inner join #sales s on t.title_id = s.title_id
    where s.qty >30

Я хочу знать, в чем разница между вышеуказанными 2 запросами, которые дают тот же результат. Также хотите знать цель ключевого слова EXISTS и где именно использовать?

Ответы

Ответ 1

EXISTS используется для возврата булевского значения, JOIN возвращает целую другую таблицу

EXISTS используется только для проверки того, возвращает ли подзапрос результаты и короткие замыкания, как только это произойдет. JOIN используется для расширения набора результатов путем объединения его с дополнительными полями из другой таблицы, к которой существует отношение.

В вашем примере запросы символически эквивалентны.

В общем случае используйте EXISTS, когда:

Вам не нужно возвращать данные из соответствующей таблицы
У вас есть обман в связанной таблице (JOIN может вызывать повторяющиеся строки, если значения повторяются)
Вы хотите проверить существование (используйте вместо условия LEFT OUTER JOIN...NULL)

Если у вас есть правильные индексы, большую часть времени EXISTS будет выполняться идентично JOIN. Исключение составляет очень сложные подзапросы, где обычно быстрее использовать EXISTS.

Если ваш ключ JOIN не проиндексирован, он может быть быстрее использовать EXISTS, но вам нужно будет проверить его конкретные обстоятельства.

Синтаксис

JOIN легче читать и чистить нормально.

Ответ 2

EXISTS - это полусоединение
JOIN - это соединение

Итак, с 3 строками и 5 строками, соответствующими

JOIN дает 15 строк
EXISTS дает 3 строки

Результатом является эффект "короткого замыкания", упомянутый другими, и нет необходимости использовать DISTINCT с JOIN. EXISTS почти всегда быстрее, если вы ищете существование строк на n стороне отношения 1: n.

Ответ 3

EXISTS в основном используется для быстрого доступа. По сути, оптимизатор выйдет из строя, как только условие будет истинным, поэтому может не понадобиться сканировать всю таблицу (в современных версиях SQL Server эта оптимизация может произойти и для IN(), хотя это не всегда так). Такое поведение может варьироваться от запроса к запросу, и в некоторых случаях объединение может фактически дать оптимизатору больше возможностей для выполнения своей работы. Поэтому мне трудно сказать "это, когда вы должны использовать EXISTS, и это когда вам не нужно", потому что, как и многие вещи, "это зависит".

Тем не менее, в этом случае, поскольку у вас есть по существу 1:1 совпадение между таблицами, вы вряд ли увидите какую-либо разницу в производительности, и оптимизатор, скорее всего, создаст аналогичный или даже идентичный план. Вы можете увидеть что-то другое, если вы сравниваете join/exists в таблице продаж, когда вы добавляете 50 000 строк для каждого заголовка (неважно, что вам нужно будет изменить свой запрос соединения, чтобы удалить дубликаты, заполнить, что у вас есть).

Ответ 4

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

Например,

SELECT * 
  FROM TABLE a
 WHERE a.val IN (1,2,3)
   AND NOT EXISTS(SELECT NULL
                    FROM TABLE b
                   WHERE b.id = a.id
                     AND b.val NOT IN (1, 2, 3))

В этом случае я исключаю строку в моем запросе a на основе записи b с тем же идентификатором, но недействительной.

Это на самом деле произошло из производственной проблемы, которая была у меня на работе. Запрос переместил большую часть логики исключения в запросе, а не в приложение, с временем загрузки от более чем 24 секунд до менее 2 секунд.: -)