There are other things that we'd like to figure out, which are pretty great to know.

Our new requirements are to figure out which products have never been purchased. We can break that out like this:

select 
	id, 
	description, 
	price 
from 
	products 
where 
	-- get any products that are not in this list
	id not in (
		-- this select will return an "array" of all product ids that have
		-- been purchased by any customer.
		-- this select is actually our basic query to get all customers and orders,
		-- but in disguise.
		select 
			p.id 
		from 
			customers c, 
			customerOrders co, 
			orderLineItems oli, 
			products p 
		where 
			c.id = co.customerId 
		and 
			co.id = oli.orderId 
		and 
			oli.productId = p.id 
		group by 
			p.id
	);

We're introducing a new concept in this last query. Towards the end of the inner select, you'll see some new key words, group by. What does group by do? Group by groups our results, aka, only returns one distinct result for a product id, even if multiple product id results would be returned normally had we not used group by. The best way to illustrate this is with an example.

What we're going to do is pull out the inner select from the query above and try it with and with out group by.

Without group by:

mysql> select 
	p.id 
from 
	customers c, 
	customerOrders co, 
	orderLineItems oli, 
	products p 
where 
	c.id = co.customerId 
and 
	co.id = oli.orderId 
and 
	oli.productId = p.id;
+----+
| id |
+----+
|  1 |
|  6 |
|  3 |
|  5 |
|  2 |
|  3 |
|  4 |
|  6 |
+----+
8 rows in set (0.00 sec)

You'll notice that there are duplicates in the id column above. We don't want duplicates. So, we'll use group by:

select 
	p.id 
from 
	customers c, 
	customerOrders co, 
	orderLineItems oli, 
	products p 
where 
	c.id = co.customerId 
and 
	co.id = oli.orderId 
and 
	oli.productId = p.id 
group by 
	p.id;
+----+
| id |
+----+
|  1 |
|  6 |
|  3 |
|  5 |
|  2 |
|  4 |
+----+
6 rows in set (0.00 sec)

The above output has no duplicates in it. We then feed those ids into our main select in a format that is akin to the following:

select 
	id, 
	description, 
	price 
from 
	products 
where 
	-- get any products that are not in this list
	id not in (1,6,3,5,2,4);

Which outputs:

+----+--------------------+-------+
| id | description        | price |
+----+--------------------+-------+
|  7 | Hot Slippery Lube  |  2.56 |
|  8 | Rainbow Clown Suit |  5.95 |
|  9 | Clown Wig          |  7.95 |
+----+--------------------+-------+
3 rows in set (0.00 sec)

Since ids numbered 7, 8, and 9 are not in our set of ids, those return rows get returned.

We can put it all together with the first query at the top of this page.