SQL LEFT OUTER JOIN: Preserving Left Table Rows & Finding Mismatches

The LEFT OUTER JOIN (commonly written as LEFT JOIN) returns all rows from the left table (Table A), regardless of whether a matching record exists in the right table (Table B). If a match exists in Table B, its column values are returned; if no match exists, the database supplies NULL for all columns of Table B.

Standard LEFT JOIN Syntax

SELECT a.column1, b.column2
FROM table_a a
LEFT JOIN table_b b ON a.key = b.key;

Hands-on Query: Listing All Students (Even Unassigned Ones)

-- Left Join guarantees EVERY student appears in the report
SELECT 
    s.student_id,
    s.first_name,
    COALESCE(d.dept_name, 'Unassigned') AS department,
    s.gpa
FROM students s
LEFT JOIN departments d ON s.dept_id = d.dept_id;

Finding Unmatched Records (The Left Anti-Join Pattern)

A common interview question: "Find all students who have not yet been assigned to any department":

SELECT s.student_id, s.first_name
FROM students s
LEFT JOIN departments d ON s.dept_id = d.dept_id
WHERE d.dept_id IS NULL; -- Filters only rows with no parent match!

Next, let us explore the mirror opposite: SQL RIGHT OUTER JOIN!