CROSS JOIN

Use CROSS JOIN to generate all combinations.

SELECT column_name, ...
FROM table1
CROSS JOIN table2;

A comma-separated FROM clause produces the same result (older style).

SELECT column_name, ...
FROM table1, table2;

The following products and colors tables are used in the examples below.

Generate all combinations of the products table and the colors table.

sample_cross_join.sql

SELECT
    p.product_name,
    c.color
FROM products AS p
CROSS JOIN colors AS c;

+--------------+-------+
| product_name | color |
+--------------+-------+
| T-Shirt      | Red   |
| T-Shirt      | Blue  |
| T-Shirt      | Green |
| T-Shirt      | White |
| Hoodie       | Red   |
| Hoodie       | Blue  |
| Hoodie       | Green |
| Hoodie       | White |
| Jacket       | Red   |
| Jacket       | Blue  |
| Jacket       | Green |
| Jacket       | White |
+--------------+-------+
12 rows in set

The following members and locations tables are used to generate all combinations.

Generate all member-location combinations (3 × 2 = 6 rows).

sample_cross_join.sql

SELECT
    f.name AS member,
    s.location_name AS location
FROM members AS f
CROSS JOIN locations AS s
ORDER BY f.name, s.location_name;

+--------+----------+
| member | location |
+--------+----------+
| user_a | org_a    |
| user_a | zone_a   |
| user_b | org_a    |
| user_b | zone_a   |
| user_d | org_a    |
| user_d | zone_a   |
+--------+----------+
6 rows in set

Generate all dates in a specific month by cross-joining a sequential number table with the first day of that month (MySQL).

sample_cross_join.sql

-- Assumes the nums table contains sequential integers from 0 to 30 (MySQL).
SELECT DATE_ADD('2025-10-01', INTERVAL n.num DAY) AS calendar_date
FROM nums AS n
WHERE n.num <= 30
  AND DATE_ADD('2025-10-01', INTERVAL n.num DAY) < '2025-11-01'
ORDER BY calendar_date;

+---------------+
| calendar_date |
+---------------+
| 2025-10-01    |
| 2025-10-02    |
| 2025-10-03    |
| ...           |
| 2025-10-31    |
+---------------+
31 rows in set

The older comma-separated syntax produces the same result as CROSS JOIN.

sample_cross_join.sql

-- Same as CROSS JOIN (older comma-separated style)
SELECT f.name AS member, s.location_name AS location
FROM members AS f, locations AS s;

+--------+----------+
| member | location |
+--------+----------+
| user_a | zone_a   |
| user_a | org_a    |
| user_b | zone_a   |
| user_b | org_a    |
| user_d | zone_a   |
| user_d | org_a    |
+--------+----------+
6 rows in set

The CROSS JOIN syntax is supported across all major databases.

-- Common to MySQL, PostgreSQL, and SQLite
SELECT p.product_name, c.color
FROM products AS p
CROSS JOIN colors AS c;

In Oracle, the older comma-separated syntax (FROM table1, table2) is also widely used. It behaves identically to CROSS JOIN, but using CROSS JOIN explicitly is recommended for clarity.

CROSS JOIN is a join with no join condition — it pairs every row in the left table with every row in the right table. The number of result rows equals the left table row count multiplied by the right table row count.

Common use cases include generating calendars, building shift schedule grids, and listing all size-and-color combinations — any situation where you intentionally need every possible pairing. Using CROSS JOIN on large tables can cause the row count to explode, so check the expected number of rows before running the query.

For the basics of joins, see INNER JOIN. To join a table to itself, see SELF JOIN.