Concepts / Joins: Combining Data from Multiple Tables

Joins: Combining Data from Multiple Tables

SQL uses a single equal sign (=) for equality testing in WHERE clauses, not the double equal sign (==) used in Python.

  • Programming

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.

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.

test each rowtrueAll rowsrows before testingWHERE conditiontrue or falseReturned rowscondition is true
Which rows are retained when a WHERE condition is applied?

Selecting Rows by a Condition

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

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

Test each row: The WHERE clause tests score > 80 against every row.

Keep qualifying rows: Only rows for which the condition is true are returned.

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.

testtestcombinecombinecombinecombinetruetrueRowCondition AcomparisonANDcombined conditionReturned rowWHERE condition is trueCondition BcomparisonORcombined condition
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.

Combining Two Requirements

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

Write the first comparison: The first condition is score >= 70.

Write the second comparison: The second condition is status != inactive.

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

Apply the complete condition: The WHERE clause tests the combined expression against each row.

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

testkeep true rowspass onarrange by columnRowsWHEREfilterFiltered rowsORDER BYsortOrdered results
How does a query move from all available rows to an ordered result?

Filtering, Then Sorting

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

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

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

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

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

Common Query Mistakes

  • Using == for equality in a SQL WHERE condition.

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

    Fix: Use = in SQL WHERE equality conditions and reserve == for the Python comparison described in the material.

  • Expecting WHERE to sort rows.

    WHERE filters rows; ORDER BY sorts results.

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

  • Applying ORDER BY conceptually before deciding which rows qualify.

    The documented sequence applies WHERE filtering before ORDER BY sorting.

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

  • Treating AND and OR as interchangeable.

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

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

Key Takeaways

  • 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

  • Conditional Row Selection with WHERE
  • Logical and Comparison Operators
  • Sorting Query Results with ORDER BY