How to Use LEFT JOIN in SQL

Use LEFT JOIN when every row from the left table must stay in the result, even if no matching row exists in the joined table. This is perfect for optional relationships like users with profiles, customers with orders, or products with reviews.

What you’ll build or solve

You’ll learn how to use LEFT JOIN in SQL to keep all left-table rows while adding matching right-table data when available. You’ll also know how to handle missing joined values.

When this approach works best

This approach is the right choice when the primary table should remain complete.

Common real-world scenarios include:

• Users with optional profiles
• Customers with zero orders
• Products with no reviews
• Employees without managers
• Students without enrollments

This is a bad idea when only exact matches should appear. In that case, use INNER JOIN.

Prerequisites

You only need:

• Two related SQL tables
• Basic SELECT and join key knowledge
• A shared key such as user_id

Step-by-step instructions

Step 1: Keep all left rows with LEFT JOIN

Start with the main table on the left side.

SELECT users.email, profiles.bio
FROM users
LEFT JOIN profiles
ON users.user_id = profiles.user_id;

This keeps every user.

If a user has no profile, the profile columns return NULL.

This makes LEFT JOIN ideal for optional relationships.

You can combine it with COALESCE() for cleaner output.

SELECT users.email,
       COALESCE(profiles.bio, 'No bio') AS bio
FROM users
LEFT JOIN profiles
ON users.user_id = profiles.user_id;

What to look for:

• Every left-table row stays visible
• Missing right-table values become NULL
• Great for optional relationships
• Join on meaningful keys
• Use COALESCE() for display fallbacks

Examples you can copy

Customers with optional orders

SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

Products with reviews

SELECT products.name, reviews.rating
FROM products
LEFT JOIN reviews
ON products.product_id = reviews.product_id;

Employees with managers

SELECT employees.name, managers.name
FROM employees
LEFT JOIN managers
ON employees.manager_id = managers.manager_id;

Common mistakes and how to fix them

Mistake 1: Adding a right-table filter in WHERE

What the reader might do:

SELECT users.email, profiles.bio
FROM users
LEFT JOIN profiles
ON users.user_id = profiles.user_id
WHERE profiles.bio IS NOT NULL;

Why it breaks: this removes users without profiles and behaves like an INNER JOIN.

Corrected approach:

Move optional right-side filters into the JOIN condition when needed.

Mistake 2: Joining on the wrong key

What the reader might do:

ON users.email = profiles.user_id

Why it breaks: unrelated columns create invalid matches.

Corrected approach:

Use the real relationship key.

Mistake 3: Forgetting NULL handling

What the reader might do:

SELECT profiles.bio

Why it breaks: missing matches produce NULL.

Corrected approach:

Use COALESCE() for readable fallbacks.

Troubleshooting

If rows disappear, check for right-table filters in WHERE.

If NULL values appear, this may be expected unmatched data.

If matches look wrong, verify the join key.

If display values should never be blank, add COALESCE().

Quick recap

• Use LEFT JOIN to keep all left rows
• Missing matches become NULL
• Great for optional relationships
• Join on shared keys
• Use COALESCE() for fallbacks