CROSS JOIN and combinations

CROSS JOIN pairs every row of one table with every row of another. Nothing is matched and nothing is filtered: 3 colours and 4 sizes give 12 rows, one for each colour and size. That is what you want when a result must contain every combination, such as the variants of a product, a report that shows every category in every month, or a calendar for every store. It is a bug when it happens by accident, because the number of rows is the product of the table sizes, and two tables of 1,000 rows give a million. This lesson builds both kinds of combination, predicts their size before they run, fills a report grid with zeros where there is no data, and shows where the engines accept different syntax.

Each of the following queries is live: Run executes it in SQLite 3.49.1 in your browser, and the closing section sets PostgreSQL 18 and MySQL 8.4 beside it.

Every combination of two lists

-- Every T-shirt variant: each colour with each size, and a stock code (SKU) for each pair.
CREATE TABLE colours (colour TEXT PRIMARY KEY, code TEXT NOT NULL);
CREATE TABLE sizes   (size TEXT PRIMARY KEY, sort_order INTEGER NOT NULL);

INSERT INTO colours VALUES ('Indigo', 'IND'), ('Maroon', 'MAR'), ('Mustard', 'MUS');
INSERT INTO sizes   VALUES ('S', 1), ('M', 2), ('L', 3), ('XL', 4);

SELECT c.colour,
       s.size,
       'TEE-' || c.code || '-' || s.size AS sku
FROM colours AS c
CROSS JOIN sizes AS s
ORDER BY c.colour, s.sort_order;

-- 3 colours times 4 sizes.
SELECT count(*) AS variants FROM colours CROSS JOIN sizes;

┌─────────┬──────┬────────────┐
│ colour  │ size │    sku     │
├─────────┼──────┼────────────┤
│ Indigo  │ S    │ TEE-IND-S  │
│ Indigo  │ M    │ TEE-IND-M  │
│ Indigo  │ L    │ TEE-IND-L  │
│ Indigo  │ XL   │ TEE-IND-XL │
│ Maroon  │ S    │ TEE-MAR-S  │
│ Maroon  │ M    │ TEE-MAR-M  │
│ Maroon  │ L    │ TEE-MAR-L  │
│ Maroon  │ XL   │ TEE-MAR-XL │
│ Mustard │ S    │ TEE-MUS-S  │
│ Mustard │ M    │ TEE-MUS-M  │
│ Mustard │ L    │ TEE-MUS-L  │
│ Mustard │ XL   │ TEE-MUS-XL │
└─────────┴──────┴────────────┘
┌──────────┐
│ variants │
├──────────┤
│ 12       │
└──────────┘

colours CROSS JOIN sizes returns each colour once with each size: Indigo with S, M, L and XL, then Maroon, then Mustard. There is no ON clause, because there is nothing to match: every pair is wanted. The stock code is built from both halves of the pair, which is the usual reason to create variants with a query instead of typing them. The count confirms the arithmetic: 3 colours × 4 sizes = 12 variants.

A catalogue rarely makes every pair. A WHERE clause after the cross join removes the pairs that do not exist: WHERE NOT (c.colour = 'Mustard' AND s.size = 'XL') leaves 11 variants for a colour that has no XL. In MySQL, build the code with concat('TEE-', c.code, '-', s.size), because there || means OR unless the PIPES_AS_CONCAT SQL mode is on, as the lesson on text functions shows.

Pairs from one list

