Querying with SELECT

SELECT is the whole of reading data in SQL. Everything else — joins, aggregates, windows — is a clause bolted onto this one statement.

All examples use the authors / books schema from the overview.

The shape of a SELECT

SELECT   title, price                 -- what to output
FROM     books                        -- where rows come from
WHERE    year > 1970                  -- which rows to keep
ORDER BY price DESC                   -- how to sort
LIMIT    10;                          -- how many

Remember the evaluation order: FROM → WHERE → SELECT → ORDER BY → LIMIT. SELECT runs late, which is why its aliases are usable in ORDER BY but not in WHERE.

Expressions and aliases

The select list is a list of expressions, not just column names. Arithmetic, function calls, string concatenation, and literals all work.

SELECT
  title,
  price,
  price * 0.9              AS sale_price,
  'Book: ' || title        AS label,
  ROUND(price * 1.2, 2)    AS with_tax
FROM books;

|| is standard string concatenation and works in both engines. AS is optional (price * 0.9 sale_price is legal) but always write it — it reads better and avoids parsing ambiguities.

Quote an alias if it needs spaces or case. Double quotes are for identifiers, single quotes are for string literals:

SELECT title AS "Book Title", 'in stock' AS status FROM books;

Danger

SQLite accepts double-quoted strings as string literals when the text doesn’t match a known column — a documented misfeature kept for compatibility. WHERE name = "Ted Chiang" silently works in SQLite and fails in PostgreSQL with column "Ted Chiang" does not exist. Always use single quotes for strings.

Escape a single quote by doubling it:

SELECT 'Le Guin''s novel';   -- => Le Guin's novel

SELECT * returns every column. Fine at the CLI; avoid it in application code, where adding a column later silently changes your result shape.

SELECT b.* FROM books AS b;      -- all columns from one table only

WHERE

WHERE filters rows one at a time. The expression must be a boolean; a row is kept only when it evaluates to true (not false, not NULL).

Comparison operators

SELECT * FROM books WHERE year = 1974;
SELECT * FROM books WHERE year <> 1974;      -- standard "not equal"
SELECT * FROM books WHERE year != 1974;      -- same, both engines accept it
SELECT * FROM books WHERE price >= 12 AND price < 16;

BETWEEN

SELECT * FROM books WHERE year BETWEEN 1970 AND 1980;

BETWEEN is inclusive on both ends — equivalent to year >= 1970 AND year <= 1980.

Caution

BETWEEN on timestamps is a classic bug. ts BETWEEN '2026-01-01' AND '2026-01-31' excludes everything after midnight on the 31st. Use a half-open range instead: ts >= '2026-01-01' AND ts < '2026-02-01'.

IN

SELECT * FROM books WHERE year IN (1968, 1974, 1979);
SELECT * FROM books WHERE author_id IN (SELECT id FROM authors WHERE birth_year < 1950);
SELECT * FROM books WHERE year NOT IN (1968, 1974);

Danger

NOT IN with a NULL in the list returns no rows at all. x NOT IN (1, NULL) expands to x <> 1 AND x <> NULL, and x <> NULL is NULL, so the whole thing is never true. Use NOT EXISTS when the subquery could produce nulls — see anti-joins.

LIKE and ILIKE

LIKE does simple pattern matching. % matches any run of characters (including none), _ matches exactly one.

SELECT * FROM books WHERE title LIKE 'The %';    -- starts with "The "
SELECT * FROM books WHERE title LIKE '%sea';     -- ends with "sea"
SELECT * FROM books WHERE title LIKE '_izard%';  -- 2nd char onward is "izard"

Case sensitivity is where the engines split:

	SQLite	PostgreSQL
LIKE	Case-insensitive for ASCII by default	Case-sensitive
ILIKE	Not supported	Case-insensitive

-- PostgreSQL
SELECT * FROM books WHERE title ILIKE '%earthsea%';

-- Portable equivalent, works in both
SELECT * FROM books WHERE LOWER(title) LIKE '%earthsea%';

Note

