Concepts / Joins: Combining Data from Multiple Tables

Joins: Combining Data from Multiple Tables

When a query works with data from more than one table, the returned rows still need to be controlled.

  • Programming
Interactive lab

Try it: SELECT, WHERE, ORDER BY, LIMIT

How a SELECT query tests every row against the WHERE condition (keeping only rows where it is TRUE, not FALSE or NULL), then picks columns, removes duplicates with DISTINCT, sorts with ORDER BY and cuts with LIMIT.

How it works

  1. FROM: start with every row of the table.
  2. WHERE: evaluate the condition on each row. Comparisons with NULL give NULL (unknown); NOT, AND and OR follow three-valued logic, with AND binding tighter than OR. Only rows where the whole condition is TRUE are kept.
  3. SELECT picks the listed columns; DISTINCT then drops result rows that repeat an earlier one.
  4. ORDER BY sorts by each key in turn, ASC or DESC; SQLite puts NULL first when ascending and last when descending.
  5. LIMIT n keeps only the first n rows of the sorted result.

Default run (13 steps): SELECT title, plays FROM track WHERE plays >= 95 AND rating >= 4 ORDER BY plays DESC LIMIT 2. FROM: start with all 8 rows of track. … Result: 2 rows.

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). Ties in ORDER BY are broken by id here (by the selected columns with DISTINCT), and DISTINCT without ORDER BY keeps first-occurrence order; SQLite itself promises no order for ties. LIKE is case-insensitive for ASCII letters, as in SQLite.

Educational simulation

Loading the simulation…

A Careful Starting Point

When a query works with data from more than one table, the returned rows still need to be controlled. The supplied material establishes three operations that shape query results: WHERE tests each row and keeps only rows whose condition is true, comparison and logical operators build those conditions, and ORDER BY sorts the filtered results. The material does not provide a specific JOIN keyword or join syntax, so this lesson concentrates on those documented parts of the query process rather than inventing join syntax.

Key point

A useful mental model is: test rows with WHERE first, then organize the rows that remain with ORDER BY.

Rows Passing Through WHERE

The WHERE clause evaluates a condition against each row. A row is returned only when that condition is true. This makes WHERE a conditional selection step: it does not return every available row automatically, but narrows the result to rows meeting the stated requirement.

All rowsrows before testingWHERE conditiontrue or falseReturned rowscondition is truetest each rowtrue
Which rows are retained when a WHERE condition is applied?
Worked example

Selecting Rows by a Condition

Suppose a query must return only rows whose score is greater than 80.

  1. 1
    Build the condition

    Use the greater-than comparison operator with the field and the required value: score > 80.

  2. 2
    Test each row

    The WHERE clause tests score > 80 against every row.

  3. 3
    Keep qualifying rows

    Only rows for which the condition is true are returned.

Result

The result contains only rows with scores greater than 80.

Building Conditions

SQL conditions can use comparison operators to compare a field with a value. The documented operators are =, <, >, <=, >=, and !=. A condition can also combine comparisons with AND and OR, allowing a WHERE clause to express more than one requirement.

RowCondition AcomparisonANDcombined conditionReturned rowWHERE condition is trueCondition BcomparisonORcombined conditiontesttestcombinecombinecombinecombinetruetrue
How do comparison and logical operators determine whether a row satisfies a combined condition?
  • Use = when the SQL condition tests equality.
  • Use < or > when a value must be lower or higher than another value.
  • Use <= or >= when the boundary value should also qualify.
  • Use != when a value must not equal another value.
  • Use AND or OR to combine comparison conditions.
Worked example

Combining Two Requirements

Suppose a query should return rows where score is at least 70 and status is not equal to inactive.

  1. 1
    Write the first comparison

    The first condition is score >= 70.

  2. 2
    Write the second comparison

    The second condition is status != inactive.

  3. 3
    Connect the conditions

    Use AND when both conditions must be part of the requirement.

  4. 4
    Apply the complete condition

    The WHERE clause tests the combined expression against each row.

Result

Only rows satisfying the combined WHERE condition are returned.

SQL Equality and Python Equality

SQL uses a single equal sign, =, for equality testing in a WHERE clause. Python uses the double equal sign, ==, for equality testing. This difference matters when moving between SQL and programming-language code: copying Python's equality spelling into a SQL WHERE condition changes the syntax expected by SQL.

