SQL Basics — SELECT, WHERE, ORDER BY, LIMIT

Runtime contract: Run the Python lab with Python 3 and its SQLite module; no database service is required. PostgreSQL 16 is the separately documented reference dialect. SQLite results are not evidence that every other engine accepts the same syntax or chooses the same plan.

Why this session exists

SQL describes a desired result rather than a loop for finding it. Like ordering a meal rather than specifying each kitchen action, it lets an optimiser choose among valid implementations. That freedom is useful, not a promise that every plan is optimal or that the same query stays fast forever.

This session teaches single-table SELECT/FROM/WHERE/ORDER BY/LIMIT, logical clause order, NULL logic, safe parameters, pagination and basic plan interpretation. It also previews why predicate placement around an outer join changes the answer. The core ideas transfer across engines; row-limiting, string matching, date arithmetic and alias rules need dialect checks.

Prerequisites

Read the relational model. Know that a SQL result can contain duplicates and that NULL is a missing-value marker, not the empty string or numeric zero.

Historical context and portability

SQL grew out of IBM's System R/SEQUEL work. Standardisation did not make all later vendor syntax interchangeable: in particular, LIMIT is not a feature guaranteed by the first SQL standard. PostgreSQL supports LIMIT and standard-style FETCH FIRST; SQL Server has its own TOP/OFFSET-FETCH forms. SQLite lacks PostgreSQL's ILIKE and ~ operators. Learn semantics first, then verify the deployed dialect rather than relying on a claimed “90% portable” percentage.

The logical pipeline, not the physical schedule

For the SELECT forms used here, think:

FROM → WHERE → GROUP BY/aggregates → HAVING → window calculations → SELECT output → DISTINCT → ORDER BY → LIMIT/OFFSET.

This is a model of meaning and name visibility, not a CPU execution trace. Relational operators compose because their outputs can serve as inputs to other relational operators; SQL's bag/NULL extensions require correspondingly valid transformation rules. Equivalent logical expressions can have very different intermediate sizes, so cost estimates choose among them. An optimiser may push a safe filter into a scan, choose an index that supplies sorted rows, combine operations or stop once enough rows are found. Some output expressions can be deferred. Inner-join equivalences have conditions; outer joins, NULL-sensitive predicates and volatile expressions cannot be reordered indiscriminately.

In PostgreSQL 16 a SELECT output alias is available in ORDER BY and, with rules for ambiguity, GROUP BY, but not WHERE or HAVING. SQLite permits some nonstandard alias references in WHERE; do not use that extension as evidence for PostgreSQL behaviour. A portable fix is to repeat the expression or name the computed relation in a subquery.

-- PostgreSQL 16: total is an output alias, not an input column.
-- SELECT price * qty AS total FROM line_items WHERE total > 100;
SELECT price * qty AS total
FROM line_items
WHERE price * qty > 100
ORDER BY total DESC;

The original “SELECT is second to last” shortcut was wrong even for its own list. More importantly, a clause diagram cannot guarantee a physical scan order. Read plans to learn implementation; read SQL semantics to establish correctness.

Aggregate NULL contract (SQLite and PostgreSQL 16): COUNT(*) counts input rows, including rows whose salary is NULL; COUNT(salary) counts only non-NULL salary values. In the seven-employee fixture below these return 7 and 6, so COUNT(*) - COUNT(salary) reports one missing salary, not a zero salary. Numeric SUM, AVG, MIN and MAX ignore NULL inputs: for values 10, NULL and 20, the sum is 30 and the average is 15, not 10. Replacing NULL with zero before averaging changes the denominator and the meaning.

With only NULL inputs, COUNT(salary) is 0 while SUM(salary), AVG(salary), MIN(salary) and MAX(salary) are NULL. An ungrouped aggregate over no input rows still returns one result row: both counts are 0 and those numeric aggregates are NULL; an ordinary GROUP BY dept over no rows produces no groups. Do not generalise this to every aggregate: PostgreSQL 16 array_agg includes NULL elements, and SQLite's nonstandard total() returns 0.0 for no non-NULL inputs. These are the documented PostgreSQL 16 aggregate and SQLite aggregate contracts, not a claim that all SQL engines or aggregates behave alike.

Employee fixture and worked query

SELECT name, salary
FROM employees
WHERE dept = 'Eng' AND salary > 100000
ORDER BY salary DESC, id
LIMIT 2;

Result: Dan 140000, Alice 120000. Alice and Dan pass the predicate; sorting puts Dan first. Bob fails the salary test. The limit removes no further row here. Add id as a unique tie-breaker so equal salaries have a stable order.

WHERE operators and three-valued logic

