SQL CROSS JOIN: Syntax, Examples & Practice

A shop sells a T-shirt in three sizes and two colours. To list every variant it has to stock, pair each size with each colour. That is a cross join:

SELECT s.size, c.colour
FROM sizes s
CROSS JOIN colours c;

Here is the same query on two small lists you can run. It is written with a comma between the tables, which means exactly the same thing as CROSS JOIN (see Syntax for why this page runs the comma form):

SELECT s.size, c.colour
FROM (SELECT 'S' AS size UNION ALL SELECT 'M' UNION ALL SELECT 'L') AS s,
     (SELECT 'Red' AS colour UNION ALL SELECT 'Blue') AS c
ORDER BY s.size, c.colour;

▶ Run this query in the SQL Playground

Three sizes times two colours is six rows. No size was "matched" to a colour; every size simply met every colour once. Other joins start from this same set of pairs and then keep only the ones that satisfy ON. A cross join keeps them all.

SELECT columns
FROM table_a
CROSS JOIN table_b;

There is no ON clause. Three spellings produce the same result:

Database support

Always the number of rows in the first table multiplied by the number in the second. The Playground has 10 departments; pairing them with the four quarters of a year gives 40 rows:

SELECT (SELECT COUNT(*) FROM departments) AS departments,
       4 AS quarters,
       COUNT(*) AS rows_returned
FROM departments d,
     (SELECT 'Q1' AS quarter UNION ALL SELECT 'Q2'
      UNION ALL SELECT 'Q3' UNION ALL SELECT 'Q4') AS q;

▶ Run this query in the SQL Playground

The last two rows matter in practice. Cross joining with an empty table returns nothing at all, and cross joining with a one-row table adds its columns to every row without changing the row count, which is one of the most useful tricks on this page.

The growth is why cross joins have a reputation. Joining the Playground's orders to its customers with no condition would already be a five-figure result:

SELECT (SELECT COUNT(*) FROM orders) AS orders,
       (SELECT COUNT(*) FROM users)  AS customers,
       (SELECT COUNT(*) FROM orders) * (SELECT COUNT(*) FROM users) AS rows_if_cross_joined;

▶ Run this query in the SQL Playground

The most common legitimate use: make sure every combination appears, even with no data.

Cross join two lists to get every combination that should exist. Every department for every quarter, ready for targets or budgets to be filled in:

SELECT d.name AS department, q.quarter
FROM departments d,
     (SELECT 'Q1' AS quarter UNION ALL SELECT 'Q2'
      UNION ALL SELECT 'Q3' UNION ALL SELECT 'Q4') AS q
ORDER BY d.name, q.quarter
LIMIT 8;

▶ Run this query in the SQL Playground

The grid becomes valuable when real data is attached to it with a LEFT JOIN. Counting orders by status and month directly leaves out any combination with no orders. Building the grid first keeps them, with a zero:

SELECT s.status, m.month, COUNT(o.id) AS orders
FROM (SELECT DISTINCT status FROM orders) AS s,
     (SELECT '2024-10' AS month UNION ALL SELECT '2024-11'
      UNION ALL SELECT '2024-12') AS m
LEFT JOIN orders o
  ON o.status = s.status
 AND strftime('%Y-%m', o.order_date) = m.month
GROUP BY s.status, m.month
ORDER BY s.status, m.month;

▶ Run this query in the SQL Playground

Six statuses times three months is 18 rows, and every one is present. A plain GROUP BY on orders returns fewer, because combinations such as cancelled orders in October never occur. Charts and pivot tables need the full grid. strftime is SQLite's date function.

Cross joining to a subquery that returns a single row puts that value on every row. It is the simplest way to compare each row with a grand total:

SELECT s.status, s.orders, t.total_orders,
       ROUND(100.0 * s.orders / t.total_orders, 1) AS pct_of_total
FROM (SELECT status, COUNT(*) AS orders
      FROM orders
      GROUP BY status) AS s,
     (SELECT COUNT(*) AS total_orders FROM orders) AS t
ORDER BY s.orders DESC;

▶ Run this query in the SQL Playground

The second subquery has one row, so the row count stays at six and total_orders is simply available everywhere. The same pattern compares each group with an overall average:

SELECT p.category,
       ROUND(AVG(p.price), 2) AS avg_price,
       a.overall_avg,
       ROUND(AVG(p.price) - a.overall_avg, 2) AS difference
FROM products p,
     (SELECT ROUND(AVG(price), 2) AS overall_avg FROM products) AS a
GROUP BY p.category, a.overall_avg
ORDER BY difference DESC;

▶ Run this query in the SQL Playground

A window function such as SUM(…) OVER () does the same job in one pass; the cross join version works in every database and is easy to read.

Cross join a table to itself to get every pairing of its rows, then add a condition to drop the pairs you do not want. Every match-up between two different departments, each listed once:

SELECT a.name AS department_a, b.name AS department_b
FROM departments a, departments b
WHERE a.id < b.id
ORDER BY a.id, b.id
LIMIT 6;

▶ Run this query in the SQL Playground

Without the WHERE there would be 10 × 10 = 100 rows, including each department paired with itself and every pair twice. a.id < b.id leaves the 45 distinct pairs. This is a self join that starts from a cross join.

An inner join is a cross join plus a condition. Add a WHERE that links the two tables and the cross join returns exactly what the inner join does:

SELECT 'comma join with WHERE' AS syntax, COUNT(*) AS rows_returned
FROM orders o, users u
WHERE u.id = o.user_id
UNION ALL
SELECT 'INNER JOIN … ON', COUNT(*)
FROM orders o
INNER JOIN users u ON u.id = o.user_id;

▶ Run this query in the SQL Playground

If the two tables are related, write an INNER JOIN with ON, not a cross join with WHERE: the result is the same, but the intent is clear and the condition cannot be forgotten. The SQL JOINs overview compares all the join types.

A quick example of the last one, with three days and two shifts:

SELECT d.day, s.shift
FROM (SELECT 1 AS n, 'Mon' AS day UNION ALL SELECT 2, 'Tue'
      UNION ALL SELECT 3, 'Wed') AS d,
     (SELECT 'Morning' AS shift UNION ALL SELECT 'Evening') AS s
ORDER BY d.n, s.shift DESC;

▶ Run this query in the SQL Playground

Far more cross joins are written by mistake than on purpose.

Check for it by counting. If a query returns more rows than its largest table and you did not expect a one-to-many join, compare the row count with the product of the table sizes. To be safe by habit:

7 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy A café sells three drinks (Coffee, Tea, Juice) in two sizes (Small, Large). List every drink and size combination. Table: (lists you write yourself)

   Show solution

   SQLite · runs in the Playground

   SELECT d.drink, s.size
   FROM (SELECT 'Coffee' AS drink UNION ALL SELECT 'Tea' UNION ALL SELECT 'Juice') AS d,
        (SELECT 'Small' AS size UNION ALL SELECT 'Large') AS s
   ORDER BY d.drink, s.size;

   ▶ Run this query in the SQL Playground

   Three drinks times two sizes gives 6 rows. With real tables this would be FROM drinks CROSS JOIN sizes.

2. 2Easy Create a planning grid with one row for every department and each of the years 2025 and 2026. Show the first ten rows. Table: departments

   Show solution

   SQLite · runs in the Playground

   SELECT d.name AS department, y.year
   FROM departments d,
        (SELECT 2025 AS year UNION ALL SELECT 2026) AS y
   ORDER BY d.name, y.year
   LIMIT 10;

   ▶ Run this query in the SQL Playground

   Ten departments times two years is 20 rows in total; LIMIT shows the first ten.

3. 3Medium For each department, show the number of employees and what percentage of all employees that is. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT e.department,
          COUNT(*) AS employees,
          ROUND(100.0 * COUNT(*) / t.total, 1) AS pct_of_company
   FROM employees e,
        (SELECT COUNT(*) AS total FROM employees) AS t
   GROUP BY e.department, t.total
   ORDER BY employees DESC;

   ▶ Run this query in the SQL Playground

   The one-row subquery puts the company total on every row. It is in the GROUP BY because it is not aggregated.

4. 4Medium List the five highest paid employees with the company average salary beside each and the difference. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT e.first_name, e.last_name, e.salary,
          a.avg_salary,
          e.salary - a.avg_salary AS above_average
   FROM employees e,
        (SELECT ROUND(AVG(salary)) AS avg_salary FROM employees) AS a
   ORDER BY e.salary DESC
   LIMIT 5;

   ▶ Run this query in the SQL Playground

   A cross join to a single row does not change the number of rows; it only adds the average as a column.

5. 5Medium How many distinct pairs of departments are there? Count them with a cross join of the table to itself. Table: departments

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS department_pairs
   FROM departments a, departments b
   WHERE a.id < b.id;

   ▶ Run this query in the SQL Playground

   Ten departments give 10 × 9 / 2 = 45 pairs. a.id < b.id removes self-pairs and mirrored pairs.

6. 6Challenging For each product category and each star rating from 1 to 5, count the reviews. Every category must show all five ratings, with 0 where there are none. Show the first ten rows. Table: products, reviews

   Show solution

   SQLite · runs in the Playground

   SELECT c.category, r.rating, COUNT(rv.id) AS reviews
   FROM (SELECT DISTINCT category FROM products) AS c,
        (SELECT 1 AS rating UNION ALL SELECT 2 UNION ALL SELECT 3
         UNION ALL SELECT 4 UNION ALL SELECT 5) AS r
   LEFT JOIN (
     SELECT p.category, v.rating, v.id
     FROM reviews v
     JOIN products p ON p.id = v.product_id
   ) AS rv
     ON rv.category = c.category
    AND rv.rating = r.rating
   GROUP BY c.category, r.rating
   ORDER BY c.category, r.rating
   LIMIT 10;

   ▶ Run this query in the SQL Playground

   The cross join builds the 10 × 5 grid; the LEFT JOIN attaches the reviews; COUNT(rv.id) gives 0 for empty cells.

7. 7Challenging Show the number of orders per month for October to December 2024 for each shipping city, including months with no orders. Show the first nine rows. Table: orders

   Show solution

   SQLite · runs in the Playground

   SELECT c.shipping_city, m.month, COUNT(o.id) AS orders
   FROM (SELECT DISTINCT shipping_city FROM orders) AS c,
        (SELECT '2024-10' AS month UNION ALL SELECT '2024-11'
         UNION ALL SELECT '2024-12') AS m
   LEFT JOIN orders o
     ON o.shipping_city = c.shipping_city
    AND strftime('%Y-%m', o.order_date) = m.month
   GROUP BY c.shipping_city, m.month
   ORDER BY c.shipping_city, m.month
   LIMIT 9;

   ▶ Run this query in the SQL Playground

   Both conditions belong in ON so that city and month combinations with no orders are kept, with a count of 0.