ContextEquality syntaxMeaning
SQL WHERE condition=Equality testing
Python==Equality testing

The equality spelling differs between SQL and Python.

SQL=Python==
What is the difference between equality syntax in SQL and Python?

Sorting After Filtering

ORDER BY sorts query results by a specified column. The documented order of operations is important: WHERE filtering is applied first, and ORDER BY is applied after that filtering. Therefore, ORDER BY organizes the rows that remain rather than deciding which rows qualify.

RowsWHEREfilterFiltered rowsORDER BYsortOrdered resultstestkeep true rowspass onarrange by column
How does a query move from all available rows to an ordered result?
Worked example

Filtering, Then Sorting

Suppose a query should return only rows with score greater than 80 and organize those returned rows by name.

  1. 1
    Filter

    Use WHERE with the comparison score > 80. Rows that fail the condition are not returned.

  2. 2
    Sort

    Use ORDER BY with the selected field name after the filtering condition.

  3. 3
    Read the result

    The result contains only qualifying rows, arranged according to the specified column.

Result

WHERE determines which rows remain; ORDER BY determines how those remaining rows are organized.

Common Query Mistakes

  • Common mistake

    Using == for equality in a SQL WHERE condition.

    What it looks like

    Writing a Python-style double equal sign when the condition is SQL.

    Why it fails

    The supplied material specifies that SQL uses a single equal sign for equality testing.

    Do this instead
    Use = in SQL WHERE equality conditions and reserve == for the Python comparison described in the material.
  • Common mistake

    Expecting WHERE to sort rows.

    What it looks like

    Using a condition and assuming the returned rows will be arranged by a field.

    Why it fails

    WHERE filters rows; ORDER BY sorts results.

    Do this instead

    Use ORDER BY when the returned rows need to be organized by a specified column.

  • Common mistake

    Applying ORDER BY conceptually before deciding which rows qualify.

    What it looks like

    Reasoning about the complete result order before applying the WHERE condition.

    Why it fails

    The documented sequence applies WHERE filtering before ORDER BY sorting.

    Do this instead

    First identify the rows whose WHERE condition is true, then sort those filtered results.

  • Common mistake

    Treating AND and OR as interchangeable.

    What it looks like

    Replacing one logical operator with the other without reconsidering the combined requirement.

    Why it fails

    The source identifies AND and OR as logical operators for building conditions, so they express different combinations of comparisons.

    Do this instead

    Choose the operator that matches the intended combination of conditions, then evaluate the complete expression against each row.

Practice the Query Flow

MEDIUM

Describe the processing plan for a query that must keep rows where amount is greater than 100 and status is not equal to inactive, then sort the returned results by customer. State which part uses WHERE, which operators appear in the condition, and when ORDER BY is applied.

Hints
  • Use > for the amount comparison.
  • Use != for the status comparison.
  • Use AND when both comparisons form the requirement.
  • ORDER BY is applied after WHERE filtering.

What do you think happens?

A query has a WHERE condition and an ORDER BY clause. Which operation determines the rows that appear in the result?

  • WHERE filtering

  • ORDER BY sorting

  • Both operations select rows

Reveal answer
Answer:

WHERE filtering

WHERE tests each row and returns only rows whose condition is true. ORDER BY organizes the filtered results after that selection.

Key Takeaways

  1. 1
    WHERE tests a condition against each row and returns only rows for which the condition is true.
  2. 2
    Comparison operators include =, <, >, <=, >=, and !=.
  3. 3
    AND and OR combine comparisons into more complex WHERE conditions.
  4. 4
    SQL uses = for equality testing, while Python uses ==.
  5. 5
    ORDER BY sorts the results after WHERE filtering has taken place.

At a glance

  • Use WHERE to select only rows that satisfy a condition.
  • Build conditions with comparison operators and combine them with AND or OR.
  • Remember that SQL equality uses =, unlike Python's ==.
  • Apply ORDER BY after filtering when returned rows must be sorted.
  • The supplied material explains filtering and sorting around multi-table query work but does not provide specific JOIN syntax.

Related Topics

Related concepts

More concepts starting with J