• =, <> (also != in these engines), <, <=, > and >= compare compatible values. BETWEEN a AND b includes both endpoints.
• IN tests membership. NOT IN needs special care with NULLs. % in LIKE means any sequence of characters, _ means one character. Matching literal wildcard characters requires an appropriate escape policy. Collation/case behaviour is dialect-specific; SQLite's default ASCII-insensitive LIKE is not a universal Unicode case-folding rule.
• Use properly typed dates in PostgreSQL. Fixed-width ISO date TEXT sorts chronologically in this SQLite fixture; arbitrary localised strings do not. For timestamp intervals, prefer a half-open interval such as created_at >= start AND created_at < next_start, with an explicit timezone definition.
• AND binds more tightly than OR. Parenthesise alternatives to express intent; parentheses do not require the engine to evaluate subexpressions left-to-right.
• IS NULL/IS NOT NULL test missingness. COALESCE(x,y,z) returns the first non-null argument; it changes the value, not what was measured.

Ordinary comparisons with NULL return UNKNOWN. WHERE keeps TRUE only. Thus salary <> 60000 excludes Frank (FALSE) and Grace (UNKNOWN), while salary <> 60000 OR salary IS NULL includes Grace. IS DISTINCT FROM is a deliberately null-aware comparison in PostgreSQL 16; the general statement “any comparison with NULL is UNKNOWN” must exclude such predicates.

This explains the anti-join trap: a nullable exclusion list can make otherwise unmatched keys UNKNOWN. Prefer NOT EXISTS when the intended question is absence of an equal row; decide separately how a missing left-hand key should behave.

Do not use COALESCE(salary,0) to silently claim Grace earns nothing. A UI may label an absent salary “Unknown”; a statistic should normally report both eligible and observed counts. If a refund amount is missing, changing NULL to zero does not recover the missing refund value. That was a substantive error in the original incident's proposed fix.

Three habits: projection, aliases and parameters

Explicit output columns make an application's data contract visible, limit unnecessary transfer and may enable an index-only or column-pruned plan. SELECT * is still useful for bounded exploration and EXISTS(SELECT 1 ...) need not retrieve a table's payload. It does not universally defeat prepared-statement caches or bypass column-level permissions. If a hypothetical new field adds 2 KB to each of 2,000 returned rows, that is about 4 MB of extra payload; it is not evidence for a particular latency change.

Give computed values meaningful aliases with AS. Bind untrusted values using driver placeholders. For Python SQLite:

# Fragment: insert after fixture creation in the complete lab below,
# before conn.close(); it needs that lab's live cur cursor.
requested_dept = "Eng' OR 1=1 --"
assert cur.execute(
    "SELECT name FROM employees WHERE dept = ?", (requested_dept,)
).fetchall() == []

The malicious-looking text remains data. Do not explain binding merely as adding escapes: separation of value and SQL structure is the contract. A placeholder does not substitute a table name, column identifier or ASC/DESC keyword. Choose those from a fixed application allowlist, and retain authorisation predicates on every request. Driver details differ; for example, SQLite execute() accepts a single statement, so a concatenated DROP TABLE story is not a reproduction of its API behaviour. Injection remains dangerous without stacked statements.

Pagination with an explicit order

LIMIT 3 OFFSET 3 skips three rows of the ordered result and returns up to the next three. On the fixture ordered by id, this gives Dan, Eve, Frank. WHERE id > 3 ORDER BY id LIMIT 3 gives the same page; IDs need not be consecutive. The B-tree seeks beyond the cursor, not necessarily to cursor + 1.

Offset work usually grows with the skipped qualifying rows. A suitable keyset index can instead offer a seek plus the work needed to find k eligible rows, roughly O(log n + k) in a simple model. Neither a constant-time guarantee nor a production speedup follows: additional filters, heap reads and plan choices can dominate.

For a newest-first feed ordered by (hired DESC, id DESC), both cursor values must be included. This SQLite/PostgreSQL-compatible shape assumes hired is non-null:

SELECT id, name, hired
FROM employees
WHERE hired < '2022-02-01'
   OR (hired = '2022-02-01' AND id < 5)
ORDER BY hired DESC, id DESC
LIMIT 3;

Result: Bob, Alice, Carol. If two rows share a timestamp and only the timestamp is saved, the next page can skip the remaining tied row. Conversely, adding <= can repeat the last row. A composite cursor fixes that boundary, not concurrent updates: an edited sort value can still move across pages. A stable export needs a snapshot/version strategy, not just a cursor. Validate cursor structure and reapply permissions; signing a cursor does not replace authorisation.

City-feed exercise and answer

Create 20 rows with IDs 1–20 spread across three cities in a disposable fixture. First page ORDER BY id LIMIT 5 returns IDs 1–5. After cursor 15, WHERE id > 15 ORDER BY id LIMIT 5 returns 16–20, matching LIMIT 5 OFFSET 15 on unchanged data. The OFFSET is still 15 if the table grows to 100 million rows; it does not magically become 15 million. If a row before the cursor is deleted, OFFSET positions shift but keyset continues after identity 15. Inserts with smaller IDs will not appear in that continuation, which may or may not meet the product's feed contract.