-- Pairs from one list: five products for a two-item combo offer.
CREATE TABLE products (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO products VALUES
  (1, 'Basmati rice'), (2, 'Toor dal'), (3, 'Masala chai'), (4, 'Coconut oil'), (5, 'Ragi flour');

-- Every ordered pair, a product with itself included: 5 x 5.
SELECT count(*) AS ordered_pairs
FROM products AS a CROSS JOIN products AS b;

-- Two different products, but (rice, dal) and (dal, rice) both counted: 5 x 4.
SELECT count(*) AS different_pairs
FROM products AS a CROSS JOIN products AS b
WHERE a.product_id <> b.product_id;

-- Each combination once: keep only the pairs whose first id is the smaller one.
SELECT a.name AS first_item, b.name AS second_item
FROM products AS a
CROSS JOIN products AS b
WHERE a.product_id < b.product_id
ORDER BY a.product_id, b.product_id;

┌───────────────┐
│ ordered_pairs │
├───────────────┤
│ 25            │
└───────────────┘
┌─────────────────┐
│ different_pairs │
├─────────────────┤
│ 20              │
└─────────────────┘
┌──────────────┬─────────────┐
│  first_item  │ second_item │
├──────────────┼─────────────┤
│ Basmati rice │ Toor dal    │
│ Basmati rice │ Masala chai │
│ Basmati rice │ Coconut oil │
│ Basmati rice │ Ragi flour  │
│ Toor dal     │ Masala chai │
│ Toor dal     │ Coconut oil │
│ Toor dal     │ Ragi flour  │
│ Masala chai  │ Coconut oil │
│ Masala chai  │ Ragi flour  │
│ Coconut oil  │ Ragi flour  │
└──────────────┴─────────────┘

A table can be crossed with itself under two aliases. Five products give 25 ordered pairs, because each product meets every product, itself included. a.product_id <> b.product_id removes the 5 pairs of a product with itself and leaves 20, but rice with dal and dal with rice are still both there. a.product_id < b.product_id keeps only the copy whose first id is the smaller, so each combination appears once: 10 of them, which is n × (n − 1) ÷ 2 for n = 5. Use <> when the order matters, as for a home match and an away match, and < when it does not, as for the two items of a combo offer.

Predict the size before you run it

-- Row counts multiply: 10 digits give 100 pairs and 1,000 triples, and an empty table gives nothing.
CREATE TABLE digits (d INTEGER PRIMARY KEY);
INSERT INTO digits VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
CREATE TABLE no_rows (x INTEGER);

SELECT count(*) AS two_tables   FROM digits AS a CROSS JOIN digits AS b;
SELECT count(*) AS three_tables FROM digits AS a CROSS JOIN digits AS b CROSS JOIN digits AS c;
SELECT count(*) AS with_empty   FROM digits AS a CROSS JOIN no_rows;

-- The 1,000 triples are the numbers 0 to 999, each once: a numbers table built from 10 rows.
CREATE TABLE numbers AS
SELECT a.d * 100 + b.d * 10 + c.d AS n
FROM digits AS a CROSS JOIN digits AS b CROSS JOIN digits AS c;

SELECT min(n) AS smallest, max(n) AS largest, count(DISTINCT n) AS different FROM numbers;

-- 1,000 rows by 1,000 rows: a million.
SELECT count(*) AS a_million FROM numbers AS x CROSS JOIN numbers AS y;

┌────────────┐
│ two_tables │
├────────────┤
│ 100        │
└────────────┘
┌──────────────┐
│ three_tables │
├──────────────┤
│ 1000         │
└──────────────┘
┌────────────┐
│ with_empty │
├────────────┤
│ 0          │
└────────────┘
┌──────────┬─────────┬───────────┐
│ smallest │ largest │ different │
├──────────┼─────────┼───────────┤
│ 0        │ 999     │ 1000      │
└──────────┴─────────┴───────────┘
┌───────────┐
│ a_million │
├───────────┤
│ 1000000   │
└───────────┘

The row count of a cross join is the product of the row counts of its inputs, whatever the rows hold. Ten digits give 100 pairs and 1,000 triples. A table without rows makes the product zero, so crossing anything with an empty table returns nothing, which matters again in the section on one-row tables. The triples have a use of their own: a.d * 100 + b.d * 10 + c.d turns each triple into a different number from 0 to 999, a numbers table made from ten typed rows. Crossing that table with itself gives the million rows that two tables of 1,000 rows always give.

Multiply before you run a query with a cross join, and compare the answer with the number of rows you expect:

• 4 categories × 3 months = 12 rows: the report grid later in this lesson.
• 3 colours × 4 sizes = 12 rows: the product variants above.
• 365 days × 200 stores = 73,000 rows: a calendar for every store for a year.
• 200 stores × 5,000 products = 1,000,000 rows: stock levels for one day.
• 365 days × 200 stores × 5,000 products = 365,000,000 rows: stock levels for a year.

The last line is the one to stop at. Even if each row held only three 8-byte integers and nothing else, 365 million rows would take about 8.8 GB (365,000,000 × 24 bytes) before any index. A grid that large is rarely what anyone needs: store the facts that exist, and build the full grid only for the slice a report shows, such as one store for 30 days, which is 1 × 30 × 5,000 = 150,000 cells.

The accidental cross join

-- A join that forgot its condition: 4 customers and 4 orders give 16 rows.
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (customer_id)
);
INSERT INTO customers VALUES (1, 'Asha Kulkarni'), (2, 'Vikram Nair'), (3, 'Meera Rathore'), (4, 'Tenzin Dorjee');
INSERT INTO orders VALUES (101, 1), (102, 2), (103, 1), (104, 3);

