What does a CROSS JOIN produce, and how do I work out how many rows I will get?
A cross join lines up every row of the first table with every row of the second table. There is no condition at all.
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL
);
CREATE TABLE categories (
id INTEGER PRIMARY KEY,
name VARCHAR(60) NOT NULL
);
INSERT INTO products (id, name) VALUES
(1, 'Notebook'),
(2, 'Desk lamp');
INSERT INTO categories (id, name) VALUES
(1, 'Stationery'),
(2, 'Furniture');
-- Every product with every category.
SELECT p.name AS product, c.name AS category
FROM products p
CROSS JOIN categories c
ORDER BY p.name, c.name;
product category
Desk lamp Furniture
Desk lamp Stationery
Notebook Furniture
Notebook Stationery
So how do you work out how many rows you will get? You multiply. Two products and two categories gives you four rows. The rule is simple: the number of rows is the number of rows of the first table times the number of rows of the second table.
That rule is also the warning. If you join three tables with cross joins and they have ten, twenty and thirty rows, then you get six thousand rows. A cross join on big tables is one of the easiest ways to bring a server down.
There is one more thing worth knowing. The inner join with no condition is the same thing as a cross join, so the two spellings are interchangeable. The cross join is the clearer one, because it says that you mean every combination.
The next two questions in this topic look at when you would actually want one.