Difference between two Tsql queries

Question

Which one is the better query and what is the difference between both

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



    SELECT custid, companyname
    FROM Sales.Customers AS C
    INNER JOIN Sales.Orders AS O
    on O.custid = C.custid
    AND O.orderdate = '20180212'

The second query may return more rows. (If a customer has several orders.) — jarlh, Oct 25 '18 at 07:11
The accepted answer explains what is the difference between exists and inner join — Zohar Peled, Oct 25 '18 at 07:14

score 0 · Accepted Answer · answered Oct 25 '18 at 07:14

Both queries may in fact have identical execution plans, depending on the data and the database. The major structural difference between these two queries is that the join version could result in a given customer record being duplicated some number of times, if it matched to more than one order on the given date. This duplication would not happen with the EXISTS query. So, to make the second join version identical, you could SELECT DISTINCT:

SELECT DISTINCT custid, companyname
FROM Sales.Customers c
INNER JOIN Sales.Orders o
    ON o.custid = c.custid
WHERE o.orderdate = '20180212';

EXISTS queries can often be the most efficient way to express a query. Which of these two versions you use depends on which you prefer, what output you want, and maybe which version seems to have a better execution plan.

Difference between two Tsql queries

1 Answers1