CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlfiltering
eabasguliyev
eabasguliyev

Reputation: 11

Filter customers without using subqueries in SQL

How can I find customers who didn't buy any books in the last 6 month without using subqueries.

SELECT first_name, last_name, email
FROM customers
WHERE id NOT IN (
    SELECT customers.id
    FROM customers
    LEFT JOIN orders ON orders.customer_id = customers.id
    WHERE DATEDIFF(MONTH, orders.purchased_date, GETDATE()) < 6
    GROUP BY customers.id
);

Upvotes: 0

Views: 430

Answers (3)

Dale K
Dale K

Reputation: 27290

Why not use a sub-query? Its the best tool for the job here. You can improve your query (while still using a sub-query) with NOT EXISTS rather than IN and by not applying the DATEDIFF function directly to your column, because that makes the query unsargable (i.e. can't use indexes). Also DATEDIFF counts month changes between then and now, not actual months. See logic change below.

SELECT c.first_name, c.last_name, c.email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customers.id
    AND o.purchased_date >= DATEADD(MONTH, -6, GETDATE())
)

Table aliases are always recommended as well.

Assuming you asked this question from a performance perspective, I roughly checked the performance of this using the estimated execution plan against real tables I have with 499,290 clients and 1,333,326 orders. Your query took 43%, John's query took 39% (a tiny improvement), my query took 9% (a massive improvement) and SMor's query took 9% (again a massive improvement). So if performance is really what your question is about you should ask that and not artificially constrain the best performing solution.

Upvotes: 3

John Cappelletti
John Cappelletti

Reputation: 81990

Here's an option without a sub-query (not clear why) ... just brute force :)

Select C.first_name
      ,C.last_name
      ,C.email
      ,LastPurchase = max(O.purchased_date)
  From customers C
  Join orders    O on O.customer_id = C.customer_id 
  Group by C.customer_id
          ,C.first_name
          ,C.last_name
          ,C.email
  Having max(O.purchased_date) <= dateadd(month,-6,getdate())

Upvotes: 3

SMor
SMor

Reputation: 2872

EXCEPT comes to mind as a kludge-y alternative. A cte gets all customer IDs and then removes those based on orders where the purchase date is WITHIN 6 months. You then just join the cte back to the customer table.

with cte as (
   select customer_id from dbo.customers
   except
   select customer_id from dbo.orders where purchase_date >= dateadd(month, -6, getdate())
)
select cust.customer_id, ... 
from dbo.customers as cust
inner join cte on cte.customer_id = cust.customer_id
order by ...;

Upvotes: 2

Related Questions