Joins: Intermediate

Keep all rows even without a match

LEFT JOIN guarantees every row from your primary table appears in the result. What SQL puts in the other table's columns when there is no match is where this join type gets interesting.

With INNER JOIN, Bob (C2) and Dave (C4) disappear from results because they have no orders. If you're analyzing "all customers," this silently loses data.

LEFT JOIN preserves all rows from your primary table, filling in NULLs where no match exists in the secondary table.

LEFT JOIN (or LEFT OUTER JOIN) keeps ALL rows from the left table. If a row has no match in the right table, the right table's columns are filled with NULL.

Watch how every row from the left table appears in the result. Rows without a matching right-side record receive NULL for those columns.

Notice: Bob and Dave appear in the results even though they have no orders. Their order columns show NULL.

LEFT JOIN shines when you need complete visibility into one table while optionally pulling in related data from another.

SELECT
  c.first_name
FROM customers AS c
___ transactions AS t
  ON c.customer_id = t.user_id
WHERE t.transaction_id ___

LEFT JOIN is the single most commonly used outer join in practice because the need to preserve a primary entity while optionally pulling in related data appears in nearly every reporting scenario.

The NULL values that LEFT JOIN produces for unmatched rows are not a defect but a deliberate signal that tells you exactly which rows had no corresponding record in the joined table.

When you see unexpected NULLs in a LEFT JOIN result, the first thing to check is whether your join condition is correctly matching the foreign key from one table to the primary key of the other.

Detect missing or orphaned records

When a LEFT JOIN produces NULL values in right-table columns, those NULLs tell you something important: no match was found. This becomes a powerful filter technique.

A powerful pattern: Use LEFT JOIN with WHERE to find rows that have NO match:

This finds customers with zero orders. The WHERE clause filters to only rows where the right table had no match (NULL).

Practice writing LEFT JOINs and using the NULL pattern to find unmatched rows.

SELECT
  c.name,
  o.total
FROM customers AS c
___ orders AS o
  ___ c.id = o.customer_id

The pattern of LEFT JOIN combined with WHERE on the right-table key being IS NULL is one of the most useful data quality patterns in SQL because it exposes orphaned or incomplete records in a single query.

Once you recognize that NULLs from a LEFT JOIN represent the absence of a match rather than missing data, you can use them as a precise filter to surface exactly the gaps you need to investigate.

Control when filters apply in joins

Where you place filter conditions in a LEFT JOIN query dramatically affects which rows survive. Understanding execution order explains why.

One of the most common mistakes with LEFT JOIN is accidentally converting it into an INNER JOIN by filtering on the right table in a WHERE clause. Understanding why this happens requires knowing the order of operations.

The database processes your query in a specific order. For a LEFT JOIN query, the evaluation happens in these phases:

The critical insight: WHERE is evaluated AFTER the join is complete. This means WHERE filters the already-joined result set, including the NULL-filled rows from unmatched left table entries.

This behavior stems from how SQL handles NULL in comparisons.

Moving filters between ON and WHERE clauses changes when conditions are evaluated and which rows survive the query.

Moving the filter condition into the ON clause changes when the filter is applied:

Match with conditions

Each customer tries to match orders where cust_id matches AND total > 50

Evaluate per customer

Alice matches (total=75). Bob has no orders. Carol's order (total=30) fails the condition.

Fill unmatched with NULL

Bob and Carol get NULL for order columns. No WHERE clause removes them.

All 3 customers appear

The LEFT JOIN preserves every left row regardless of the ON filter outcome.

With WHERE o.total > 50: Only Alice appears (1 row). Bob and Carol are filtered out because their rows have NULL or values that don't match.

With the filter in ON: All 3 customers appear. Alice shows her total (75), while Bob and Carol show NULL because they don't have matching high-value orders.

Both ON and WHERE placements have valid use cases. The choice depends on whether you want to preserve unmatched rows.