SQLite’s LIKE case-insensitivity applies only to ASCII A–Z; accented characters stay case-sensitive. PRAGMA case_sensitive_like = ON; switches it to case-sensitive if you need that.

Escape a literal % or _ with ESCAPE:

SELECT * FROM products WHERE code LIKE '100!%%' ESCAPE '!';   -- code starting "100%"

PostgreSQL also has POSIX regular expressions via ~ (match), ~* (case-insensitive match), !~, !~*:

SELECT * FROM books WHERE title ~* '^(the|a) ';

SQLite has a REGEXP operator in its grammar but no implementation built in — it errors unless the host application registers a regexp() function. Do not rely on it in portable SQL.

IS NULL

= NULL never matches anything. Use IS NULL / IS NOT NULL:

SELECT * FROM books WHERE year IS NULL;
SELECT * FROM books WHERE year IS NOT NULL;

NULL and three-valued logic

SQL logic has three values: true, false, and unknown (NULL). Any comparison involving NULL yields NULL.

SELECT NULL = NULL;     -- NULL, not true
SELECT NULL <> NULL;    -- NULL
SELECT 1 + NULL;        -- NULL
SELECT 'a' || NULL;     -- NULL in PostgreSQL; NULL in SQLite too

The truth tables:

AND	true	false	null
true	true	false	null
false	false	false	false
null	null	false	null

OR	true	false	null
true	true	true	true
false	true	false	null
null	true	null	null

Consequences worth internalising:

• WHERE keeps rows only where the result is true. Unknown is discarded, same as false.
• CHECK constraints reject rows only where the result is false. Unknown is accepted. (Opposite of WHERE.)
• NULL AND false is false — the one case where a null still gives a definite answer.

Null-safe equality

To compare treating two nulls as equal:

-- SQLite: the IS operator does exactly this
SELECT * FROM books WHERE year IS NULL;
SELECT * FROM t WHERE a IS b;          -- true when both are NULL

-- PostgreSQL (and SQLite 3.39+)
SELECT * FROM t WHERE a IS NOT DISTINCT FROM b;
SELECT * FROM t WHERE a IS DISTINCT FROM b;   -- null-safe "not equal"

COALESCE, NULLIF, and friends

COALESCE(a, b, c, ...) returns the first non-null argument. It is standard and works everywhere.

SELECT title, COALESCE(year, 0) AS year FROM books;
SELECT COALESCE(nickname, first_name, 'friend') AS greeting FROM users;

NULLIF(a, b) returns NULL when a = b, otherwise a. The classic use is avoiding division by zero:

SELECT total / NULLIF(count, 0) AS average FROM stats;   -- NULL instead of an error

Function	SQLite	PostgreSQL
COALESCE(...)	Yes	Yes
NULLIF(a, b)	Yes	Yes
IFNULL(a, b)	Yes	No — use COALESCE

NULLs in ORDER BY

-- PostgreSQL default: NULLS LAST for ASC, NULLS FIRST for DESC
SELECT title, year FROM books ORDER BY year;

-- SQLite default: NULLs sort first in ASC, last in DESC

-- Explicit, works in PostgreSQL and SQLite 3.30+
SELECT title, year FROM books ORDER BY year ASC NULLS LAST;

Caution

The defaults are opposite. If null ordering matters, always write NULLS FIRST or NULLS LAST.

ORDER BY

SELECT title, year, price FROM books
ORDER BY year DESC, title ASC;

Sort by multiple keys; ASC is the default. You can sort by an expression, a SELECT alias, or a 1-based output column position (legal but discouraged — it breaks when you edit the select list).

ORDER BY LENGTH(title);           -- expression
ORDER BY sale_price DESC;         -- alias from SELECT
ORDER BY 2 DESC;                  -- second output column

Text sorting uses a collation. SQLite’s default BINARY collation sorts by byte value, so uppercase sorts before lowercase; NOCASE gives ASCII-case-insensitive ordering. PostgreSQL uses the database’s locale collation, which usually sorts case-insensitively and ignores punctuation.

