How to write a SQL query that subtracts INNER JOIN results from LEFT JOIN results?

Question

ere's an example: I want to see how good my marketing efforts are working for a product I'm trying to sell in a store. For instance, I want to know how many people bought my product within a month after they received a coupon for it in their email on 12/1/2014, compared to how many people bought my product in that same time period without ever receiving a coupon. Here's a sample of my Customer table:

CUSTOMER_NUMBER        PURCHASE_DATE
---------------        -------------
1                      2014-12-02
2                      2014-12-05 
3                      2014-12-05 
4                      2014-12-10 
5                      2014-12-21 

Here's a sample of my Email table

CUSTOMER_NUMBER        EMAIL_ADDR         SEND_DATE
---------------        ------------       ----------
1                      [email protected]       2014-12-01
3                      [email protected]       2014-12-01
5                      [email protected]       2014-12-01

I have a pretty good idea how to determine who bought the product with the coupon: I use an inner join on the two tables. But in order to determine who bought the product anyway, even though they didn't have a coupon for whatever reason (they don't have email, they're part of a control group, etc.), I think I need to use a left join to get a result set, and then subtract the results of the inner join from my first result set. Alas, that is where I am stuck. In the example above, Customers 2 and 5 bought the product even though they never received a coupon, but I cannot figure out how to write a query to return that data. I am using IBM's Netezza DB. Thank you!!

Answer 1

Use Left Outer Join with NULL check

SELECT C.*
FROM   customer C
       LEFT OUTER JOIN email e
                    ON C.customer_Number = E.customer_Number
WHERE  E.customer_Number IS NULL

Or use Not Exists

SELECT *
FROM   customer C
WHERE  NOT EXISTS (SELECT 1
                   FROM   email e
                   WHERE  c.customer_number = e.customer_number)