How to find out which combinations of items from different tables never appear in a third table.
Problem: Our customers buy several types of pizza and different drinks. We want to know which combinations of pizza/drink were never ordered this week. For example, we want to know if no one ordered a marinara with 7up.
The first step is to find all possible combinations of pizzas and drinks. To do this, we’ll use a
CROSS JOIN. There is probably no relationship between pizza and drink tables, but this is fine for our purpose.
SELECT p.name, d.name FROM pizza AS p CROSS JOIN drink AS d;
CROSS JOINs return all combinations of rows from the left table with rows from the right table (Cartesian product). They can be very slow, but there is no other way to get this information. We’ll talk in another article about what to do to run queries faster in a case like this. Fortunately, however,
CROSS JOINs are rarely needed in a typical daily job.
The next step is to run an antijoin. We explained antijoins in another article: Find rows that have a match in another table.
We’ll use the
CROSS JOIN as a left table. Yes, the result of a query can be used as a table in a
JOIN clause (derived table). The right table will be the regular table (base table) that describes the orders, containing the id’s of the pizzas and drinks that have been ordered.
SELECT FROM ( SELECT p.id AS pizza_id, d.id AS drink_id FROM pizza AS p CROSS JOIN drink AS d ) AS c LEFT JOIN order AS o ON c.pizza_id <> o.pizza_id AND c.drink_id <> o.drink_id WHERE o.pizza_id IS NULL ;
LEFT JOIN returns all the rows from the left table (the pizza/drink combinations) plus the matching rows from the right table (the orders). For pizza/drink combinations with no matching order, the values of
order.drink_id are both
NULL. This is the only case in which one of those values can be
NULL. So the
WHERE clause specifies that we only want to see the rows with no match.