Replace Exists with Join

Question

Hi Everyone, can we replace 'EXISTS' with Join in the below query

SELECT custid, companyname

FROM Sales.Customers AS C

WHERE EXISTS

  (SELECT *

   FROM Sales.Orders AS O

   WHERE O.custid = C.custid

     AND O.orderdate >= '20070101'

     AND O.orderdate < '20080101')

  AND NOT EXISTS

  (SELECT *

   FROM Sales.Orders AS O

   WHERE O.custid = C.custid

     AND O.orderdate >= '20080101'

     AND O.orderdate < '20090101');

Answer 1

The EXISTS() could only be replaced, when the cardinality of the relationship is 1:1/0. The NOT EXISTS() can be replaced.

E.g.

SELECT custid, companyname
FROM Sales.Customers AS C
    LEFT JOIN Sales.Orders AS O ON O.custid = C.custid
                                     AND O.orderdate >= '20080101'
                                     AND O.orderdate < '20090101'
WHERE EXISTS
    (
        SELECT  *
        FROM    Sales.Orders AS I
        WHERE   I.custid = C.custid
            AND I.orderdate >= '20070101'
            AND I.orderdate < '20080101'
    )
    AND O.custid IS NULL;

Answer 2

another shorthand way of writing the same logic is this

SELECT custid, companyname
FROM Sales.Customers AS C
GROUP BY custId,companyname
HAVING SUM(CASE WHEN orderdate >= '20070101'
AND orderdate < '20080101' THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN orderdate >= '20080101'
AND orderdate < '20090101' THEN 1 ELSE 0 END) = 0

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page