SELECT Statements: Retrieving Data from Tables
SQL uses a single equal sign (=) for equality testing in WHERE clauses, not the double equal sign (==) used in Python.
From Table to Result
A SELECT statement retrieves data from a table, but retrieval does not have to mean returning every row. A WHERE clause tests a condition against each row. Only rows for which that condition is true are returned. After the matching rows have been selected, ORDER BY can sort those filtered results using a specified column.
Testing Each Row
The WHERE clause acts as a filter. Imagine a table containing several rows and a condition such as priority = 'high'. SQL tests that condition against each row. A row whose priority satisfies the condition passes through to the result; a row that does not satisfy it is left out. The test is performed for every row rather than for the table as one undivided object.
Selecting high-priority tasks
Suppose a tasks table contains rows with a priority column. Retrieve only rows whose priority is high.
Choose the condition: The required condition is priority = 'high'. In SQL, the single equal sign tests equality in a WHERE clause.
Test the rows: Each row is checked against the condition. Rows with priority high satisfy the test; rows with another priority do not.
Return the matches: Only the rows that satisfy the condition appear in the result.
The result contains only high-priority task rows.
Combining Conditions
A WHERE clause can contain more than one comparison. AND combines conditions so that the combined test is satisfied only when the required conditions are both true. OR provides an alternative: the combined test is satisfied when either condition is true. These logical operators let you build more complex filtering conditions.
Applying AND and OR
Consider a generated products table with category and stock columns. Compare the filters category = 'books' AND stock > 0 with category = 'books' OR stock > 0.
Evaluate AND: A row must have category equal to books and also have stock greater than 0. A row failing either test does not satisfy the combined condition.
Evaluate OR: A row satisfies the combined condition if its category is books, if its stock is greater than 0, or if both statements are true.
Compare the result sets: The AND condition requires both tests together, while the OR condition accepts either test. Therefore, the OR filter can admit rows that would not pass the AND filter.
AND narrows the rows to those satisfying both conditions; OR accepts rows satisfying at least one of the alternatives.
Filtering Before Sorting
WHERE and ORDER BY have different jobs. WHERE decides which rows belong in the result by testing a condition. ORDER BY organizes the rows that remain according to a specified column. The filtering step comes first conceptually: SQL first identifies the rows that satisfy WHERE, then sorts those filtered results.
Selecting and organizing open tasks
Suppose a tasks table has a status column and a priority column. Retrieve rows whose status is open and organize the returned rows by priority.
Filter by status: Use a WHERE condition that tests status = 'open'. Closed rows are not part of the result.
Choose the sort field: Use ORDER BY priority to organize the rows that passed the status test.
Read the result: The result contains only open tasks, and those returned rows are organized by the selected priority column.
The query returns the open-task rows in priority order.
Mistakes to Avoid
Writing == for equality in a SQL WHERE clause.
SQL uses a single equal sign for equality testing in WHERE clauses. The double equal sign is identified with Python syntax instead.
Fix:
Write status = 'open'.Expecting WHERE to arrange rows.
WHERE filters rows, while ORDER BY sorts the filtered results.
Fix:
Use ORDER BY with the column that should organize the returned rows.Treating AND and OR as interchangeable.
AND requires the combined conditions to be satisfied together, while OR accepts either condition.
Fix:
Choose AND when the row must satisfy both tests and OR when either test is sufficient.Forgetting that WHERE is tested against each row.
The WHERE condition is tested against each row, and only rows where it is true are returned.
Fix:
Evaluate the condition row by row.
| Context | Equality form | Purpose |
|---|---|---|
| SQL WHERE clause | = | Equality testing |
| Python | == | Equality syntax identified in the source comparison |
Practice and Review
A generated inventory table contains item, category, stock, and status columns. Describe a SELECT query that returns only rows where category is 'hardware' and stock is greater than 0, then organizes the returned rows by item. Also state which equality symbol belongs in the SQL WHERE condition.
Hints
- Use WHERE for the row conditions.
- Join the category and stock tests with AND.
- Use ORDER BY with item after the filtering condition.
- SQL equality uses =, not ==.
- SELECT retrieves rows from a table. WHERE tests a condition against each row and returns only rows for which the condition is true. Comparison operators and the logical operators AND and OR can form simple or complex filtering conditions. SQL uses one equal sign for equality in WHERE clauses, unlike the double equal sign used in Python. ORDER BY sorts the results that remain after filtering.
Key Takeaways
- WHERE filters rows by testing a condition against each row.
- Comparison operators and AND or OR allow conditions to be combined.
- SQL uses = for equality in WHERE clauses, while Python uses ==.
- ORDER BY organizes the results after WHERE filtering.