What Is a LEFT JOIN in SQL?

What Is a LEFT JOIN in SQL?

A LEFT JOIN returns all rows from the left table and the matching rows from the right table; when no match exists, NULLs appear in right-side columns. This preserves every record from the primary table while optionally adding related information.

How Does a LEFT JOIN Differ From INNER JOIN?

INNER JOIN keeps only rows with matches in both tables, while LEFT JOIN keeps unmatched left-side rows. Choose LEFT JOIN when you need to show all records from the main table, even when related data is missing.

What Is the Basic LEFT JOIN Syntax?

Use SELECT columns FROM left_table LEFT JOIN right_table ON join_condition;. The ON clause defines how rows relate, typically by matching primary and foreign keys.

Basic LEFT JOIN Example

The query below lists every customer and any orders they have placed. Customers with no orders still appear, with NULLs for order columns.

SELECT c.customer_id,
c.name,
o.order_id,
o.order_date
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id;

LEFT JOIN With Filtering

Filter on the right-side table inside the ON clause to avoid turning your LEFT JOIN into an accidental INNER JOIN.

SELECT c.customer_id,
c.name,
o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON c.customer_id = o.customer_id
AND o.status = 'SHIPPED';

LEFT JOIN on Multiple Conditions

Combine several predicates in the ON clause to join on composite keys or apply additional criteria while retaining unmatched rows.

SELECT p.product_id,
p.name,
s.store_id,
s.stock_qty
FROM products AS p
LEFT JOIN stock AS s
ON p.product_id = s.product_id
AND s.store_id = 101;

When Should I Use a LEFT JOIN in Real Projects?

Use LEFT JOIN for optional relationships (users and profiles), audit reports (show all invoices even if unpaid), data quality checks (find rows lacking matches), and building summary tables where missing data should be visible.

What Are Best Practices for LEFT JOIN Performance?

Create indexes on join columns, filter in the ON clause, avoid SELECT *, and check execution plans. Limit retrieved columns and rows to keep queries fast.

How Can I Replace LEFT JOIN With Modern SQL Features?

CommonTableExpressions, window functions, and CROSS APPLY can sometimes replace LEFT JOIN for readability or performance, but LEFT JOIN remains the clearest way to keep all left-table rows.

Key Takeaways on SQL LEFT JOIN

LEFT JOIN preserves every row from the left table, fills unmatched right-side columns with NULL, and excels at optional data relationships. Index join keys, filter smartly, and test execution plans for optimal performance.