-- SQLite
SELECT title FROM books ORDER BY title COLLATE NOCASE;

-- PostgreSQL
SELECT title FROM books ORDER BY title COLLATE "C";        -- byte order
SELECT title FROM books ORDER BY LOWER(title);             -- portable

LIMIT and OFFSET

SELECT title FROM books ORDER BY year LIMIT 10;
SELECT title FROM books ORDER BY year LIMIT 10 OFFSET 20;   -- rows 21–30

Both engines support LIMIT n OFFSET m. PostgreSQL also supports the standard FETCH FIRST n ROWS ONLY; SQLite does not.

Caution

LIMIT without ORDER BY returns an arbitrary subset — and the arbitrary choice can change between runs as the plan changes. Always pair them.

Also: OFFSET is O(offset). The database still produces and discards those rows. For deep pagination use keyset pagination:

SELECT * FROM books WHERE (year, id) > (1979, 4) ORDER BY year, id LIMIT 10;

Row-value comparison like (a, b) > (x, y) works in both PostgreSQL and SQLite 3.15+.

DISTINCT

DISTINCT removes duplicate output rows, considering all selected columns together.

SELECT DISTINCT year FROM books;
SELECT DISTINCT author_id, year FROM books;   -- distinct pairs

For deduplication, DISTINCT treats NULLs as equal to each other — one of the few places it does.

PostgreSQL adds DISTINCT ON, which keeps the first row per group according to ORDER BY. It is the cleanest way to get “the latest row per key”:

-- PostgreSQL only
SELECT DISTINCT ON (author_id) author_id, title, year
FROM books
ORDER BY author_id, year DESC;

The ORDER BY must start with the DISTINCT ON expressions. The portable equivalent uses a window function — see aggregation and windows.

CASE

CASE is SQL’s conditional expression. It returns a value, so it can appear anywhere an expression can.

SELECT
  title,
  CASE
    WHEN price < 12 THEN 'cheap'
    WHEN price < 15 THEN 'normal'
    ELSE 'expensive'
  END AS bucket
FROM books;

Branches are tested in order; the first true one wins. Without ELSE, an unmatched row yields NULL.

The short “simple” form compares one expression against values:

SELECT CASE status
         WHEN 'p' THEN 'pending'
         WHEN 's' THEN 'shipped'
         ELSE 'unknown'
       END
FROM orders;

Note

The simple form uses =, so it can never match NULL. CASE x WHEN NULL THEN ... never fires — use the searched form with WHEN x IS NULL.

CASE inside an aggregate is how you pivot rows into columns:

SELECT
  author_id,
  COUNT(CASE WHEN year < 1980 THEN 1 END) AS pre_1980,
  COUNT(CASE WHEN year >= 1980 THEN 1 END) AS since_1980
FROM books
GROUP BY author_id;

Built-in functions

Function names are the least portable part of SQL. These are the ones you’ll reach for.

Strings

Task	SQLite	PostgreSQL
Length	LENGTH(s)	LENGTH(s) / CHAR_LENGTH(s)
Case	UPPER(s), LOWER(s)	UPPER(s), LOWER(s)
Trim	TRIM(s), LTRIM, RTRIM	TRIM(s), LTRIM, RTRIM, BTRIM
Substring	SUBSTR(s, start, len)	SUBSTR(s, start, len) or SUBSTRING(s FROM 2 FOR 3)
Replace	REPLACE(s, from, to)	REPLACE(s, from, to)
Concatenate	s1 || s2	s1 || s2, CONCAT(s1, s2)
Find position	INSTR(haystack, needle)	POSITION(needle IN haystack), STRPOS(haystack, needle)
Left/right slice	SUBSTR(s, 1, n)	LEFT(s, n), RIGHT(s, n)
Split	not available	SPLIT_PART(s, delim, n)
Pad	PRINTF/FORMAT tricks	LPAD(s, n, c), RPAD(s, n, c)
Regex replace	not available	REGEXP_REPLACE(s, pat, repl, 'g')
Format	FORMAT(fmt, ...) (3.38+, was PRINTF)	FORMAT(fmt, ...)

