SQL CROSS JOIN: Generate Every Combination Deliberately

The practice task

Generate a fictional planning grid from two study days and three rooms. The grid describes possible slots, not confirmed bookings or evidence that the rooms are available.

The data below is fictional. Run the example in a new SQLite database. It was checked with SQLite 3.51.0; other databases may use different syntax, functions or type rules.

Complete worked query

Create the small fixture and run the query together. The IDs, boundary values and missing entries make the result small enough to check by hand.

CREATE TABLE days(day TEXT);
CREATE TABLE rooms(room TEXT);
INSERT INTO days VALUES ('Monday'),('Tuesday');
INSERT INTO rooms VALUES ('R1'),('R2'),('R3');

SELECT day, room FROM days CROSS JOIN rooms ORDER BY day, room;

Checked result

This is the result of the exact example above. NULL marks a missing database value rather than the text string "NULL".

Read the result

The result has six combinations: each of two days pairs with each of three rooms. No reservation condition is evaluated. The ordering turns the combination set into an easy-to-check planning grid.

The multiplication is intentional here. In an unrelated report, omitting a join condition could create the same shape by mistake and inflate counts or sums. Estimate the expected number of rows before running a large combination query.

An available-slot report needs additional information such as room opening times and existing reservations. Filter or anti-join the generated grid against those rules. Do not present every theoretical combination as available merely because it appears in the Cartesian product.

Mistakes to check

Use these checks when adapting the example to a different dataset.

• Calculate the expected input-row product for a small fixture.
• Keep combinations distinct from confirmed real-world events.
• Avoid adding DISTINCT just to hide an unintended multiplication.