# Return missing combinations of items from different tables

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.

## CROSS JOIN

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 JOIN`s 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 JOIN`s are rarely needed in a typical daily job.

## Antijoin

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 `FROM` or `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
;``````

A `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.pizza_id` and `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.

More articles: