LEFT JOIN

Overview

A LEFT JOIN (or LEFT OUTER JOIN) returns all records from the left table, along with matching records from the right table. Unmatched right rows are populated with NULLs.

Learning Objectives

• Master LEFT JOIN semantics to retain all left-table records.
• Identify missing/unmatched relationships by checking for NULLs in right-table columns.
• Avoid accidental conversion of LEFT JOINs into INNER JOINs inside WHERE clauses.

Detailed Concept Explanation

Unlike INNER JOIN, a LEFT JOIN guarantees that every row from the left table will appear in the final result set at least once:

• When a matching row exists in the right table, right columns are populated.
• When no match exists in the right table, right columns return NULL.

Code Examples

SQL

-- Retrieve all customers and their optional order details
SELECT 
  c.customer_id,
  c.name,
  o.order_id,
  o.total_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- Find customers who have NEVER placed an order
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Best Practices

• Beware of WHERE Clause Conversion: Placing a WHERE right_table.col = 'value' filter on a LEFT JOIN turns it into an INNER JOIN because NULLs get filtered out. Put right-table conditions inside the ON clause instead!

Interview Perspective

Interactive Challenges

Summary

LEFT JOIN preserves all left-table rows, filling missing right-table columns with NULLs.