In this lesson, you will learn how the commutative property of joins allows you to reorder tables in your query to minimize the size of intermediate results and speed up performance.
The Logic of Commutativity
In algebra, we know that A + B is the same as B + A. Because relational databases treat data as sets, the join operation is also commutative. This means that Table_A JOIN Table_B produces the exact same result as Table_B JOIN Table_A.
While the final output is identical, the process the database takes to get there—and the amount of memory it consumes along the way—can be vastly different depending on the order.
Minimizing Intermediate Results
Imagine you have two tables:
Users(1,000,000 rows)Orders(10 rows for a specific rare promotion)
If you write your SQL to join Users to Orders, the database engine might try to build a massive intermediate table using all the Users first. However, if you know the join order is commutative, you can structure your query to filter the smaller dataset first:
-- Join order matters for performance
SELECT *
FROM Orders O
JOIN Users U ON O.user_id = U.id
WHERE O.promotion_id = 'SPRING24';
By placing the Orders table first, the database engine can narrow down the dataset to those 10 rows immediately. It then only performs the expensive join operation on those 10 rows against the Users table, rather than trying to handle a million rows first and filtering them down at the end.
Putting it into Practice
The database query optimizer often tries to do this automatically, but as a developer, you hold the power to guide the execution path. Whenever you join tables, always think about the "size" of the data entering the join. By placing smaller, more selective datasets at the start of your join sequence, you keep the intermediate results small, which reduces memory pressure and speeds up your query execution.
Key Takeaway: Since join operations are commutative, you can reorder your tables to process the smallest datasets first, drastically reducing the size of intermediate results and improving overall query performance.