SQL · Theory

CROSS JOIN

Theory

63/439

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.

Diagram

  students.id
       =
  marks.student_id
        │
        ▼
     one result row

Exam tip

3 × 4 = 12 rows. Accidental if you forgot ON.

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.

Short notes

DefCROSS JOIN = cartesian product, no match rule.
RuleSize = rowsA × rowsB. Use on purpose only.
TrapAccidental CROSS from a missing ON.

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

CROSS JOIN — sample query

Short notes

Questions

CROSS JOIN

CROSS JOIN — sample query

Short notes

Questions