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

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

Источник

4 ответа

Решение

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

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

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

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

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

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

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

JOIN синтаксис также легче читать и понятнее.

Источник
  • EXISTS является полусоединением
  • JOIN - это соединение

Так что с 3 рядами и 5 строками соответствия

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

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

Источник

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

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

Источник

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

Например,

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 секунд.:-)

Источник
Другие вопросы по тегам