Lesson 07 beginner

Querying Data with SELECT

Read data with SELECT: choose columns and expressions, filter with WHERE, combine conditions correctly, understand NULL and three-valued logic, sort and paginate, match patterns with LIKE and ILIKE, build values with CASE and functions, remove duplicates, and learn the order in which a query is evaluated.

SELECT is the statement you will write most, and the one with the most options. This lesson works through it piece by piece using the Orbit data from lesson 6: three users and five notes.

The shape of a SELECT

SELECT   columns and expressions
FROM     tables
WHERE    row filter
GROUP BY grouping          -- lesson 9
HAVING   group filter      -- lesson 9
ORDER BY sort order
LIMIT    how many rows;

Only SELECT is required: SELECT 1 + 1; is a valid query. Everything else is optional, but the clauses must appear in this order.

Choosing columns

SELECT id, title, pinned FROM notes ORDER BY id;
 id |      title       | pinned
----+------------------+--------
  1 | Shopping list    | t
  2 | Deploy checklist | f
  3 | Book ideas       | f
  4 | Postgres tips    | t
  5 | Meeting notes    | f
(5 rows)

SELECT * returns every column. It is handy in psql, but in application code list the columns you need: it sends less data and does not break when someone adds a column.

Warning: Without ORDER BY, rows come back in whatever order is cheapest for the server. That order can change after an update, a vacuum or a new index. If order matters, always say ORDER BY.

You can select expressions and give them names with AS:

SELECT title,
       length(body) AS chars,
       upper(left(title, 3)) AS code
FROM notes
WHERE user_id = 2
ORDER BY id;
     title     | chars | code
---------------+-------+------
 Book ideas    |    26 | BOO
 Postgres tips |    34 | POS
(2 rows)

Filtering with WHERE

WHERE keeps the rows for which the condition is true:

SELECT id, title FROM notes WHERE pinned ORDER BY id;
 id |     title
----+---------------
  1 | Shopping list
  4 | Postgres tips
(2 rows)

The usual comparison operators work on every type: =, <> (or !=), <, <=, >, >=. A few more save typing:

SELECT id FROM notes WHERE user_id IN (1, 3);
SELECT id FROM notes WHERE created_at BETWEEN '2026-10-01' AND '2026-10-31';
SELECT id FROM notes WHERE created_at >= '2026-10-01' AND created_at < '2026-11-01';

The last two look the same, but BETWEEN includes both ends, and '2026-10-31' means midnight at the start of that day, so a note written on the afternoon of 31 October would be missed. For time ranges, prefer the half-open form >= start AND < end.

AND binds tighter than OR

SELECT count(*) FROM notes WHERE user_id = 1 OR user_id = 2 AND pinned;
 count
-------
     4
(1 row)
SELECT count(*) FROM notes WHERE (user_id = 1 OR user_id = 2) AND pinned;
 count
-------
     2
(1 row)

The first query means “all of user 1’s notes, plus user 2’s pinned notes”, because AND is evaluated before OR. Use parentheses whenever you mix them.

NULL: the missing value

NULL means “unknown”. Any comparison with an unknown value is itself unknown, not true or false:

SELECT NULL = NULL AS equals, NULL IS NULL AS is_null, NULL <> 1 AS not_one;
 equals | is_null | not_one
--------+---------+---------
 (null) | t       | (null)
(1 row)

WHERE keeps only rows where the condition is true, so WHERE email = NULL never matches anything. Use these instead:

To testWrite
Value is missingcol IS NULL
Value is presentcol IS NOT NULL
Equal, treating two NULLs as equala IS NOT DISTINCT FROM b
Replace NULL with a fallbackcoalesce(col, 'fallback')
Turn a value into NULLnullif(col, '')

Sorting and limiting

SELECT id, title, created_at::date AS day
FROM notes
ORDER BY created_at DESC
LIMIT 3;
 id |     title     |    day
----+---------------+------------
  5 | Meeting notes | 2026-11-02
  4 | Postgres tips | 2026-10-12
  3 | Book ideas    | 2026-10-05
(3 rows)
  • DESC sorts descending; ASC is the default.
  • Sort by several columns: ORDER BY pinned DESC, created_at DESC puts pinned notes first, newest first within each group.
  • NULLs sort last in ascending order and first in descending order; change that with NULLS FIRST or NULLS LAST.
  • ::date casts the timestamp to a date for display.

Pagination

LIMIT 20 OFFSET 40 gives page 3 of 20 rows, but the server still reads and throws away the first 40 rows, which gets slow on deep pages. Keyset pagination remembers the last row of the previous page instead:

SELECT id, title FROM notes WHERE id > 2 ORDER BY id LIMIT 2;
 id |     title
----+---------------
  3 | Book ideas
  4 | Postgres tips
(2 rows)

With an index on the sort column, every page is as fast as the first.

Pattern matching

LIKE matches text with two wildcards: % for any run of characters and _ for exactly one. ILIKE ignores case:

SELECT id, title FROM notes WHERE title ILIKE '%list%' ORDER BY id;
 id |      title
----+------------------
  1 | Shopping list
  2 | Deploy checklist
(2 rows)

For regular expressions use ~ (or ~* to ignore case): WHERE email ~ '@orbit\.dev$'. A pattern that starts with % cannot use a normal index; lesson 11 shows the trigram index that can.

Building values with CASE

CASE is SQL’s if/else, and it works anywhere an expression does:

SELECT id,
       CASE WHEN pinned THEN 'pinned' ELSE 'normal' END AS kind
FROM notes
ORDER BY id;
 id |  kind
----+--------
  1 | pinned
  2 | normal
  3 | normal
  4 | pinned
  5 | normal
(5 rows)

Working with dates

SELECT id, to_char(created_at, 'Dy DD Mon') AS written
FROM notes
WHERE created_at >= '2026-10-01' AND created_at < '2026-10-08'
ORDER BY id;
 id |  written
----+------------
  1 | Thu 01 Oct
  2 | Sat 03 Oct
  3 | Mon 05 Oct
(3 rows)

Other helpers you will reach for: now() and current_date, date_trunc('month', created_at), extract(year FROM created_at), and interval arithmetic such as created_at > now() - interval '7 days'.

Removing duplicates

SELECT DISTINCT user_id FROM notes ORDER BY user_id;
 user_id
---------
       1
       2
(2 rows)

DISTINCT applies to the whole selected row. Postgres also has DISTINCT ON (col), which keeps the first row of each group according to ORDER BY, a handy way to get “the latest note per user”.

The order of evaluation

SQL is written in one order and evaluated in another:

FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

That explains two common surprises. You cannot use a column alias from SELECT in WHERE, because WHERE runs first. You can use it in ORDER BY, which runs after SELECT.

Quick reference

TaskSQL
Columns and expressionsSELECT a, b * 2 AS doubled FROM t
FilterWHERE a = 1 AND (b > 2 OR c IS NULL)
List and rangeIN (...), >= start AND < end
Missing valuesIS NULL, coalesce(col, 'x')
SortORDER BY a DESC, b NULLS LAST
Top NORDER BY ... LIMIT n
Next pageWHERE id > :last ORDER BY id LIMIT n
Text patternLIKE '%x%', ILIKE, ~
If / elseCASE WHEN ... THEN ... ELSE ... END
Unique rowsSELECT DISTINCT ...

Exercise

Which query returns users whose email is missing?

  1. SELECT * FROM users WHERE email = NULL;
  2. SELECT * FROM users WHERE email = ”;
  3. SELECT * FROM users WHERE NOT email;
  4. SELECT * FROM users WHERE email IS NULL;
Show answer

Answer: D. Comparing anything with NULL using = yields NULL, which WHERE treats as not true. IS NULL is the only correct test.

Exercise

Why does SELECT title AS t FROM notes WHERE t LIKE ‘P%’ fail?

  1. WHERE is evaluated before SELECT, so the alias t does not exist yet
  2. LIKE does not work on aliases of text columns
  3. Aliases must be written in double quotes
  4. The pattern needs a % on both sides
Show answer

Answer: A. Logically, FROM and WHERE run before the SELECT list is computed. Repeat the expression in WHERE, or wrap the query in a subquery.

Exercise

You need every note created in October 2026, including those written late on 31 October. Which filter is correct?

  1. created_at BETWEEN ‘2026-10-01’ AND ‘2026-10-31’
  2. created_at > ‘2026-10-01’ AND created_at < ‘2026-10-31’
  3. created_at >= ‘2026-10-01’ AND created_at < ‘2026-11-01’
  4. extract(month FROM created_at) = 10
Show answer

Answer: C. ‘2026-10-31’ means midnight at the start of that day, so BETWEEN misses the rest of it. The half-open range covers the whole month, and unlike extract it can use an index on created_at and does not match October of other years.

Key takeaways

  • Always add ORDER BY when the order of rows matters.
  • Use parentheses when mixing AND and OR.
  • NULL is unknown: test it with IS NULL, replace it with coalesce.
  • Filter time ranges with >= start AND < end; paginate with keyset rather than large offsets.
  • Clauses are evaluated in the order FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.

FAQ

Is SELECT * really that bad?

It is fine for exploring in psql. In application code and views, explicit columns send less data, allow index-only scans and do not change shape when the table changes.

Are SQL keywords case-sensitive?

No. select, SELECT and Select are the same. Writing keywords in upper case is a convention that makes queries easier to scan. Identifiers are folded to lower case unless quoted, and string literals are always case-sensitive.

Should I use single or double quotes?

Single quotes for text values ('Ana'), double quotes only for identifiers that need them ("Order Lines"). Writing WHERE name = "Ana" makes Postgres look for a column called Ana.

How do I search for a literal % or _ with LIKE?

Escape it with a backslash: WHERE title LIKE '100\%'. You can choose another escape character with LIKE '100!%' ESCAPE '!'.

Why is my case-insensitive search slow?

lower(email) = '...' or ILIKE cannot use a normal index on email. Create an expression index on lower(email), or a trigram index for ILIKE '%...%' searches. Lesson 11 shows both.

↑ ↓ navigate Enter open Esc close