CROSS JOIN — SQL Technical Interview Questions & Answers

CROSS JOIN

CROSS JOIN

  • CROSS JOIN is every row of A paired with every row of B. 3 students × 4 subjects = 12 rows. No ON. Sometimes useful to generate combinations (students × exam dates).
  • A missing ON on an INNER JOIN accidentally becomes this. If the result is huge, you probably crossed.
  • Trap: CROSS JOIN as a ‘fancy join’ in every answer. It is rare on purpose.
  • On the example next to this theory: CROSS JOIN — jOIN combines rows using a match condition between tables.

Technical Interview Tip: 3 × 4 = 12 rows. Accidental if you forgot ON.

Code Examples for CROSS JOIN

CROSS JOIN — sample query

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO orders VALUES (10, 1, 500), (11, 1, 200);
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;

CROSS JOIN — jOIN combines rows using a match condition between tables.

How to Explain CROSS JOIN to an Interviewer

What is CROSS JOIN?

Every combination of rows No ON condition