Concepts / Filtering Results with WHERE Clauses

Filtering Results with WHERE Clauses

After the matching rows are combined, a WHERE clause can filter the resulting rows.

  • Programming
Interactive lab

Try it: JOIN Step by Step

How a JOIN combines rows from two tables by testing the ON condition for every pair (a nested loop), how LEFT and RIGHT JOIN keep unmatched rows by padding with NULL, and how a junction table links a many-to-many relationship with two joins.

How it works

  1. Take the first row of the outer (left) table.
  2. Compare its key with the key of every row of the inner table using the ON condition; every TRUE comparison produces one combined row. NULL = anything is NULL, never a match.
  3. INNER JOIN drops an outer row with no match; LEFT JOIN keeps it with the inner columns set to NULL. RIGHT JOIN is the mirrored LEFT JOIN; CROSS JOIN pairs every row with every row.
  4. Repeat for every outer row. A second JOIN treats the rows produced so far as its outer table — this is how student → member → course crosses a junction table.

Default run (18 steps): SELECT artist.name, album.title FROM artist JOIN album ON artist.id = album.artist_id. A nested loop: for each outer row, test the ON condition against every inner row. … Result: 3 joined rows (15 ON comparisons).

Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). The join is shown as a plain nested loop; real SQLite may reorder tables or build a temporary index, but the rows produced are the same. Result order here is outer row, then inner row (SQL promises no order without ORDER BY).

Educational simulation

Loading the simulation…

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.

Customerscustomer_id, nameJoined resultcustomer and order columnsOrdersorder_id, customer_idmatching key valuesmatching key values
How do rows from two separate normalized tables combine into one joined result set?

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.

customer_id 7Customers primary keycustomer_id 7Orders foreign keycustomer_id 12Customers primary keycustomer_id 12Orders foreign keyequal key valuesequal key values
Which row in one table matches each row in the other table, and how does the key comparison determine those matches?

Predicting the Joined Rows

Worked example

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.

  1. 1
    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.

  2. 2
    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.

  3. 3
    Combine columns

    Each matching pair contributes columns from both source tables to the joined result.

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.

Customers7, 12Order 301Customer 7Orders301→7, 302→12Order 302Customer 12key 7key 7key 12key 12
What columns and rows appear in the output after matching records from the source tables?
Joined rowOrder sourceCustomer sourceMatching key
1order_id 301customer_id 77
2order_id 302customer_id 1212

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.

Source tablesseparate normalized dataMatched rowsON key relationshipFiltered rowsWHERE conditionJOIN and ONWHERE
After rows are joined, which records remain when a WHERE condition filters the combined 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?

  • To define which columns belong to the two source tables

  • To establish the key relationship between the tables

  • To select which combined rows remain in the result

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.

Integer keycompact comparisonRelated rowkey valueString keylarger comparisonRelated rowtext valuematch keymatch key
How do integer key values connect related records, and why can those connections be more efficient than matching strings?

Common Joining Mistakes

  • Common mistake

    Treating JOIN and ON as if they perform the same job.

    What it looks like

    A learner identifies the second table but does not identify the key relationship between the tables.

    Why it fails

    JOIN names the table to combine, while ON defines how related rows are matched.

    Do this instead

    Read the statement in two stages: identify the table being joined, then identify the foreign-key-to-primary-key comparison in the ON condition.

  • Common mistake

    Expecting every source row to appear in the joined result.

    What it looks like

    A learner assumes that all rows from both normalized tables will automatically be present.

    Why it fails

    The joined result is based on rows that match according to the key relationship.

    Do this instead

    Trace the key values first and include only the row combinations supported by the ON condition.

  • Common mistake

    Using the WHERE clause to describe the table relationship.

    What it looks like

    A learner uses a filtering condition as though it explains which customer belongs to an order.

    Why it fails

    The ON condition defines the logical relationship. The WHERE clause filters the combined rows after that relationship has been established.

    Do this instead

    Use ON for matching related keys and WHERE for selecting rows from the joined result.

  • Common mistake

    Assuming string connections are as efficient as integer connections.

    What it looks like

    A learner treats a long text value and a compact integer key as equivalent for join performance.

    Why it fails

    Integer keys reduce data volume and comparison time relative to strings.

    Do this instead

    Prefer integer key values for connections when the database design provides them.

Practice the Trace

EASY

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.
Worked example

Practice Result

Use the keys in the practice tables and retain only the joined row associated with customer_id 9.

  1. 1
    Match order 501

    Order 501 has customer_id 9, so it matches the customer row with customer_id 9.

  2. 2
    Match order 502

    Order 502 has customer_id 4, so it matches the customer row with customer_id 4.

  3. 3
    Apply the filter

    A WHERE condition selecting customer_id 9 removes the combined row associated with customer_id 4.

Result

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.

Related Topics

Related concepts

More concepts starting with F