SELECT, Filtering, Expressions, and NULL

SELECT, filtering, expressions, and NULL are the foundation of reading data from PostgreSQL. The outcome of this lesson is practical: given a table, you should be able to choose columns, compute values, filter rows, and predict how missing or unknown values affect the result.

This chapter fits the querying section because most later PostgreSQL features still depend on the same evaluation rules. Joins, aggregates, indexes, views, and application reports all become easier to reason about when you understand what a simple SELECT statement asks PostgreSQL to do.

What SELECT Really Does

A SELECT statement describes a table-shaped result. PostgreSQL parses the SQL text, checks names and types, rewrites parts of the query when rules or views are involved, plans an execution strategy, and then returns rows. The visible syntax starts with SELECT, but the logical processing order is easier to understand as FROM, then WHERE, then the select list, then ordering and limiting. The engine first determines candidate rows, removes rows whose filter is not true, computes output expressions, and finally formats the result columns.

The select list is not limited to stored columns. It can contain arithmetic, string operations, function calls, casts, comparisons, and conditional expressions. Each expression has a data type, and PostgreSQL chooses operators and functions using those types. For example, quantity * unit_price is a numeric expression when both inputs are numeric. lower(email) is a text expression. placed_at::date converts a timestamp to a date for display or comparison.

WHERE is a row filter. For each candidate row, the predicate evaluates to true, false, or NULL. Only true keeps the row. This is the point that surprises many developers: in SQL, NULL means unknown or inapplicable, so a comparison such as shipped_at < now() is unknown when shipped_at is NULL. Unknown is not true, so the row is filtered out unless the predicate explicitly handles it.

Syntax Anatomy

A compact query has four important areas:

SELECT column_or_expression [AS output_name], ...
FROM table_name
WHERE boolean_expression
ORDER BY sort_expression;

SELECT names the output columns. AS gives an output column a readable alias; the keyword is optional, but using it is clearer for computed values. FROM identifies the input relation. WHERE contains a Boolean expression. ORDER BY makes result order deterministic for humans, tests, and examples. Without ORDER BY, PostgreSQL is free to return rows in any physical or planned order.

Common predicates include equality with =, inequality with <>, ranges with <, <=, >, and >=, set membership with IN, pattern matching with LIKE or ILIKE, and null tests with IS NULL or IS NOT NULL. Use AND, OR, and NOT to combine predicates. Parentheses are cheap and often prevent mistakes.

Example 1: Projecting and Filtering Rows

This first example creates a temporary table, inserts four order lines, selects a subset of columns, computes a line total, and filters to paid rows. The computed column does not exist in storage; PostgreSQL evaluates it for each surviving row.

CREATE TEMP TABLE lesson_orders (
    order_id integer PRIMARY KEY,
    customer text NOT NULL,
    status text NOT NULL,
    quantity integer NOT NULL,
    unit_price numeric(10,2) NOT NULL
);

INSERT INTO lesson_orders (order_id, customer, status, quantity, unit_price) VALUES
    (1, 'Ada', 'paid', 2, 12.50),
    (2, 'Ben', 'draft', 1, 20.00),
    (3, 'Cy', 'paid', 3, 5.00),
    (4, 'Diya', 'cancelled', 4, 7.25);

SELECT order_id,
       customer,
       quantity * unit_price AS line_total
FROM lesson_orders
WHERE status = 'paid'
ORDER BY order_id;

The result contains orders 1 and 3 because only those rows make status = 'paid' true. The expected values are Ada with 25.00 and Cy with 15.00. The draft and cancelled rows are never used to compute output expressions because they fail the filter.

Example 2: Expressions, Casts, and Aliases

Expressions become more useful when they shape raw data into an application-facing result. Here the query creates a display label, converts a timestamp to a date, and classifies orders with a CASE expression.

CREATE TEMP TABLE lesson_shipments (
    shipment_id integer PRIMARY KEY,
    customer text NOT NULL,
    placed_at timestamp NOT NULL,
    shipped_at timestamp,
    total numeric(10,2) NOT NULL
);

INSERT INTO lesson_shipments VALUES
    (10, 'Ada', '2026-01-02 09:15', '2026-01-03 11:00', 25.00),
    (11, 'Ben', '2026-01-02 10:30', NULL, 20.00),
    (12, 'Cy', '2026-01-05 14:00', '2026-01-08 08:00', 15.00);

SELECT shipment_id,
       customer || ' #' || shipment_id AS label,
       placed_at::date AS order_date,
       CASE
           WHEN shipped_at IS NULL THEN 'open'
           ELSE 'shipped'
       END AS fulfillment_state
FROM lesson_shipments
WHERE total >= 20.00
ORDER BY shipment_id;

The expected rows are shipments 10 and 11. Shipment 12 is filtered out by the total. Shipment 10 has state shipped; shipment 11 has state open because the query uses IS NULL instead of comparing the timestamp to another value. This example also shows that aliases name output columns, not stored columns.

Example 3: NULL and Three-Valued Logic

NULL is not an empty string, zero, false, or a special largest or smallest value. It is the absence of a known value. Ordinary comparisons involving NULL usually return unknown. PostgreSQL displays that result as a null field, and WHERE does not keep it.

CREATE TEMP TABLE lesson_reps (
    rep_id integer PRIMARY KEY,
    name text NOT NULL,
    region text
);

INSERT INTO lesson_reps VALUES
    (1, 'Iris', 'north'),
    (2, 'Jules', 'south'),
    (3, 'Kai', NULL);

SELECT name,
       region,
       region <> 'north' AS not_north
FROM lesson_reps
ORDER BY rep_id;

