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!!
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)