locked
Replace Exists with Join RRS feed

  • 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');

    Saturday, March 17, 2018 9:09 PM

Answers

  • 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;

    • Marked as answer by gangarocks Saturday, March 17, 2018 10:39 PM
    Saturday, March 17, 2018 9:33 PM

All replies

  • 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;

    • Marked as answer by gangarocks Saturday, March 17, 2018 10:39 PM
    Saturday, March 17, 2018 9:33 PM
  • 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

    Sunday, March 18, 2018 3:30 AM