SELECT count(*) AS with_condition
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id;

SELECT count(*) AS comma_without_where
FROM orders AS o, customers AS c;

-- SQLite also runs a JOIN that has no ON at all, as a cross join.
SELECT o.order_id, c.name
FROM orders AS o
JOIN customers AS c
ORDER BY o.order_id, c.customer_id
LIMIT 8;

┌────────────────┐
│ with_condition │
├────────────────┤
│ 4              │
└────────────────┘
┌─────────────────────┐
│ comma_without_where │
├─────────────────────┤
│ 16                  │
└─────────────────────┘
┌──────────┬───────────────┐
│ order_id │     name      │
├──────────┼───────────────┤
│ 101      │ Asha Kulkarni │
│ 101      │ Vikram Nair   │
│ 101      │ Meera Rathore │
│ 101      │ Tenzin Dorjee │
│ 102      │ Asha Kulkarni │
│ 102      │ Vikram Nair   │
│ 102      │ Meera Rathore │
│ 102      │ Tenzin Dorjee │
└──────────┴───────────────┘

Most cross joins in real code are mistakes: joins whose condition went missing. The old comma syntax makes that easy, because the condition sits in WHERE, away from the table it belongs to, and a query without it still runs. Four orders and four customers give 4 rows with the condition and 16 without it. The third query shows that SQLite also accepts a JOIN without any ON and treats it as a cross join: order 101 appears beside all four customers, although only Asha placed it. MySQL accepts it too. PostgreSQL rejects a plain JOIN that has neither ON nor USING with a syntax error, which catches the mistake before the query runs.

The symptom is the row count. A join from orders to their customers follows a foreign key to exactly one row, so it must return exactly as many rows as there are orders, here 4. When a result has the product of two table sizes, or a whole multiple of the count you expected, look for a join without a condition, a condition that compares a column with itself (o.customer_id = o.customer_id is true on every row), or a condition written with the wrong alias.

Report grids that show every cell

-- Revenue per category and month for the first quarter of 2026, with a row for every cell.
CREATE TABLE categories (category TEXT PRIMARY KEY);
CREATE TABLE months (month TEXT PRIMARY KEY);          -- 'YYYY-MM'
CREATE TABLE sales (                                   -- one row per product sold
  order_id    INTEGER NOT NULL,
  placed_on   TEXT    NOT NULL,                        -- 'YYYY-MM-DD'
  status      TEXT    NOT NULL,                        -- 'delivered' or 'cancelled'
  category    TEXT    NOT NULL,
  qty         INTEGER NOT NULL,
  price_paise INTEGER NOT NULL
);

INSERT INTO categories VALUES ('Beverages'), ('Grains'), ('Oils'), ('Pulses');
INSERT INTO months VALUES ('2026-01'), ('2026-02'), ('2026-03');
INSERT INTO sales VALUES
  (201, '2026-01-04', 'delivered', 'Grains',    2, 14999),
  (201, '2026-01-04', 'delivered', 'Pulses',    1,  8950),
  (202, '2026-01-19', 'delivered', 'Beverages', 2, 12000),
  (203, '2026-02-02', 'delivered', 'Grains',    1,  6800),
  (204, '2026-02-27', 'cancelled', 'Pulses',    3,  8950),
  (205, '2026-03-08', 'delivered', 'Beverages', 1, 12000),
  (205, '2026-03-08', 'delivered', 'Grains',    1, 14999),
  (206, '2026-04-01', 'delivered', 'Oils',      2, 21500);   -- April: outside the grid

