PostgreSQL CROSS JOIN

CROSS JOIN produces the Cartesian product of two tables: every row from the first table is paired with every row from the second. Unlike all other join types, CROSS JOIN has no join condition — there is no ON or USING clause. The result set size is exactly n × m rows, where n and m are the row counts of the two tables.

Syntax

SELECT select_list
FROM table1
CROSS JOIN table2;

Equivalent comma syntax (less preferred):

SELECT select_list
FROM table1, table2;

Equivalent using INNER JOIN ON true:

SELECT select_list
FROM table1
INNER JOIN table2 ON true;

All three forms produce identical results. The explicit CROSS JOIN keyword is preferred because it signals intent clearly to anyone reading the query.

Practical Example

The scenario below generates a scheduling matrix: every support agent paired with every available time slot, so a staffing tool can mark which slots are already filled.

CREATE TABLE support_agents (
  agent_id   SERIAL PRIMARY KEY,
  agent_name VARCHAR(80) NOT NULL,
  team       VARCHAR(50)
);

CREATE TABLE time_slots (
  slot_id    SERIAL PRIMARY KEY,
  slot_label VARCHAR(30) NOT NULL,   -- e.g. 'Mon 09:00', 'Mon 13:00'
  day_of_week VARCHAR(15),
  start_time  TIME
);

INSERT INTO support_agents (agent_name, team) VALUES
  ('Amara Nwobi',    'Tier 1'),
  ('Finn Larsson',   'Tier 1'),
  ('Zara Okonkwo',   'Tier 2');

INSERT INTO time_slots (slot_label, day_of_week, start_time) VALUES
  ('Mon 09:00', 'Monday',    '09:00'),
  ('Mon 13:00', 'Monday',    '13:00'),
  ('Tue 09:00', 'Tuesday',   '09:00'),
  ('Tue 13:00', 'Tuesday',   '13:00');

Generate all agent–slot combinations (3 × 4 = 12 rows)

SELECT
  sa.agent_name,
  sa.team,
  ts.slot_label,
  ts.day_of_week,
  ts.start_time
FROM support_agents sa
CROSS JOIN time_slots ts
ORDER BY sa.agent_name, ts.day_of_week, ts.start_time;

All 12 combinations appear — a complete matrix ready to have actual schedule data joined onto it.

Filter the Cartesian product with WHERE

-- Only Monday slots for Tier 2 agents
SELECT
  sa.agent_name,
  ts.slot_label
FROM support_agents sa
CROSS JOIN time_slots ts
WHERE sa.team = 'Tier 2'
  AND ts.day_of_week = 'Monday'
ORDER BY ts.start_time;

Verify expected row count before running on large tables

SELECT
  (SELECT COUNT(*) FROM support_agents) AS agents,
  (SELECT COUNT(*) FROM time_slots)     AS slots,
  (SELECT COUNT(*) FROM support_agents) *
  (SELECT COUNT(*) FROM time_slots)     AS expected_cross_join_rows;

CROSS JOIN vs Other Joins

Testing with Vela

Generating large scheduling matrices or test datasets with CROSS JOIN can strain a production database if the involved tables are larger than expected. Vela’s database branching lets you run the CROSS JOIN query against a production-scale branch, measure actual execution time, and tune the query with filters or LIMIT before running it in production.

Production Tips

• Always calculate the expected output row count (n × m) before running CROSS JOIN on tables with more than a few hundred rows — a 10,000-row table crossed with another 10,000-row table produces 100 million rows.
• Add a WHERE clause to filter the Cartesian product down to only the combinations you actually need.
• Use LIMIT during development to inspect the output shape and verify column assignments before retrieving the full result.
• Prefer the explicit CROSS JOIN keyword over the comma-separated FROM table1, table2 syntax — the explicit form makes the intent unmistakable and prevents confusion with an accidentally forgotten ON clause.
• If CROSS JOIN is being used to generate a date or integer series, consider generate_series() as a more efficient and self-documenting alternative.
• Monitor query duration with EXPLAIN ANALYZE and set an appropriate statement_timeout if using CROSS JOIN in automated jobs to prevent runaway queries.