Difference between subquery and correlated subquery for given code

Question

Example for correlated subquery given in a book is as follows;

Customers who placed orders on February 12, 2007

SELECT custid, companyname
FROM Sales.Customers AS C
WHERE EXISTS
  (SELECT *
   FROM Sales.Orders AS O
   WHERE O.custid = C.custid
     AND O.orderdate = '20070212');

But, I wrote following code for the same purpose using simple subquery

SELECT custid, companyname
FROM Sales.Customers
WHERE custid IN
    (SELECT [custid] FROM [Sales].[Orders]
    WHERE [orderdate] ='20070212')

Both gives identical output. Which method is better? and why? and I do not understand the use of EXISTS here in the first set of codes

You can read here(http://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance), depending on the version of SQL Server you are using, the performance might be different. As you can read in the answers and comments, as of 2008 the optimizer will treat both IN and EXISTS equally. — Rigerta, Apr 18 '17 at 12:43
EXISTS evaluates the select, if the SELECT returns rows, then EXISTS evaluates to TRUE, else to FALSE. The method which is most efficient might be the best ;) To know what is done differently you will have to study the execution plan of the queries. It might well be that you get the same execution plan, which would mean that the two queries are in effect identical. — Søren Kongstad, Apr 19 '17 at 07:43

score 1 · Answer 1 · answered Apr 19 '17 at 08:06

I tried similar queries on my own data on SQL Server 2016 SP!:

select
*
from EXT.dbo_CUSTTABLE
where ACCOUNTNUM in 
(select CUSTACCOUNT from EXT.dbo_SALESLINE  b 
  where b.CREATEDDATETIME between '20170101 00:00' and '20170102 23:59');

select
*
from EXT.dbo_CUSTTABLE a
where exists 
(select * from EXT.dbo_SALESLINE b 
  where a.ACCOUNTNUM=b.CUSTACCOUNT 
    and b.CREATEDDATETIME between '20170101 00:00' and '20170102 23:59');

Look at the execution plans, they are identical!

If I add a clustered index on the customer table, and an index on the salesline, we get a more efficient query, with index seek and inner join, in stead of table scans and hash joins, but still identical!:

Now if you are using another version of SQL server youre results may vary, since the query optimizer changes between versions.

Difference between subquery and correlated subquery for given code

1 Answers1