Course contentsShow
← Data Science
Lesson 932 of 2,14524. SQL JoinsFree lesson

LEFT JOIN with Multiple Conditions

Adding complex join conditions beyond simple equality to filter matches while preserving all left-table rows.

LEFT JOIN with Multiple Conditions

You'll learn how to combine multiple filtering conditions directly in a LEFT JOIN to create more selective matches while still preserving all rows from the left table.

The Core Idea

When you learned basic LEFT JOINs, you joined tables using a single condition—typically matching IDs. But real-world scenarios often require matching on multiple criteria simultaneously. For example, you might want to join customer orders to shipping records only when both the customer ID matches and the order date falls within a certain range.

The key difference from a simple WHERE filter is timing: conditions in the ON clause determine which right-table rows match during the join, while all left-table rows remain. Adding a WHERE filter after the join would eliminate left-table rows, defeating the purpose of LEFT JOIN.

How It Works

You chain conditions in the ON clause using logical operators:

SELECT customers.name, orders.order_id, orders.amount
FROM customers
LEFT JOIN orders 
  ON customers.customer_id = orders.customer_id
  AND orders.order_date >= '2024-01-01'
  AND orders.amount > 100;

This returns all customers, but only matches orders from 2024 with amounts over $100. Customers without matching orders still appear with NULL order values.

Think of it like a guest list: Everyone from the left table (customers) gets into the party. But they only get paired with a companion from the right table (orders) if all the conditions are met. No companion? They still attend solo (with NULLs).

When to Use This

  • Finding customers who made large recent purchases (while keeping all customers)
  • Matching inventory items to suppliers for specific categories
  • Joining transactions within date ranges while preserving all accounts

Key Takeaway: Multiple conditions in the LEFT JOIN ON clause let you filter matches selectively without removing any left-table rows—all left rows survive, matched or not.