Concepts / SELECT Statements: Retrieving Data from Tables

SELECT Statements: Retrieving Data from Tables

A SELECT statement retrieves data from a table, but retrieval does not have to mean returning every row.

  • 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…

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.

orders tableall rowsrow 1condition truereturned rowsmatching rows onlyrow 2condition falseinspectinspectkeepexclude
How does a SELECT statement inspect table rows and keep only the rows that satisfy a condition?

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.

Worked example

Selecting high-priority tasks

Suppose a tasks table contains rows with a priority column. Retrieve only rows whose priority is high.

  1. 1
    Choose the condition

    The required condition is priority = 'high'. In SQL, the single equal sign tests equality in a WHERE clause.

  2. 2
    Test the rows

    Each row is checked against the condition. Rows with priority high satisfy the test; rows with another priority do not.

  3. 3
    Return the matches

    Only the rows that satisfy the condition appear in the result.

Result

The result contains only high-priority task rows.

=equalityrow conditiontrue or false<less than>greater than<=less than or equal>=greater than or equal!=not equaltesttesttesttesttesttest
How do the available comparison operators determine which rows satisfy a WHERE condition?

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.

Worked example

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.

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

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

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

Result

AND narrows the rows to those satisfying both conditions; OR accepts rows satisfying at least one of the alternatives.

product rowcategory = booksAND resultboth conditionsstock > 0OR resulteither conditiontesttestANDANDOROR
How do AND and OR change which rows pass through a WHERE clause when multiple conditions are evaluated?

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.

Worked example

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.

  1. 1
    Filter by status

    Use a WHERE condition that tests status = 'open'. Closed rows are not part of the result.

  2. 2
    Choose the sort field

    Use ORDER BY priority to organize the rows that passed the status test.

  3. 3
    Read the result

    The result contains only open tasks, and those returned rows are organized by the selected priority column.

Result

The query returns the open-task rows in priority order.

priority 3priority 1priority 1priority 2priority 2priority 3reorderedreorderedreordered
What changes in the returned result when rows are reordered by a selected field?

Mistakes to Avoid

  • Common mistake

    Writing == for equality in a SQL WHERE clause.

    What it looks like
    status == 'open'
    Why it fails

    SQL uses a single equal sign for equality testing in WHERE clauses. The double equal sign is identified with Python syntax instead.

    Do this instead
    Write status = 'open'.
  • Common mistake

    Expecting WHERE to arrange rows.

    What it looks like

    Filtering open rows and assuming they will appear in a particular order.

    Why it fails

    WHERE filters rows, while ORDER BY sorts the filtered results.

    Do this instead

    Use ORDER BY with the column that should organize the returned rows.

  • Common mistake

    Treating AND and OR as interchangeable.

    What it looks like

    Replacing category = 'books' AND stock > 0 with category = 'books' OR stock > 0 without considering the changed result.

    Why it fails

    AND requires the combined conditions to be satisfied together, while OR accepts either condition.

    Do this instead

    Choose AND when the row must satisfy both tests and OR when either test is sufficient.

  • Common mistake

    Forgetting that WHERE is tested against each row.

    What it looks like

    Thinking that one matching row causes every row in the table to be returned.

    Why it fails

    The WHERE condition is tested against each row, and only rows where it is true are returned.

    Do this instead

    Evaluate the condition row by row.

ContextEquality formPurpose
SQL WHERE clause=Equality testing
Python==Equality syntax identified in the source comparison

Use the equality syntax belonging to the language in which the condition is written.

SQL WHERE=Python==
What is the difference between using a single equal sign in SQL and a double equal sign in Python when testing equality?

Practice and Review

MEDIUM

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

Related Topics

More concepts starting with S