Hands-on: run all single-table examples

Save as sql_basics.py. The complete lab retains the original seven-row fixture and prints each query/result. The negative-control exercise below makes the precedence bug observable rather than relying on a coincidental output.

"""sql_basics.py — SELECT / WHERE / ORDER BY / LIMIT + the classic NULL gotchas."""
from __future__ import annotations
import sqlite3
 
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
 
cur.executescript("""
CREATE TABLE employees (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    dept   TEXT NOT NULL,
    salary INTEGER,                  -- nullable ON PURPOSE for demo
    hired  TEXT NOT NULL
);
INSERT INTO employees VALUES
    (1, 'Alice', 'Eng',   120000, '2020-03-01'),
    (2, 'Bob',   'Eng',    95000, '2021-06-15'),
    (3, 'Carol', 'HR',     80000, '2019-01-10'),
    (4, 'Dan',   'Eng',   140000, '2018-09-20'),
    (5, 'Eve',   'Sales', 110000, '2022-02-01'),
    (6, 'Frank', 'Sales',  60000, '2023-11-05'),
    (7, 'Grace', 'HR',      NULL, '2024-05-12');
""")
 
def run(label: str, sql: str, params: tuple = ()) -> None:
    print(f"\n-- {label} --")
    print(sql.strip())
    for row in cur.execute(sql, params):
        print(" ", row)
 
# 1. Basic SELECT + WHERE + ORDER BY + LIMIT
run("Top 2 Eng earners over 100k",
    """SELECT name, salary FROM employees
       WHERE dept = 'Eng' AND salary > 100000
       ORDER BY salary DESC, id LIMIT 2;""")
 
# 2. Precedence bug — AND binds tighter than OR
run("Precedence BUG · name starts with A or E, in HR or Sales, 80k-120k",
    """SELECT name, dept, salary FROM employees
       WHERE salary BETWEEN 80000 AND 120000
         AND dept IN ('HR','Sales')
         AND name LIKE 'A%' OR name LIKE 'E%';""")
 
# Same query with parentheses — the intended answer
run("Precedence FIXED · with parentheses around the OR",
    """SELECT name, dept, salary FROM employees
       WHERE salary BETWEEN 80000 AND 120000
         AND dept IN ('HR','Sales')
         AND (name LIKE 'A%' OR name LIKE 'E%');""")
 
# 3. NULL handling — the classic trap
run("Employees with UNKNOWN salary (IS NULL)",
    "SELECT name FROM employees WHERE salary IS NULL;")
 
run("Wrong way: `= NULL` returns zero rows (silent bug)",
    "SELECT name FROM employees WHERE salary = NULL;")
 
run("`salary != 60000` — does Grace appear? (three-valued logic)",
    "SELECT name, salary FROM employees WHERE salary != 60000;")
 
run("`salary != 60000 OR salary IS NULL` — explicit NULL handling",
    "SELECT name, salary FROM employees WHERE salary != 60000 OR salary IS NULL;")
 
# 4. COALESCE for a default
run("Show salary as 0 when NULL, sort ascending",
    "SELECT name, COALESCE(salary, 0) AS salary FROM employees ORDER BY salary ASC, id;")
 
# 5. DISTINCT + LIMIT/OFFSET pagination
run("Distinct departments alphabetically",
    "SELECT DISTINCT dept FROM employees ORDER BY dept;")
 
run("Page 2 of employees, 3 per page (LIMIT 3 OFFSET 3)",
    "SELECT id, name FROM employees ORDER BY id LIMIT 3 OFFSET 3;")
 
# 6. Keyset pagination — index-supported seek plus eligible result work
run("Keyset pagination — next page after id=3",
    "SELECT id, name FROM employees WHERE id > ? ORDER BY id LIMIT 3;", (3,))
 
# 7. Parameterised query — the ANTI-SQL-INJECTION pattern
run("Safe: parameterised query", "SELECT name FROM employees WHERE dept = ?", ('Eng',))
 
# 8. EXPLAIN — see what the planner is doing
run("EXPLAIN plan for the top-earners query",
    """EXPLAIN QUERY PLAN
       SELECT name FROM employees
       WHERE dept='Eng' ORDER BY salary DESC, id LIMIT 2;""")
 
# Add an index and re-EXPLAIN
cur.execute("CREATE INDEX ix_dept_salary ON employees(dept, salary DESC)")
run("EXPLAIN after CREATE INDEX",
    """EXPLAIN QUERY PLAN
       SELECT name FROM employees
       WHERE dept='Eng' ORDER BY salary DESC, id LIMIT 2;""")
 
conn.close()

Read the actual results