SELECT name
FROM lesson_reps
WHERE region <> 'north'
ORDER BY name;

SELECT name
FROM lesson_reps
WHERE region IS NULL OR region <> 'north'
ORDER BY name;

The first query shows Iris as false, Jules as true, and Kai as unknown for not_north. The second query returns only Jules because unknown is not true. The third query returns Jules and Kai because it explicitly includes rows where the region is missing.

Design Choices and Trade-Offs

Choose whether a missing value is valid in the model before writing filters around it. If a column should always be known, declare it NOT NULL and avoid downstream ambiguity. If the value can genuinely be unknown, use IS NULL, IS NOT NULL, COALESCE, or CASE deliberately. COALESCE(region, 'unassigned') is useful for presentation, but using it in a predicate can hide the difference between a real value and a missing value.

Keep filters sargable when possible, meaning the predicate can use an index efficiently. A predicate such as WHERE customer = 'Ada' can use a normal index on customer. A predicate such as WHERE lower(customer) = 'ada' may require an expression index on lower(customer) or a case-insensitive type strategy. The expression is valid either way; the trade-off is between query convenience, storage cost, write overhead, and planner options.

Prefer explicit column lists over SELECT * in application code. The star is fine while exploring, but it couples clients to every column in the table, including columns added later. Explicit projection reduces network transfer, avoids accidental exposure of sensitive fields, and makes tests easier to read.

Failure Modes and Troubleshooting

Symptom: a query intended to find non-northern reps misses rows with no region. Cause: region <> 'north' evaluates to unknown for NULL. Diagnostic: run a select list that exposes the predicate, such as SELECT region, region <> 'north' FROM .... Correction: decide whether missing regions should be included, then write region IS NULL OR region <> 'north' or require region NOT NULL.

Symptom: a report changes order between runs. Cause: the query has no ORDER BY, so result order is not guaranteed. Diagnostic: inspect the SQL used by the report or run EXPLAIN and notice that physical access order may vary. Correction: add an ORDER BY on stable columns that match the report requirement.

Symptom: a filter is slow after applying a function to a column. Cause: the planner may not be able to use a plain index for the transformed value. Diagnostic: run EXPLAIN or EXPLAIN (ANALYZE, BUFFERS) on representative data and compare scan type and row counts. Correction: rewrite the predicate, add an appropriate expression index, or store a normalized value if that matches the data model.

Security, Performance, and Reliability

Projection is a security and performance choice. Selecting only needed columns reduces accidental leakage and lowers transfer cost. Filtering in SQL is usually more reliable than fetching many rows and filtering in application memory, because the database can use indexes, statistics, and a consistent snapshot. Use bind parameters in application code instead of concatenating user input into WHERE clauses; the SQL shape should be fixed while values are supplied separately.

NULL handling is also a reliability issue. A missing value can silently change eligibility, totals, and report counts. Tests should include at least one row with a null value for every nullable column that participates in a predicate or expression.

Hands-On Lab

Prerequisites: a PostgreSQL session where you can create temporary tables. The lab uses only temporary objects, so it will not modify permanent schema state.

1. Create the lab table and data with the script below.
2. Run the verification queries and compare the returned names with the comments.
3. Change one NULL value to a concrete value and rerun the filters.
4. Use EXPLAIN on each SELECT to observe the plan shape on your small test table.
5. Cleanup by ending the session, or run DROP TABLE lesson_lab_customers;.

CREATE TEMP TABLE lesson_lab_customers (
    customer_id integer PRIMARY KEY,
    email text NOT NULL,
    country text,
    lifetime_value numeric(10,2) NOT NULL,
    blocked_at timestamp
);

INSERT INTO lesson_lab_customers VALUES
    (1, 'ada@example.com', 'US', 120.00, NULL),
    (2, 'ben@example.com', NULL, 80.00, NULL),
    (3, 'cy@example.com', 'CA', 40.00, '2026-02-01 12:00'),
    (4, 'diya@example.com', 'US', 15.00, NULL);

SELECT customer_id, email, lifetime_value * 0.10 AS credit
FROM lesson_lab_customers
WHERE blocked_at IS NULL AND lifetime_value >= 50.00
ORDER BY customer_id;

SELECT email
FROM lesson_lab_customers
WHERE country = 'US'
ORDER BY email;

SELECT email
FROM lesson_lab_customers
WHERE country IS NULL OR country <> 'US'
ORDER BY email;

DROP TABLE lesson_lab_customers;

Verification: the first query should return Ada and Ben with credits 12.00 and 8.00. The second should return Ada and Diya. The third should return Ben and Cy, because it explicitly includes missing country values and countries other than the United States. The cleanup removes the temporary table immediately; otherwise PostgreSQL drops it automatically when the session ends.

Assessment Exercises

1. A table has completed_at timestamp with some nulls. Write a predicate that returns only incomplete rows. Then explain why completed_at = NULL is wrong.
2. Given price numeric and discount_percent numeric, write a select-list expression named discounted_price. Decide how your expression should behave when the discount is null.
3. A query uses WHERE lower(email) = lower($1). What index design might help, and what trade-off does it introduce?
4. Rewrite a query that uses SELECT * for an API response so it exposes only stable, necessary fields.
5. Explain why a test for a nullable predicate should include true, false, and null-producing rows.

Summary

SELECT defines the shape of a result, WHERE decides which rows survive, expressions compute values with PostgreSQL types and operators, and NULL introduces unknown rather than ordinary equality. The dependable habit is to state the intended rows, include deterministic ordering for examples and reports, project only needed columns, and test predicates with data that covers true, false, and unknown outcomes.