-- GROUP BY alone returns only the cells that have delivered sales: 6 of the 12.
SELECT s.category,
       substr(s.placed_on, 1, 7) AS month,
       sum(s.qty * s.price_paise) AS revenue_paise
FROM sales AS s
WHERE s.status = 'delivered'
  AND s.placed_on >= '2026-01-01' AND s.placed_on < '2026-04-01'
GROUP BY s.category, month
ORDER BY s.category, month;

-- The grid: every category with every month, then the delivered sales of each cell.
SELECT c.category,
       m.month,
       count(s.order_id) AS line_count,
       coalesce(sum(s.qty * s.price_paise), 0) AS revenue_paise
FROM categories AS c
CROSS JOIN months AS m
LEFT JOIN sales AS s
       ON s.category = c.category
      AND substr(s.placed_on, 1, 7) = m.month
      AND s.status = 'delivered'
GROUP BY c.category, m.month
ORDER BY c.category, m.month;

┌───────────┬─────────┬───────────────┐
│ category  │  month  │ revenue_paise │
├───────────┼─────────┼───────────────┤
│ Beverages │ 2026-01 │ 24000         │
│ Beverages │ 2026-03 │ 12000         │
│ Grains    │ 2026-01 │ 29998         │
│ Grains    │ 2026-02 │ 6800          │
│ Grains    │ 2026-03 │ 14999         │
│ Pulses    │ 2026-01 │ 8950          │
└───────────┴─────────┴───────────────┘
┌───────────┬─────────┬────────────┬───────────────┐
│ category  │  month  │ line_count │ revenue_paise │
├───────────┼─────────┼────────────┼───────────────┤
│ Beverages │ 2026-01 │ 1          │ 24000         │
│ Beverages │ 2026-02 │ 0          │ 0             │
│ Beverages │ 2026-03 │ 1          │ 12000         │
│ Grains    │ 2026-01 │ 1          │ 29998         │
│ Grains    │ 2026-02 │ 1          │ 6800          │
│ Grains    │ 2026-03 │ 1          │ 14999         │
│ Oils      │ 2026-01 │ 0          │ 0             │
│ Oils      │ 2026-02 │ 0          │ 0             │
│ Oils      │ 2026-03 │ 0          │ 0             │
│ Pulses    │ 2026-01 │ 1          │ 8950          │
│ Pulses    │ 2026-02 │ 0          │ 0             │
│ Pulses    │ 2026-03 │ 0          │ 0             │
└───────────┴─────────┴────────────┴───────────────┘

GROUP BY returns one row for each group that has rows. A report that must show every category in every month therefore loses the cells where nothing was sold. Oils sold nothing in the quarter, Beverages nothing in February, and the only February sale of Pulses was cancelled, so the first query returns 6 of the 12 cells. The grid pattern fixes the shape in three steps:

1. Build every cell: the categories cross joined with the months, 4 × 3 = 12 rows.
2. LEFT JOIN the facts to the cells, with every condition on the facts in ON: the category, the month and the status.
3. Group by the cell, turn an empty sum into 0 with coalesce, and count a column of the facts, count(s.order_id), because count(*) would count 1 for an empty cell.

The cells come from small tables that list what the report must show, not from the sales. SELECT DISTINCT category FROM sales can never list Oils, the very row the grid exists to show, so keep a table of categories, or take them from the products, and a table of months. The April sale needs no date filter, because no month of the grid matches it. Typing three months is fine for a quarter; the lesson on recursive common table expressions generates longer series of days and months. Joining on substr(s.placed_on, 1, 7) compares text, and the lesson on range joins shows how to join each sale to the first and last day of its month instead, which an index on the date can serve.

A condition in WHERE undoes the grid

-- The same grid with the status condition moved from ON to WHERE: the empty cells disappear again.
CREATE TABLE categories (category TEXT PRIMARY KEY);
CREATE TABLE months (month TEXT PRIMARY KEY);
CREATE TABLE sales (order_id INTEGER, placed_on TEXT, status TEXT, category TEXT, qty INTEGER, price_paise INTEGER);