By putting the date filter in the ON clause, customers without recent orders appear with NULL order data instead of being excluded entirely.

SELECT
  c.name,
  o.total
FROM customers AS c
LEFT JOIN orders AS o
  ___ c.id = o.customer_id
  ___ o.total > 100

The WHERE vs ON distinction for LEFT JOIN filters is one of the most subtle and consequential decisions in SQL, because the results can look superficially similar while being logically very different.

When a colleague asks why their LEFT JOIN is behaving like an INNER JOIN, the WHERE clause filter on a right-table column is almost always the cause, making this one of the most important debugging patterns to recognize.

A reliable mental model is that the ON clause defines which rows can pair together, while the WHERE clause decides which pairs to keep after all rows have been preserved or filled with NULL.

Preserve rows from either or both tables

RIGHT JOIN preserves all rows from the right table instead of the left. While rarely used in practice, understanding it completes the join picture.

RIGHT JOIN is the mirror of LEFT JOIN. It keeps ALL rows from the right table, filling NULL for unmatched left table columns.

Notice how every right-side row appears regardless of whether a matching left row exists. Absent left-side values become NULL.

Notice that the result preserves every order row. When a customer has no matching record (C5), the name column fills with NULL rather than dropping the row entirely.

Order O3 appears with NULL name because it references a customer that doesn't exist. RIGHT JOIN ensures all orders are shown.

FULL OUTER JOIN preserves all rows from both tables, making it ideal for data reconciliation and comparison tasks.

FULL OUTER JOIN combines LEFT and RIGHT JOIN. It keeps ALL rows from BOTH tables, filling NULL where there's no match on either side.

Observe how neither table loses a row. Both sides are fully preserved, with NULL standing in wherever a match is absent.

Both tables are fully represented in the output. Rows unique to users get NULL in the right columns, rows unique to user_sessions get NULL in the left columns, and matched rows appear complete.

FULL OUTER JOIN is perfect for reconciliation - comparing two lists to find discrepancies:

SELECT
  t.user_id,
  p.user_id
FROM transactions AS t
___ push_notifs AS p
  ___ t.user_id = p.user_id

FULL OUTER JOIN is the most comprehensive join type because it guarantees that no row from either table will be dropped, making it ideal for any scenario where you need a complete picture of both datasets.

Data reconciliation between two systems is the most common real-world use case for FULL OUTER JOIN because it lets you identify records that exist in one system but not the other in a single pass.

RIGHT JOIN can almost always be rewritten as a LEFT JOIN by swapping the table order, which is why most teams adopt a convention of using only LEFT JOIN to keep their SQL consistent and easier to read.

Chain three or more tables together

For an in-depth exploration of table relationships and cardinality notation, see the Relationships lesson in Data Modeling.

In a 1:N relationship from customers to orders, LEFT JOIN from customers shows all customers. Some may have zero orders (NULL in order columns), some may have multiple.

Real-world databases normalize data across multiple tables. To reassemble complete information, you often need to chain multiple JOINs together, connecting tables through their foreign key relationships.

When the database processes multiple JOINs, it evaluates them sequentially from left to right:

Conceptually, each JOIN produces a new virtual table that becomes the input for the next JOIN. The first JOIN combines customers and orders, then that combined result joins with products.

Index lookups

Using indexes on join keys for O(log n) lookups

Hash joins

Building hash tables for O(1) key matching

Join reordering

Starting with smaller tables to reduce intermediate results

Predicate pushdown

Applying WHERE filters early to reduce data volume

While the optimizer rearranges INNER JOINs for performance, your written order matters for readability. Follow this pattern for clear, maintainable queries:

This keeps all orders (LEFT JOIN to customers), but only orders with valid products (INNER JOIN to products).

SELECT
  c.name,
  o.order_id,
  p.name
FROM orders AS o
___ customers AS c
  ___ o.cust_id = c.id
___ products AS p
  ___ o.prod_id = p.id