In this lesson, you will learn why moving your filters as close to the source data as possible is the secret to high-performance queries.
The Problem: Carrying "Dead Weight"
When you perform a join between two tables, the database engine has to process every row involved in that join. If you have a large table—say, Orders with 1,000,000 rows—and you only care about orders from the year 2023, you have two choices. You can join the entire million-row table to your Customers table and then filter for 2023, or you can filter the Orders table down to just the 2023 rows before performing the join.
"Filtering Late" means doing the extra work of joining rows you are just going to throw away anyway. "Filtering Early" means reducing the size of your sets before the expensive join operation happens.
Practical Application
Imagine we want to see the names of customers who placed orders in the "Electronics" category.
Filtering Late (Inefficient):
SELECT c.name, o.item_name
FROM Customers c
JOIN Orders o ON c.id = o.customer_id
WHERE o.category = 'Electronics';
In this scenario, the database performs a full join across all categories before discarding the non-electronic items.
Filtering Early (Efficient):
SELECT c.name, o.filtered_orders.item_name
FROM Customers c
JOIN (
SELECT * FROM Orders WHERE category = 'Electronics'
) AS filtered_orders ON c.id = filtered_orders.customer_id;
By filtering the Orders table inside a sub-select (or by placing the condition directly in the JOIN criteria), we provide the database engine with a much smaller set of data to manage. Because joins are computationally expensive, reducing the number of rows being joined saves significant memory and processing time.
Think of it like packing for a trip: it is much easier to sort through your clothes and remove what you don't need before you try to squeeze everything into your suitcase, rather than trying to fit all your belongings into the bag only to realize you have to take them all back out again.
Key Takeaway: Always apply filters as early as possible to minimize the number of rows entering a join, reducing the computational burden on your database.