INSERT INTO categories VALUES ('Beverages'), ('Grains'), ('Oils'), ('Pulses');
INSERT INTO months VALUES ('2026-01'), ('2026-02'), ('2026-03');
INSERT INTO sales VALUES
  (201, '2026-01-04', 'delivered', 'Grains', 2, 14999), (201, '2026-01-04', 'delivered', 'Pulses', 1, 8950),
  (202, '2026-01-19', 'delivered', 'Beverages', 2, 12000), (203, '2026-02-02', 'delivered', 'Grains', 1, 6800),
  (204, '2026-02-27', 'cancelled', 'Pulses', 3, 8950), (205, '2026-03-08', 'delivered', 'Beverages', 1, 12000),
  (205, '2026-03-08', 'delivered', 'Grains', 1, 14999), (206, '2026-04-01', 'delivered', 'Oils', 2, 21500);

SELECT c.category,
       m.month,
       coalesce(sum(s.qty * s.price_paise), 0) AS revenue_paise
FROM categories AS c
CROSS JOIN months AS m
LEFT JOIN sales AS s
       ON s.category = c.category
      AND substr(s.placed_on, 1, 7) = m.month
WHERE s.status = 'delivered'          -- an empty cell's NULL status fails this test
GROUP BY c.category, m.month
ORDER BY c.category, m.month;

┌───────────┬─────────┬───────────────┐
│ category  │  month  │ revenue_paise │
├───────────┼─────────┼───────────────┤
│ Beverages │ 2026-01 │ 24000         │
│ Beverages │ 2026-03 │ 12000         │
│ Grains    │ 2026-01 │ 29998         │
│ Grains    │ 2026-02 │ 6800          │
│ Grains    │ 2026-03 │ 14999         │
│ Pulses    │ 2026-01 │ 8950          │
└───────────┴─────────┴───────────────┘

For a cell without sales, the LEFT JOIN adds one row whose sales columns are all NULL. WHERE runs after the join, and NULL = 'delivered' is not true, so it drops exactly those rows, and the result shrinks back to the 6 cells with sales. Written in ON, as in the grid above, the same test only decides which sales attach to a cell. This is the rule from the lesson on outer joins, and a grid is where forgetting it shows first.

A one-row table on every row

-- A one-row table adds columns to every row; an empty one removes every row.
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, total_paise INTEGER NOT NULL);
CREATE TABLE delivery_rule (free_from_paise INTEGER NOT NULL, fee_paise INTEGER NOT NULL);
INSERT INTO orders VALUES (101, 38948), (102, 21500), (103, 30800), (104, 26850);
INSERT INTO delivery_rule VALUES (25000, 4000);       -- no fee from 250 rupees, else 40 rupees

SELECT o.order_id,
       o.total_paise,
       CASE WHEN o.total_paise >= r.free_from_paise THEN 0 ELSE r.fee_paise END AS delivery_paise
FROM orders AS o
CROSS JOIN delivery_rule AS r
ORDER BY o.order_id;

-- Someone empties the rule table: 4 orders times 0 rules.
DELETE FROM delivery_rule;
SELECT count(*) AS rows_left FROM orders AS o CROSS JOIN delivery_rule AS r;

-- LEFT JOIN ... ON TRUE keeps every order and shows the missing rule as NULL.
SELECT o.order_id, r.fee_paise
FROM orders AS o
LEFT JOIN delivery_rule AS r ON TRUE
ORDER BY o.order_id;

┌──────────┬─────────────┬────────────────┐
│ order_id │ total_paise │ delivery_paise │
├──────────┼─────────────┼────────────────┤
│ 101      │ 38948       │ 0              │
│ 102      │ 21500       │ 4000           │
│ 103      │ 30800       │ 0              │
│ 104      │ 26850       │ 0              │
└──────────┴─────────────┴────────────────┘
┌───────────┐
│ rows_left │
├───────────┤
│ 0         │
└───────────┘
┌──────────┬───────────┐
│ order_id │ fee_paise │
├──────────┼───────────┤
│ 101      │           │
│ 102      │           │
│ 103      │           │
│ 104      │           │
└──────────┴───────────┘