• Both original precedence queries return Eve only. Alice is in Eng, so the intended HR/Sales filter excludes her. The original answer claiming Alice and Eve was wrong.
• Add (8,'Eli','Eng',50000,'2024-06-01') temporarily. The unparenthesised query now incorrectly includes Eli via its standalone name LIKE 'E%' alternative; the parenthesised one still returns only Eve. Delete that fixture row before comparing the original later results.
• IS NULL finds Grace; = NULL finds nobody. Excluding salary 60000 yields Alice, Bob, Carol, Dan and Eve; the explicit NULL alternative adds Grace.
• The display-only COALESCE ordering is Grace, Frank, Carol, Bob, Eve, Alice, Dan.
• Distinct departments are Eng, HR, Sales. Both id-based second-page variants return (4,Dan),(5,Eve),(6,Frank).
• Three latest hires, using ORDER BY hired DESC, id DESC LIMIT 3, are Grace, Frank and Eve.

EXPLAIN and the optimiser's information

SQLite's EXPLAIN QUERY PLAN distinguishes a SCAN from a selective SEARCH and may show a temporary B-tree for sorting. The lab records the plan before/after (dept, salary DESC) is added. Do not freeze the entire printed plan string as a cross-version contract. A scan can itself use an index, including a covering index; “SCAN” is not synonymous with a broken query.

PostgreSQL EXPLAIN describes an estimated plan; EXPLAIN (ANALYZE, BUFFERS) executes the statement and reports actual rows, loops and buffer behaviour. Never run it casually on a destructive statement or an expensive production query. There is no universal 10,000-row safety threshold or 30-second inspection cost.

Bad cardinality estimates can lead to poor join/scan choices. Fresh statistics, appropriate indexes, smaller eligible inputs and narrower projected rows are all possible remedies. The old claim that changing SELECT columns cannot help was false: projection affects transfer, sort/hash memory and covering-index opportunities. Accurate statistics also do not guarantee an optimal plan.

Preview: WHERE versus ON around an outer join

For an inner join and ordinary deterministic predicates, placing a condition in ON or WHERE can be semantically equivalent; identical physical plans are not guaranteed. In a LEFT JOIN, ON ... AND o.status='paid' defines eligible right-side matches while retaining every left row. WHERE o.status='paid' instead rejects NULL-extended unmatched rows. Not every WHERE condition converts a LEFT JOIN to INNER: WHERE c.active=1 on the preserved side, for example, has a different purpose.

A prefiltered right-side CTE can express the same optional-match intent. Explain each input and output grain, then test zero matches, one match and multiple matches. A LEFT JOIN before later filters preserves every left input row at least once; multiple matches may legitimately multiply it. This correctness reasoning matters more than choosing a preferred clause by habit.

Recall and worked answers

1. Logical order? FROM, WHERE, grouping/aggregates, HAVING, windows/output, DISTINCT, ORDER BY, limit. It is not a physical execution promise.
2. Why = NULL returns no matching row? UNKNOWN is not TRUE; use IS NULL or a deliberately null-aware comparison.
3. Why enumerate columns? A schema addition should not silently widen an application response or its data dependencies.
4. Top three hires? SELECT id,name,hired FROM employees ORDER BY hired DESC,id DESC LIMIT 3; answer Grace, Frank, Eve.
5. What is LIMIT 10 OFFSET 20? Skip 20 of the ordered result, return at most 10. Without deterministic ordering the page is not stable.
6. SQL injection fix? Bind values, allowlist dynamic SQL structure, enforce permissions and use least privilege. One parameter cannot represent an entire SQL expression.
7. AND/OR trap? A AND B OR C means (A AND B) OR C. With the original seven employees both versions coincidentally returned Eve; Eli is the distinguishing test.
8. COALESCE? A value substitution policy. Zero is not synonymous with unknown salary or unknown refund.
9. What does a plan establish? The selected/observed access strategy for that engine, configuration and data, not an asymptotic theorem or general speed guarantee.
10. Keyset stretch? WHERE id > :last_seen_id ORDER BY id LIMIT 20 avoids a large positional skip when supported by an appropriate index. Timestamps require a unique tie-breaker and the same ordering/predicate pair.

Explain aloud: SQL is declarative; query meaning and physical strategy are separate; projection and parameter binding make application contracts safer. An outer join filter or a missing-value policy is a business decision, not just syntax.

Sources and next step

Read joins, then grouping, windows, CTEs and indexes. Those extend the same logical pipeline; an ETL system may also contain non-SQL work.

Primary sources inspected 7 October 2026: PostgreSQL 16 SELECT, comparison predicates, subquery expressions, EXPLAIN, Python 3.12 sqlite3 parameter binding, SQLite query plans and IBM SQL lineage. SQLite docs are mutable access-date snapshots. No PostgreSQL runtime, production load test or vendor-feed implementation was reproduced.