WHERE value IS NOT IN (subquery)

Question

I've been struggling with this query. I have two tables. One with coupons and Invoicenumbers. One with Invoicenumbers and customer names.

I need to get the customers who have not used a given coupon.

Here are the tables:

Promotion table:

Promotions
Invoice | Coupon
----------------
1       | couponA
2       | couponB
3       | couponB

Orders Table:

Orders
Invoice | Customer
------------------
1       | Jack
2       | Jack
3       | Jill

So Jack has used coupons A and B. And Jill has only used coupon B.

If my query were select customers who have not used coupon A, I should get Jill.

This works, but it seems clumsy and slow. Is there a better way?

SELECT Customer 
FROM Promotions INNER JOIN Orders
ON Promotions.Invoice = Orders.Invoice
WHERE Customer NOT IN(
    SELECT Customer 
    FROM Promotions INNER JOIN Orders
    ON Promotions.Invoice = Orders.Invoice
    WHERE Coupon = couponA)
GROUP BY Customer

Thanks for looking!

edit: Here's an SQLFiddle schema http://sqlfiddle.com/#!2/21d31/6

Are you still seeking a solution or does one if the answers work? — Bohemian, Dec 24 '12 at 08:05

Sami · Answer 1 · 2021-07-13T10:07:18.673

Updated: We should use prefer to use joins for better performance when its easy to do for us. Join vs. sub-query

Sql Fiddle

Select distinct Customer from orders o
join 
(
  SELECT distinct Customer as changedname FROM Orders o2 
  join
  (
     Select distinct invoice from Promotions where Coupon='couponA'
  ) t3
  on o2.invoice = t3.invoice      
) t2
on o.customer != t2.changedname;

Note: I changed column name customer for t3 because two joined tables must have different column names

Explanation:

Using inner or sub query is expensive when you have big data. use joins instead, lets learn converting subquery to join

With Subquery We had:

Select distinct Customer from orders where customer not in 
(SELECT distinct Customer FROM Orders where invoice in
(Select distinct invoice from Promotions where Coupon='couponA'));

Converting sub-query to join

First step:

Select distinct Customer from orders o
join 
(
  SELECT distinct Customer as changedname FROM Orders where invoice in
  (Select distinct invoice from Promotions where Coupon='couponA')
) t2
on o.customer != t2.changedname;

2nd step:

Select distinct Customer from orders o
join 
(
  SELECT distinct Customer as changedname FROM Orders o2 where invoice 
  join
  (
     Select distinct invoice from Promotions where Coupon='couponA'
  ) t3
  on o2.invoice = t3.invoice      
) t2
on o.customer != t2.changedname;

And that's it, much faster for tables having numerous rows

Original answer:

Use not in. Have a look.

Select distinct Customer from orders where customer not in 
(SELECT distinct Customer FROM Orders where invoice in
(Select distinct invoice from Promotions where Coupon='couponA'));

Edit I have added distinct to make query faster

SQL Fiddle

Tried a simple query without any join which will fetch your desired result and not bad with efficiency as well. I Can explain if any problem. — Sami, Dec 24 '12 at 07:32
This works in the Fiddle, but when I try it on my real data (which includes 10k-20k records) it times out. — TimSum, Dec 24 '12 at 15:30
@TimSum. I have added distinct to make query faster. Check it now. — Sami, Dec 24 '12 at 17:10

Akhil · Answer 2 · 2015-06-24T09:05:58.490

4

 SELECT DISTINCT o2.customer FROM ORDER o2 
LEFT JOIN (promotions p1 
    JOIN Orders o1 ON p1.cuopon = 'CuoponA' AND p1.invoice = o1.invoice ) p3 
    ON o2.customer = p3.customer 
WHERE p3.customer IS NULL

edited Jun 24 '15 at 09:05

answered Dec 24 '12 at 06:56

Akhil

2,602
23
36

After adjusting the spelling, I still get this error, "Unknown column 'p1.Coupon' in 'where clause'" – TimSum Dec 24 '12 at 15:27
`Select distinct customer from order left join promotions p1 on p1.cuopon = 'CuoponA' and p1.invoice = order.invoice where p1.coupon is null` – Akhil Dec 24 '12 at 16:51
add p1 as alias for promotions table – Akhil Dec 24 '12 at 16:51
After fixing Lot of mistake. `Select distinct customer from orders left outer join promotions p1 on p1.coupon = 'CouponA' and p1.invoice = orders.invoice where p1.coupon is null` Still wrong result – Sami Dec 24 '12 at 17:12
`SELECT DISTINCT o2.customer FROM ORDER o2 LEFT JOIN (promotions p1 JOIN Orders o1 ON p1.cuopon = 'CuoponA' AND p1.invoice = o1.invoice ) p3 ON o2.customer = p3.customer WHERE p3.customer IS NULL` – Akhil Dec 24 '12 at 17:32

Sergey Kalinichenko · Answer 3 · 2012-12-25T11:53:37.110

1

Try this query instead:

SELECT DISTINCT Customer
FROM Orders o1
WHERE NOT EXISTS (
    SELECT 1 
    FROM Orders o2
    INNER JOIN Promotions ON Promotions.Invoice = o2.Invoice
    WHERE o1.Customer = o2.Customer AND Coupon = 'couponB')

The idea is to get rid of the GROUP BY by removing a join in the top part of the query, and also eliminate the NOT IN by making a coordinated subquery.

Here is a link to sqlfiddle.

edited Dec 25 '12 at 11:53

answered Dec 24 '12 at 05:30

Sergey Kalinichenko

714,442
84
1,110
1,523

I can't get this to work correctly. I recently put up a SQLFiddle link – TimSum Dec 24 '12 at 06:08

score 0 · Answer 4 · answered Dec 24 '12 at 05:44

0

Try this with a right join

SELECT Customer, Coupon
FROM Promotions 
RIGHT JOIN Orders ON Promotions.Invoice = Orders.Invoice
    AND Coupon = 'couponA'
GROUP BY Customer
HAVING Coupon IS NULL

answered Dec 24 '12 at 05:44

user1864034

74
2

This doesn't quite work when I try it on my actual data. It's filtering out the Invoices that include 'CouponA', but I need the whole customer kept from the results. This worked in the Fiddle at first, but when I switched the order of the coupons (from A,B,B to B,A,B) it did not work. – TimSum Dec 24 '12 at 06:11

WHERE value IS NOT IN (subquery)

4 Answers4

Sql Fiddle

SQL Fiddle

Linked