Both are 1-based for SUBSTR. CONCAT in PostgreSQL ignores nulls, while || propagates them — a useful difference:

-- PostgreSQL
SELECT 'a' || NULL;        -- NULL
SELECT CONCAT('a', NULL);  -- 'a'

Numbers

Task	SQLite	PostgreSQL
Absolute value	ABS(x)	ABS(x)
Round	ROUND(x, n)	ROUND(numeric, n)
Floor / ceiling	FLOOR, CEIL (3.35+)	FLOOR, CEIL/CEILING
Modulo	x % y	x % y, MOD(x, y)
Power	POWER(x, y) (3.35+)	POWER(x, y), x ^ y
Random	RANDOM() (large signed int)	RANDOM() (float in [0,1))
Cast	CAST(x AS INTEGER)	CAST(x AS integer) or x::integer

Caution

Two integer-division traps, both engines: SELECT 1/2 returns 0 because both operands are integers. Cast one side: SELECT 1.0 * a / b.

And in PostgreSQL, ROUND(x, n) is only defined for numeric. ROUND(1.0::double precision, 2) errors — write ROUND(x::numeric, 2). SQLite has no such restriction.

Dates and times

This is the widest gap between the engines, because SQLite has no date type.

SQLite stores dates as text/integer/real and provides five functions that interpret them: date(), time(), datetime(), julianday(), strftime() (plus unixepoch() in 3.38+). Each takes a time value followed by any number of modifiers.

-- SQLite
SELECT date('now');                          -- 2026-08-09
SELECT datetime('now', 'localtime');         -- local wall clock
SELECT date('now', '+7 days');               -- a week out
SELECT date('now', 'start of month');        -- 2026-08-01
SELECT strftime('%Y-%m', '2026-08-09');      -- 2026-08
SELECT julianday('2026-08-09') - julianday('2026-01-01');   -- 220.0 (days between)
SELECT unixepoch('now');                     -- seconds since 1970

PostgreSQL has real temporal types and interval arithmetic:

-- PostgreSQL
SELECT current_date;                         -- 2026-08-09
SELECT now();                                -- 2026-08-09 12:00:00+00
SELECT now() + interval '7 days';
SELECT date_trunc('month', now());           -- 2026-08-01 00:00:00+00
SELECT to_char(now(), 'YYYY-MM');            -- 2026-08
SELECT EXTRACT(year FROM now());             -- 2026
SELECT age(now(), '2026-01-01'::timestamptz);
SELECT now() AT TIME ZONE 'Europe/Paris';    -- convert to a wall clock
SELECT (date '2026-08-09' - date '2026-01-01');   -- 220 (integer days)

Tip

Store timestamps as ISO-8601 text ('2026-08-09T12:00:00Z') or Unix seconds in SQLite, and be consistent. ISO-8601 text sorts and compares correctly as a plain string, which is why it is the usual choice.

Caution

now() in PostgreSQL returns the transaction start time and is constant within a transaction. Use clock_timestamp() for the actual wall clock at the moment of the call. SQLite’s datetime('now') reads the clock each time.

Key points

• SELECT evaluates after WHERE, so SELECT aliases work in ORDER BY but not WHERE.
• Single quotes are strings, double quotes are identifiers — never rely on SQLite’s double-quoted-string fallback.
• NULL makes comparisons unknown; WHERE keeps only true rows, CHECK rejects only false ones.
• NOT IN with a null in the list matches nothing — reach for NOT EXISTS.
• LIKE is case-insensitive in SQLite and case-sensitive in PostgreSQL; ILIKE is PostgreSQL-only; LOWER(x) LIKE ... is portable.
• NULLS FIRST/NULLS LAST defaults are opposite between the two engines — be explicit.
• LIMIT needs ORDER BY; deep OFFSET is slow — use keyset pagination.
• Date handling is the biggest portability gap: SQLite has functions over text/integers, PostgreSQL has real types and intervals.