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 test | Write |
|---|---|
| Value is missing | col IS NULL |
| Value is present | col IS NOT NULL |
| Equal, treating two NULLs as equal | a IS NOT DISTINCT FROM b |
| Replace NULL with a fallback | coalesce(col, 'fallback') |
| Turn a value into NULL | nullif(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)
DESCsorts descending;ASCis the default.- Sort by several columns:
ORDER BY pinned DESC, created_at DESCputs pinned notes first, newest first within each group. - NULLs sort last in ascending order and first in descending order; change that with
NULLS FIRSTorNULLS LAST. ::datecasts 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
| Task | SQL |
|---|---|
| Columns and expressions | SELECT a, b * 2 AS doubled FROM t |
| Filter | WHERE a = 1 AND (b > 2 OR c IS NULL) |
| List and range | IN (...), >= start AND < end |
| Missing values | IS NULL, coalesce(col, 'x') |
| Sort | ORDER BY a DESC, b NULLS LAST |
| Top N | ORDER BY ... LIMIT n |
| Next page | WHERE id > :last ORDER BY id LIMIT n |
| Text pattern | LIKE '%x%', ILIKE, ~ |
| If / else | CASE WHEN ... THEN ... ELSE ... END |
| Unique rows | SELECT DISTINCT ... |
Exercise
Which query returns users whose email is missing?
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?
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?
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 BYwhen the order of rows matters. - Use parentheses when mixing
ANDandOR. NULLis unknown: test it withIS NULL, replace it withcoalesce.- 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.