Crossing with a table of exactly one row multiplies the row count by one: every row stays and gains the columns of that row. That is a tidy way to apply a setting to every row, here a delivery rule (no delivery fee from 250 rupees, otherwise a fee of 40 rupees), without typing the numbers into the query. The danger lies in the other counts. If the rule table is empty, 4 orders × 0 rules = 0 rows, and a report built on it silently returns nothing; if a second rule is added, every order appears twice. LEFT JOIN … ON TRUE keeps every order when the table is empty and shows NULL in its columns, so a missing setting becomes a visible gap instead of an empty report. A table meant to hold one row can also enforce it with a key that may take only one value, such as id INTEGER PRIMARY KEY CHECK (id = 1); the module on creating tables and constraints covers both parts of that line.

Three ways to write it, and how the engines differ

a CROSS JOIN b, a, b and a JOIN b ON TRUE return the same full product. Prefer CROSS JOIN: it tells the reader that every pair is wanted, so nobody wonders whether a condition went missing. Two habits keep a query portable. Do not mix commas with JOIN: in PostgreSQL and MySQL a JOIN binds more tightly than a comma, so in FROM t1, t2 JOIN t3 ON t1.x = t3.x the ON cannot see t1 and the query fails (MySQL reports error 1054, an unknown column), while SQLite, which reads every join from left to right, accepts it. And give every inner join its ON, because SQLite and MySQL accept a join without one and quietly cross the tables.

In SQLite, CROSS JOIN has one more effect: the query planner never reorders the two tables of a CROSS JOIN, so the left one is always the outer loop. The rows are the same as with JOIN; the keyword is a way to force a join order, and SQLite’s documentation advises using it only where that control is needed. PostgreSQL plans a CROSS JOIN like any inner join, in whatever order it finds cheapest, and MySQL treats CROSS JOIN as a synonym of JOIN, with STRAIGHT_JOIN as its way to keep the left table first.

Your turn

-- Revenue of delivered sales for every category in each month of the first quarter of 2026,
-- with 0 for a category and month that sold nothing.
SELECT s.category,
       substr(s.placed_on, 1, 7) AS month,
       sum(s.qty * s.price_paise) AS revenue_paise
FROM sales AS s
WHERE s.status = 'delivered'
GROUP BY s.category, month;

