EXISTS 与 JOIN 以及 EXISTS 子句的使用 [英] EXISTS vs JOIN and use of EXISTS clause

问题描述

以下是代码示例:

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 关键字的用途以及具体使用在哪里?

I want to know what is the difference between the above 2 queries which gives the same result.Also want to know the purpose of EXISTS keyword and where exactly to use?

推荐答案

EXISTS 用于返回一个布尔值，JOIN 返回整个其他表

EXISTS is used to return a boolean value, JOIN returns a whole other table

EXISTS 仅用于测试子查询是否返回结果，并在返回后立即短路.JOIN 用于扩展结果集，方法是将结果集与另一个有关系的表中的附加字段相结合.

EXISTS is only used to test if a subquery returns results, and short circuits as soon as it does. JOIN is used to extend a result set by combining it with additional fields from another table to which there is a relation.

在您的示例中，查询在语义上是等效的.

In your example, the queries are semantically equivalent.

通常，在以下情况下使用 EXISTS:

In general, use EXISTS when:

• 您不需要从相关表中返回数据
• 您在相关表中有重复项(如果值重复，JOIN 可能导致重复行)
• 您想检查是否存在(使用而不是 LEFT OUTER JOIN...NULL 条件)

• You don't need to return data from the related table
• You have dupes in the related table (JOIN can cause duplicate rows if values are repeated)
• You want to check existence (use instead of LEFT OUTER JOIN...NULL condition)

如果您有适当的索引，大多数时候EXISTS 的表现将与JOIN 相同.例外情况是非常复杂的子查询，通常使用 EXISTS 会更快.

If you have proper indexes, most of the time the EXISTS will perform identically to the JOIN. The exception is on very complicated subqueries, where it is normally quicker to use EXISTS.

如果您的 JOIN 键未编入索引，使用 EXISTS 可能会更快，但您需要针对您的特定情况进行测试.

If your JOIN key is not indexed, it may be quicker to use EXISTS but you will need to test for your specific circumstance.

JOIN 语法通常也更容易阅读和清晰.

JOIN syntax is easier to read and clearer normally as well.

这篇关于EXISTS 与 JOIN 以及 EXISTS 子句的使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！