SQL · Theory

Left Join

Theory

61/439

Left Join

LEFT JOIN (LEFT OUTER JOIN) keeps every row from the left table. If the right side has no match, right columns are NULL. FROM students s LEFT JOIN marks m ON s.roll = m.roll; — Kabir still appears with NULL score if he has no marks row.

This is the ‘list all students and their marks if any’ query. Filter ‘students without marks’ with WHERE m.roll IS NULL (anti-join).

Putting WHERE m.score > 50 on a LEFT JOIN turns it into an INNER JOIN for practical purposes — you filtered out the NULLs. Use AND in the ON clause if you must filter the right table but keep left rows.

Trap — WHERE on the right table after LEFT JOIN. Trap: RIGHT JOIN when swapping table order + LEFT would be simpler.

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

Diagram

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

Exam tip

LEFT JOIN marks — students without marks still show, score NULL.

Left 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;

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

Short notes

DefLEFT JOIN keeps all left rows; right side may be NULL.
RuleWHERE right.key IS NULL finds unmatched left rows.
TrapWHERE on right columns turning it into INNER.

SQL · Theory

Left Join

← All stacks

Theory

61/439

Left Join

This is the ‘list all students and their marks if any’ query. Filter ‘students without marks’ with WHERE m.roll IS NULL (anti-join).

Trap — WHERE on the right table after LEFT JOIN. Trap: RIGHT JOIN when swapping table order + LEFT would be simpler.

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

Diagram

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

Exam tip

LEFT JOIN marks — students without marks still show, score NULL.

Left 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;

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

Short notes

DefLEFT JOIN keeps all left rows; right side may be NULL.
RuleWHERE right.key IS NULL finds unmatched left rows.
TrapWHERE on right columns turning it into INNER.

Left Join

Left Join — sample query

Short notes

Questions

Left Join

Left Join — sample query

Short notes

Questions