SQL · Theory

Union vs Join

Theory

70/439

Union vs Join

JOIN adds columns (wider row): name next to marks. UNION stacks rows (longer list): 2024 names plus 2025 names. JOIN needs a key. UNION needs the same number of columns and similar types.

UNION removes duplicates. UNION ALL keeps them and is faster. Column names in the result come from the first SELECT.

Trap: UNION when you meant JOIN (you get two lists, not a combined report). Trap: JOIN when you meant UNION (you invent a fake key).

On the example next to this theory — Union vs Join — jOIN combines rows using a match condition between tables.

Diagram

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

Exam tip

JOIN widens. UNION lengthens. Draw both.

Union vs 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;

Union vs Join — jOIN combines rows using a match condition between tables.

Short notes

DefJOIN = sideways (related row). UNION = stacked lists.
RuleUNION same column count. JOIN needs ON.
TrapUsing one word for the other idea.

SQL · Theory

Union vs Join

← All stacks

Theory

70/439

Union vs Join

JOIN adds columns (wider row): name next to marks. UNION stacks rows (longer list): 2024 names plus 2025 names. JOIN needs a key. UNION needs the same number of columns and similar types.

UNION removes duplicates. UNION ALL keeps them and is faster. Column names in the result come from the first SELECT.

Trap: UNION when you meant JOIN (you get two lists, not a combined report). Trap: JOIN when you meant UNION (you invent a fake key).

On the example next to this theory — Union vs Join — jOIN combines rows using a match condition between tables.

Diagram

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

Exam tip

JOIN widens. UNION lengthens. Draw both.

Union vs 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;

Union vs Join — jOIN combines rows using a match condition between tables.

Short notes

DefJOIN = sideways (related row). UNION = stacked lists.
RuleUNION same column count. JOIN needs ON.
TrapUsing one word for the other idea.

Union vs Join

Union vs Join — sample query

Short notes

Questions

Union vs Join

Union vs Join — sample query

Short notes

Questions