CODEWITHSUNDEEP
sqldatetimesubqueryleft-join
palace
palace

Reputation: 11

SQL - List all customers that we did not make a sale to in the year 1996

This is my functioning SQL query to return the customers that we sold to in 1996:

   SELECT C.CustomerID, C.CompanyName
   FROM Customers C, Orders O
   WHERE C.CustomerID = O.CustomerID AND YEAR(O.OrderDate) = 1996
   GROUP BY C.CustomerID, C.CompanyName
   ORDER BY C.CustomerID

Now I'm trying to show the opposite; return all customers that we did not sell to in 1996 (even if we did sell to them in other years). This is what I have, however it returns both the customers we didn't sell to in 1996 but also the ones we did:

SELECT C.CustomerID, C.CompanyName FROM Orders O JOIN Customers C
ON O.CustomerID = C.CustomerID 
WHERE YEAR(O.OrderDate) != 1996
GROUP BY C.CustomerID, C.CompanyName
ORDER BY C.CustomerID

Upvotes: 0

Views: 616

Answers (3)

GMB
GMB

Reputation: 222442

We can build on your existing query and use the left join antipattern:

SELECT C.CustomerID, C.CompanyName
FROM Customers C
LEFT JOIN Orders O
    ON  C.CustomerID = O.CustomerID 
    AND O.OrderDate >= '1996-01-01'
    AND O.OrderDate <  '1997-01-01'
WHERE O.CustomerID IS NULL
ORDER BY C.CustomerID

This phrases as : try to join each customers with the orders they have placed in 1996, and filter on those without any order.

Side note:

  • always use explicit, standard join (with the ON keyword); old-school, implicit joins should be avoided (no comma in the FROM clause)

  • as also commented by sticky bit (whose answer is valid and I upvoted it), using date comparison is better form performance than relying on date functions

Upvotes: 2

sticky bit
sticky bit

Reputation: 37472

You can use a correlated subquery that gets the orders from 1996 of a customer with NOT EXISTS.

SELECT c.customerid,
       c.companyname
       FROM customers c
       WHERE NOT EXISTS (SELECT *
                                FROM orders o
                                WHERE o.customerid = c.customerid
                                      AND o.orderdate >= '1996-01-01'
                                      AND o.orderdate < '1997-01-01');

Note that you better shouldn't use year() on orderdate as this can prevent indexes form being used, so slowing down the query.

Upvotes: 2

zip
zip

Reputation: 4061

try this

SELECT C.CustomerID, C.CompanyName FROM Customers C
WHERE 
not exists(select 1 FROM Orders O where O.CustomerID = C.CustomerID and YEAR(O.OrderDate) = 1996)
ORDER BY C.CustomerID

Upvotes: 0

Related Questions