# Sample tests: each one builds the three tables, runs your query on them and compares the rows it returns (in any
# order).
tests:
  - name: one row for every category and month, with 0 for the empty cells
    setup: |
      CREATE TABLE categories (category TEXT PRIMARY KEY);
      CREATE TABLE months (month TEXT PRIMARY KEY);
      CREATE TABLE sales (order_id INTEGER, placed_on TEXT, status TEXT, category TEXT, qty INTEGER, price_paise INTEGER);
      INSERT INTO categories VALUES ('Dairy'), ('Snacks'), ('Staples');
      INSERT INTO months VALUES ('2025-12'), ('2026-01'), ('2026-02'), ('2026-03'), ('2026-04');
      INSERT INTO sales VALUES
        (1, '2026-01-03', 'delivered', 'Staples', 2, 16500),
        (1, '2026-01-03', 'delivered', 'Dairy',   1, 39000),
        (2, '2026-01-21', 'delivered', 'Snacks',  3,  4000),
        (3, '2026-02-09', 'delivered', 'Dairy',   2,  9000),
        (4, '2026-03-14', 'delivered', 'Staples', 1, 64000);
    expected:
      columns: [category, month, revenue_paise]
      rows:
        - ['Dairy', '2026-01', 39000]
        - ['Dairy', '2026-02', 18000]
        - ['Dairy', '2026-03', 0]
        - ['Snacks', '2026-01', 12000]
        - ['Snacks', '2026-02', 0]
        - ['Snacks', '2026-03', 0]
        - ['Staples', '2026-01', 33000]
        - ['Staples', '2026-02', 0]
        - ['Staples', '2026-03', 64000]
  - name: cancelled sales and sales outside the quarter count nowhere
    setup: |
      CREATE TABLE categories (category TEXT PRIMARY KEY);
      CREATE TABLE months (month TEXT PRIMARY KEY);
      CREATE TABLE sales (order_id INTEGER, placed_on TEXT, status TEXT, category TEXT, qty INTEGER, price_paise INTEGER);
      INSERT INTO categories VALUES ('Dairy'), ('Fruit');
      INSERT INTO months VALUES ('2025-12'), ('2026-01'), ('2026-02'), ('2026-03'), ('2026-04'), ('2026-05');
      INSERT INTO sales VALUES
        (1, '2025-12-31', 'delivered', 'Fruit', 1, 5000),
        (2, '2026-01-01', 'delivered', 'Fruit', 2, 5000),
        (3, '2026-02-15', 'cancelled', 'Fruit', 4, 5000),
        (4, '2026-02-15', 'cancelled', 'Dairy', 1, 5600),
        (5, '2026-03-31', 'delivered', 'Dairy', 1, 5600),
        (6, '2026-04-01', 'delivered', 'Dairy', 3, 5600);
    expected:
      columns: [category, month, revenue_paise]
      rows:
        - ['Dairy', '2026-01', 0]
        - ['Dairy', '2026-02', 0]
        - ['Dairy', '2026-03', 5600]
        - ['Fruit', '2026-01', 10000]
        - ['Fruit', '2026-02', 0]
        - ['Fruit', '2026-03', 0]
  - name: several sales in one cell add up, and a category with no sales gets three zeros
    setup: |
      CREATE TABLE categories (category TEXT PRIMARY KEY);
      CREATE TABLE months (month TEXT PRIMARY KEY);
      CREATE TABLE sales (order_id INTEGER, placed_on TEXT, status TEXT, category TEXT, qty INTEGER, price_paise INTEGER);
      INSERT INTO categories VALUES ('Oils'), ('Pulses'), ('Spices');
      INSERT INTO months VALUES ('2026-01'), ('2026-02'), ('2026-03'), ('2026-06');
      INSERT INTO sales VALUES
        (1, '2026-01-05', 'delivered', 'Pulses', 1,  8950),
        (2, '2026-01-28', 'delivered', 'Pulses', 3,  8950),
        (3, '2026-02-11', 'delivered', 'Pulses', 2,  8950),
        (4, '2026-03-02', 'delivered', 'Oils',   1, 21500),
        (5, '2026-06-30', 'delivered', 'Spices', 5,  3000);
    expected:
      columns: [category, month, revenue_paise]
      rows:
        - ['Oils', '2026-01', 0]
        - ['Oils', '2026-02', 0]
        - ['Oils', '2026-03', 21500]
        - ['Pulses', '2026-01', 35800]
        - ['Pulses', '2026-02', 17900]
        - ['Pulses', '2026-03', 0]
        - ['Spices', '2026-01', 0]
        - ['Spices', '2026-02', 0]
        - ['Spices', '2026-03', 0]

Interview questions

Fresher · What does a CROSS JOIN return, and when would you write one on purpose?

A CROSS JOIN returns every combination of a row from the left table with a row from the right one, with no condition, so its row count is the product of the two counts: 40 rows and 25 rows give 1,000, and an empty table gives none. You write one on purpose when the answer must contain every combination: the variants of a product (sizes with colours), the cells of a report that shows every category in every month even where nothing sold (cross the dimensions, then LEFT JOIN the facts and COALESCE the gaps to 0), or one settings row attached to every row. Unplanned, it is usually a join that lost its condition, and the result then has a multiple of the expected row count, which is how you notice it.

Key takeaways

• CROSS JOIN returns every pair of rows. Its row count is the product of its inputs’ row counts, and an empty input makes it zero.
• Cross a table with itself and keep a.id < b.id to list each combination once: n × (n − 1) ÷ 2 of them.
• Multiply before you run: 1,000 × 1,000 rows is a million, and a third table of 365 days makes it 365 million.
• A row count that is a product or a multiple of the expected one usually means a join lost its condition. PostgreSQL refuses JOIN without ON; SQLite and MySQL run it as a cross join.
• For a report grid, cross the tables that list the rows and columns, LEFT JOIN the facts with their conditions in ON, and coalesce empty sums to 0.
• A one-row settings table can be crossed onto every row, but an empty one empties the result: use LEFT JOIN … ON TRUE when it may be empty.