Subqueries: Nesting SELECT Statements
SQL uses a single equal sign (=) for equality testing in WHERE clauses, not the double equal sign (==) used in Python.
The Filtering Question
A SELECT statement can be understood as a sequence of decisions about rows. A WHERE clause tests a condition against each row, and only rows for which that condition is true are returned. In a nested SELECT statement, the inner SELECT supplies results that the outer SELECT uses while determining its final result. This article focuses on the conditions that control row selection and on the ordering of the rows that remain.
Tracing Row Selection
Think of WHERE as a row-by-row filter. The condition is tested against each row separately. A row that makes the condition true passes through to the returned result; a row that does not make the condition true is filtered out. A nested SELECT adds an inner selection step whose results are used by the outer SELECT as part of this decision.
Following a WHERE Decision
Suppose a query's WHERE condition is designed to keep only rows that meet a stated criterion. What happens to a row that satisfies the criterion and to a row that does not?
Test the first row: The WHERE condition is evaluated against that row.
Keep a true result: If the condition is true, the row is included in the returned results.
Reject a false result: If the condition is not true, the row is filtered out.
The returned result contains only rows for which the WHERE condition is true.
Building Conditions
Comparison operators express a relationship to test. SQL WHERE clauses can use =, <, >, <=, >=, and !=. Logical operators combine conditions: AND requires the combined condition to account for both parts, while OR allows a condition made from either part to be used.
| Operator group | Operators | Purpose |
|---|---|---|
| Equality and inequality | =, != | Test whether values are equal or not equal |
| Ordering comparisons | <, >, <=, >= | Compare values using less-than, greater-than, or an inclusive boundary |
| Logical combination | AND | Combine conditions so both parts are considered |
| Logical alternative | OR | Combine conditions so either part can provide a match |
Operators named in the source material for constructing WHERE conditions.
A complex WHERE condition is still evaluated as a condition for each row. First identify the comparisons being made, then identify how AND or OR combines those comparisons. This makes the filter easier to inspect: each comparison contributes a result, and the logical operator determines how the combined condition is formed.
Equality in SQL and Python
| Context | Equality syntax | Meaning in this lesson |
|---|---|---|
| SQL WHERE clause | = | Equality testing |
| Python | == | The programming-language equality syntax contrasted with SQL syntax |
Ordering the Result
ORDER BY organizes the rows that are returned by sorting them according to a specified column. The order of operations matters conceptually: WHERE first filters the rows, and ORDER BY then sorts the filtered results. Therefore, ORDER BY does not restore rows removed by WHERE; it organizes the rows that remain.
Separate the two jobs: WHERE decides which rows are present, while ORDER BY organizes the rows that remain by a specified column.
Mistakes to Avoid
Writing == for equality in a SQL WHERE clause
The source material specifies that SQL uses a single equal sign for equality testing in WHERE clauses.
Fix:
Use = for equality in SQL WHERE conditions.Treating WHERE as a sorting instruction
WHERE filters rows, whereas ORDER BY sorts the filtered results.
Fix:
Use WHERE to select rows and ORDER BY to organize the rows that remain.Forgetting that a condition is tested against each row
The WHERE clause tests the condition against each row, and only rows where the condition is true are returned.
Fix:
Evaluate the condition row by row.Using only one comparison when the requirement contains multiple conditions
AND and OR are available for constructing logical expressions from multiple conditions.
Fix:
Use AND or OR to express how the conditions should be combined.
Practice Check
A query must return only rows that satisfy a condition and then organize those returned rows by a selected column. Explain which clause performs each job. Then state which equality symbol belongs in the SQL WHERE condition and which symbol is associated with Python.
Hints
- The filtering clause tests a condition against each row.
- The sorting clause is applied after filtering.
- SQL and Python use different equality syntax in the comparison given here.
What do you think happens?
Predict the result of applying WHERE and then ORDER BY: will ORDER BY sort rows that WHERE already filtered out?
Reveal answer
Answer: No. WHERE determines which rows remain, and ORDER BY sorts the filtered results.
The source material states that ORDER BY is applied after WHERE filtering, so ordering affects the returned rows rather than restoring filtered-out rows.
Key Takeaways
- A nested SELECT places one SELECT statement inside another so the inner results participate in the outer selection.
- WHERE tests a condition against each row and returns only rows for which the condition is true.
- Comparison operators include =, <, >, <=, >=, and !=; AND and OR combine logical conditions.
- SQL uses = for equality testing in a WHERE clause, while Python uses ==.
- WHERE filters first, and ORDER BY sorts the filtered results by a specified column.
Key Takeaways
- Subqueries nest SELECT statements so an inner SELECT supplies results used by an outer SELECT.
- WHERE performs conditional row selection by testing every row.
- Comparison and logical operators build simple or complex WHERE conditions.
- SQL equality uses =, not Python's ==.
- ORDER BY sorts the rows that remain after WHERE filtering.