Left Join — SQL Technical Interview Questions & Answers

Left Join

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.

Technical Interview Tip: LEFT JOIN marks — students without marks still show, score NULL.

Code Examples for Left Join

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.

How to Explain Left Join to an Interviewer

When LEFT JOIN?

Need all students even with no marks

How do you find students with no marks?

LEFT JOIN … WHERE m.roll IS NULL