Mysql + difference in two result sets

Question

I have a pretty simple MySQL question. I have two tables, Customer and Orders. Customer table has fields (id, name) and Order has fields (id, customerID, and item). I can find which customer bought product A and customers that bought product B with the following query in MySQL.

SELECT DISTINCT c.`id`, c.name, o.`item`, o.qty FROM `customer` as c
INNER JOIN order AS o ON (c.`Id` =  o.`customerID`) 
where o.`item` ="Product A"

Union

SELECT DISTINCT c.`id`, c.name, o.`item`, o.qty FROM `customer` as c
INNER JOIN order AS o ON (c.`Id` =  o.`customerID`) 
where o.`item` ="Product B"

How can find the difference and similarity in these two result sets? 1) I.e. Customers that bought only product A but did not by product B 2) I.e. Customers that bought both product A and B

Thank you for your assistance.

D

Answer 1

You can try using the LEFT OUTER JOIN to get the result.