nested query vs jOINs

Question

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 )

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 )

thanx :)

Answer 1

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).

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.

Answer 2

Use the execution plan in SQL Server Management Studio and see for yourself what runs faster against your database.

Answer 3

First, your syntax is probably incorrect.Thus, the two formats would look like:

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

And

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 )

Note the Exists statements. They are not Where Col Exists(... but instead are just Where Exists( ....

Second, the efficiency and speed will depend on the data, statistics, indexes and, at the end of the day, what the optimizer is able to make more efficient. Thus, you really need to look at the execution plan to know which is faster. Now, another form might be:

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