问题描述
以下是代码示例:
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 (
JOINcan cause duplicate rows if values are repeated) - You want to check existence (use instead of
LEFT OUTER JOIN...NULLcondition)
如果您有适当的索引，大多数时候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 子句的使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！