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 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.

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.

Reference

More articles: