嵌套查询与连接 [英] nested query vs jOINs

问题描述

如果我使用嵌套子查询、JOINs 或者临时表 ..另一个问题:在子查询中，如果我对同一个查询使用两次 IN 子句，它也应该执行两次！?像这样:

whos could be much efficient if I use nestted subquery, JOINs Or maybe temp tables .. another question : in subqueries if i use IN Clause twice with the same query it should be execute a twice too !? like this :

Select ...
From X 
Where Exists( Select 1  From Y Where Idx = Y.SomeColumn ) 
Or Exists( Select 1 From Y Idy = Y.SomeColumn )

子查询SELECT * FROM Y可以在这个查询中执行多少次！
如果我使用这种方式这样做会怎样:

how many times the sub-query SELECT * FROM Y could be executed in this query !
and what if I use this way to do so :

With XX As
(
Select ...
From Y
)
Select ...
From X
Where Exists ( Select 1 From XX Where Idx = XX.SomeColumn )
Or Exists ( Select 1 From XX Where Idy = XX.SomeColumn )

谢谢:)

推荐答案

这两个查询是等效的，应该产生相同的计划.CTE 只编译一次，提供了性能优势，这是一种误解.非递归 CTE 只是派生表/内联视图的语法糖(IMO 错误地称为子查询).

The two queries are equivalent, and should produce identical plans. It's a misconception that CTEs are compiled only once, providing a performance benefit. Non-recursive CTEs are just syntactic sugar for derived tables/inline views (IMO mistakenly referred to as subqueries).

其次，JOIN 与 IN/EXISTS 会产生不同的结果.如果有两个或多个支持记录，则 JOIN 存在重复数据的风险.如果存在重复条件，最好使用 EXISTS，因为它在第一次遇到条件时返回 true - 使其可能比 IN 或 JOIN 更快.使用 EXISTS 或 IN 时没有数据重复风险.

Secondly, JOINs vs IN/EXISTS can produce different results. JOINs risk duplicated data, if there's two or more supporting records. EXISTS is best used if there are duplicate criteria, because it returns true on the first encounter of the criteria - making it potentially faster than IN or JOIN. There's no data duplication risk when using EXISTS or IN.

这篇关于嵌套查询与连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！