Filtering Results with WHERE Clauses
JOIN reconstructs data from multiple normalized tables by matching rows based on key values specified in the ON condition.
From Separate Tables to One Result
A normalized database commonly stores related information in separate tables. A JOIN reconstructs information from those tables by matching related rows. The ON condition tells the database which key values represent that relationship. After the matching rows are combined, a WHERE clause can filter the resulting rows.
Reading the ON Condition
The ON condition defines the logical relationship between the tables. Typically, it matches a foreign key in one table to a primary key in another table. The value in the foreign-key column identifies the related row whose primary-key value is equal to it. The JOIN uses these key values to decide which rows belong together.
Predicting the Joined Rows
Matching Orders to Customers
Suppose a normalized Customers table contains customer_id values 7 and 12. Suppose an Orders table contains order_id 301 with customer_id 7 and order_id 302 with customer_id 12. Predict the joined result when the ON condition matches Orders.customer_id to Customers.customer_id.
Identify the relationship: The order's customer_id is treated as the foreign-key value, and the customer's customer_id is treated as the corresponding primary-key value.
Match equal keys: The order with customer_id 7 matches the customer with customer_id 7. The order with customer_id 12 matches the customer with customer_id 12.
Combine columns: Each matching pair contributes columns from both source tables to the joined result.
The result contains one combined row for order 301 with customer 7 and one combined row for order 302 with customer 12. The result can contain identifying columns from both tables, such as order_id and customer_id.
| Joined row | Order source | Customer source | Matching key |
|---|---|---|---|
| 1 | order_id 301 | customer_id 7 | 7 |
| 2 | order_id 302 | customer_id 12 | 12 |
Illustrative joined rows formed from equal key values.
Filtering After the Join
A WHERE clause filters the rows produced by the joined result. The useful sequence to remember is: first, the JOIN and ON condition establish which source rows correspond; then, the WHERE condition selects which of those combined rows remain in the requested result.
What do you think happens?
Two source rows match through the ON condition. A WHERE condition is then applied to the joined result. What is the purpose of that WHERE step?
Reveal answer
Answer: To select which combined rows remain in the result
The ON condition establishes the relationship used for matching. The WHERE clause is applied as a filtering step to the combined rows.
Integer Keys and Join Efficiency
Integer keys are more efficient for joins than strings because they reduce both the amount of data involved and the time needed for comparisons. This is one reason normalized tables connected by key values can perform well. The source material emphasizes that database performance is limited by data scanning, not by query complexity alone; using compact integer connections helps reduce the data burden during matching.
Common Joining Mistakes
Treating JOIN and ON as if they perform the same job.
JOIN names the table to combine, while ON defines how related rows are matched.
Fix:
Read the statement in two stages: identify the table being joined, then identify the foreign-key-to-primary-key comparison in the ON condition.Expecting every source row to appear in the joined result.
The joined result is based on rows that match according to the key relationship.
Fix:
Trace the key values first and include only the row combinations supported by the ON condition.Using the WHERE clause to describe the table relationship.
The ON condition defines the logical relationship. The WHERE clause filters the combined rows after that relationship has been established.
Fix:
Use ON for matching related keys and WHERE for selecting rows from the joined result.Assuming string connections are as efficient as integer connections.
Integer keys reduce data volume and comparison time relative to strings.
Fix:
Prefer integer key values for connections when the database design provides them.
Practice the Trace
Imagine a Customers table with customer_id values 4 and 9. Imagine an Orders table with order_id 501 linked to customer_id 9 and order_id 502 linked to customer_id 4. Trace the joined result before filtering, then decide which row remains if the WHERE clause selects customer_id 9.
Hints
- Match each Orders.customer_id value with the equal Customers.customer_id value.
- The joined result has one combined row for each successful key match.
- Apply the WHERE condition only after identifying the combined rows.
Practice Result
Use the keys in the practice tables and retain only the joined row associated with customer_id 9.
Match order 501: Order 501 has customer_id 9, so it matches the customer row with customer_id 9.
Match order 502: Order 502 has customer_id 4, so it matches the customer row with customer_id 4.
Apply the filter: A WHERE condition selecting customer_id 9 removes the combined row associated with customer_id 4.
The remaining joined result contains the combined row for order 501 and customer 9.
Key Takeaways
- JOIN combines data from normalized tables by matching related rows.
- The ON condition defines the relationship, typically by matching a foreign key with a primary key.
- A joined result contains combined columns and rows formed from successful key matches.
- A WHERE clause filters the combined rows after the JOIN relationship has been established.
- Integer keys can improve join efficiency by reducing data volume and comparison time.