LEFT JOIN returns all records from the left table and matching records from the right table.

Basic LEFT JOIN

SELECT customers.name, orders.id

FROM customers

LEFT JOIN orders ON customers.id = orders.customer_id;

Find Records Without Matches

SELECT customers.name

FROM customers

LEFT JOIN orders ON customers.id = orders.customer_id

WHERE orders.id IS NULL;

LEFT JOIN with Multiple Tables

SELECT c.name, o.id, p.product_name

FROM customers c

LEFT JOIN orders o ON c.id = o.customer_id

LEFT JOIN order_items oi ON o.id = oi.order_id

LEFT JOIN products p ON oi.product_id = p.id;

LEFT JOIN with Aggregation

SELECT c.name, COUNT(o.id) as order_count

FROM customers c

LEFT JOIN orders o ON c.id = o.customer_id

GROUP BY c.id;

LEFT JOIN vs INNER JOIN

-- LEFT JOIN: All customers, even without orders

-- INNER JOIN: Only customers with orders

Generate LEFT JOIN Queries

AI2sql chooses the right JOIN type automatically.