SELECT Fundamentals

Overview

SELECT: Reads rows; everything else in querying (joins, aggregates, windows) builds on it
Key Ideas: Filtering (WHERE), sorting (ORDER BY), paging (LIMIT/OFFSET vs keyset), de-duplication (DISTINCT / DISTINCT ON), NULL logic, CASE
Must Know for Interviews: The logical processing order — it explains most “why can’t I use this alias here?” errors
Sample Data: E-commerce Practice Queries

Logical Query Processing Order

Written Order vs Evaluated Order

Written:    SELECT → FROM → WHERE → GROUP BY → HAVING → WINDOW → ORDER BY → LIMIT
 
Evaluated:  1. FROM / JOIN      build the row set
            2. WHERE            filter rows        (no aggregates, no SELECT aliases)
            3. GROUP BY         form groups
            4. HAVING           filter groups      (aggregates allowed)
            5. SELECT           compute expressions, then window functions
            6. DISTINCT         remove duplicates
            7. ORDER BY         sort               (SELECT aliases allowed)
            8. LIMIT / OFFSET   cut

Consequences

Aliases in WHERE: Not allowed — WHERE runs before SELECT
Aliases in ORDER BY: Allowed — ORDER BY runs after SELECT
Postgres Extension: Postgres also accepts output aliases / ordinal positions in GROUP BY (GROUP BY 1) — not standard SQL
Window Functions in WHERE: Not allowed — wrap in a subquery/CTE (see Window Functions)
Physical Plan: The planner may execute in a different order (e.g. push filters into joins); the logical order only defines the result

-- Alias works in ORDER BY…
SELECT name, price * 1.18 AS price_with_tax
FROM products
ORDER BY price_with_tax DESC
LIMIT 3;
 
-- …but not in WHERE: repeat the expression or use a subquery
-- SELECT name, price * 1.18 AS price_with_tax FROM products WHERE price_with_tax > 100;
--   ERROR: column "price_with_tax" does not exist
SELECT *
FROM (SELECT name, price * 1.18 AS price_with_tax FROM products) t
WHERE price_with_tax > 1000;

WHERE: Filtering

Operators

Operator	Example	Notes
Comparison	`price >= 100`	`<>` and `!=` are the same
BETWEEN	`price BETWEEN 50 AND 100`	Inclusive both ends — careful with timestamps
IN	`status IN ('paid','shipped')`	Readable OR-list
LIKE / ILIKE	`name ILIKE '%phone%'`	`ILIKE` = case-insensitive (Postgres)
Regex	`sku ~ '^ACC-\d+$'`	`~` match, `~*` case-insensitive, `!~` not match
IS [NOT] NULL	`country IS NULL`	Never `= NULL`
IS [NOT] DISTINCT FROM	`a IS DISTINCT FROM b`	NULL-safe equality
ANY / ALL	`id = ANY (ARRAY[1,2,3])`	Great for passing arrays from app code

SELECT sku, name, price
FROM products
WHERE is_active
  AND (name ILIKE '%laptop%' OR sku ~ '^PHN-')
  AND price BETWEEN 500 AND 2500
ORDER BY price;

Date Ranges: Use Half-Open Intervals

-- Bad:  created_at BETWEEN '2025-03-01' AND '2025-03-31'   → misses 31 Mar after 00:00
-- Good: >= start AND < next start
SELECT id, created_at
FROM orders
WHERE created_at >= '2025-03-01' AND created_at < '2025-04-01'
ORDER BY created_at;

ORDER BY

Direction: ASC (default) / DESC
NULL Placement: Postgres puts NULLs last for ASC and first for DESC (treats NULL as largest). Override with NULLS FIRST / NULLS LAST
Determinism: Without a unique tie-breaker, row order among equal keys is undefined — always add id when paginating
No ORDER BY = No Order: A table has no inherent order; results can change between runs

SELECT full_name, country
FROM users
ORDER BY country DESC NULLS LAST, full_name;

LIMIT / OFFSET vs Keyset Pagination

OFFSET Pagination

-- Page 3, 5 per page
SELECT id, created_at, status
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 5 OFFSET 10;
 
-- Standard-SQL spelling (same thing), plus WITH TIES support
SELECT id, created_at FROM orders ORDER BY created_at DESC OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY;

Keyset (Seek / Cursor) Pagination

-- Client remembers the last row of the previous page: (created_at, id) = ('2025-05-01 12:00+00', 9)
SELECT id, created_at, status
FROM orders
WHERE (created_at, id) < ('2025-05-01 12:00+00', 9)   -- row-value comparison
ORDER BY created_at DESC, id DESC
LIMIT 5;
-- Backed by an index on (created_at, id) this is a single index range scan, whatever the page number

Aspect	OFFSET	Keyset
Deep Pages	Slow — must read and throw away `OFFSET` rows	Constant time with an index
Concurrent Inserts	Rows shift → duplicates / skipped rows	Stable
Jump to Page N	Yes	No (next/prev only)
Requirements	Any `ORDER BY`	Unique, indexed sort key (add `id` as tie-breaker)
Best For	Admin UIs, small tables	Infinite scroll, APIs, exports

DISTINCT and DISTINCT ON

DISTINCT

SELECT DISTINCT country FROM users ORDER BY country;   -- NULL appears once
SELECT count(DISTINCT user_id) AS customers_with_orders FROM orders;

DISTINCT ON (Postgres-only)

What: Keep the first row per group, where “first” is defined by ORDER BY
Rule: ORDER BY must start with the DISTINCT ON expressions
Classic Use: “Latest X per Y” without a window function

-- Most expensive product in each category
SELECT DISTINCT ON (category_id) category_id, name, price
FROM products
ORDER BY category_id, price DESC, id;

NULL Handling

Three-Valued Logic

Values: TRUE, FALSE, UNKNOWN (NULL)
Comparisons with NULL: NULL = NULL, NULL <> 1, NULL > 0 are all NULL, not true/false
WHERE Keeps Only TRUE: Rows where the condition is NULL are dropped — this is how rows “disappear”

a	b	a AND b	a OR b	NOT a
TRUE	NULL	NULL	TRUE	FALSE
FALSE	NULL	FALSE	NULL	TRUE
NULL	NULL	NULL	NULL	NULL

SELECT NULL = NULL                 AS eq,            -- NULL
       NULL IS NULL                AS is_null,       -- true
       NULL IS DISTINCT FROM NULL  AS distinct_from, -- false
       1    IS DISTINCT FROM NULL  AS one_vs_null,   -- true
       TRUE OR NULL                AS t_or_n,        -- true
       FALSE AND NULL              AS f_and_n;       -- false
 
-- Classic trap: "everyone not from IN" silently drops Hiro (country IS NULL)
SELECT count(*) FROM users WHERE country <> 'IN';                              -- 4
SELECT count(*) FROM users WHERE country IS DISTINCT FROM 'IN';                -- 5

NULL Functions

Function	Returns	Example
COALESCE(a, b, …)	First non-NULL argument	`COALESCE(country, 'unknown')`
NULLIF(a, b)	NULL if `a = b`, else `a`	`total / NULLIF(count, 0)` avoids divide-by-zero
IS [NOT] NULL	Boolean	`WHERE referred_by IS NULL`
count(col) vs *count()**	Non-NULL values vs all rows	`count(country)` = 7, `count(*)` = 8

SELECT full_name,
       COALESCE(country, 'unknown') AS country,
       NULLIF(country, 'IN')        AS non_indian_country
FROM users
ORDER BY id;
 
SELECT count(*) AS all_rows, count(country) AS with_country, count(DISTINCT country) AS countries
FROM users;

NULLs in Aggregates and Sorting

Aggregates Ignore NULL: sum, avg, min, max, count(col) skip NULLs; sum of zero rows is NULL, not 0
Grouping: All NULLs fall into one group
Arithmetic: 5 + NULL is NULL; 'a' || NULL is NULL (use concat() which skips NULLs)

SELECT 'a' || NULL               AS concat_op,   -- NULL
       concat('a', NULL, 'b')    AS concat_fn,   -- 'ab'
       (SELECT sum(price) FROM products WHERE false) AS sum_of_nothing;  -- NULL

CASE Expressions

-- Searched CASE
SELECT name, price,
       CASE
           WHEN price >= 1000 THEN 'premium'
           WHEN price >= 100  THEN 'mid'
           ELSE 'budget'
       END AS price_band
FROM products
ORDER BY price DESC;
 
-- Simple CASE
SELECT id, status,
       CASE status WHEN 'cancelled' THEN 0 WHEN 'pending' THEN 1 ELSE 2 END AS status_rank
FROM orders
ORDER BY status_rank, id
LIMIT 5;
 
-- CASE in ORDER BY: custom sort order
SELECT id, status FROM orders
ORDER BY CASE status WHEN 'pending' THEN 1 WHEN 'paid' THEN 2 WHEN 'shipped' THEN 3 ELSE 4 END, id;

Missing ELSE: Returns NULL
Evaluation: First matching WHEN wins; branches are evaluated lazily

Handy Postgres Extras

SELECT upper(name), length(name), left(sku, 3) AS prefix,
       split_part(sku, '-', 2) AS sku_number,
       round(price * 0.9, 2) AS discounted,
       price::int AS price_int,            -- :: is Postgres cast syntax (CAST(x AS int) is standard)
       format('%s costs %s', name, price) AS label
FROM products
WHERE id <= 3;
 
SELECT * FROM generate_series(date '2025-01-01', date '2025-03-01', interval '1 month') AS month;

Interview Questions

“Why does WHERE x <> 5 miss rows?”: Rows with x IS NULL evaluate to unknown and are filtered out
“Why can’t I use a column alias in WHERE?”: WHERE is evaluated before SELECT
“OFFSET pagination is slow on page 10,000 — fix it?”: Keyset pagination with a composite index on the sort key + id
“Latest row per group?”: DISTINCT ON in Postgres; ROW_NUMBER() portable
“COALESCE vs NULLIF?”: COALESCE replaces NULL with a value; NULLIF turns a value into NULL

Summary

Processing Order: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT
Filtering: Use half-open date ranges, ILIKE/regex for text, = ANY(array) for lists
Pagination: OFFSET is simple but degrades; keyset is stable and constant-time
DISTINCT ON: Postgres shortcut for “first row per group”
NULLs: Three-valued logic — use IS NULL, IS DISTINCT FROM, COALESCE, NULLIF, and remember aggregates skip NULLs

Kaushik's Notes

Explorer

SELECT Fundamentals

Overview

Logical Query Processing Order

Written Order vs Evaluated Order

Consequences

WHERE: Filtering

Operators

Date Ranges: Use Half-Open Intervals

ORDER BY

DISTINCT and DISTINCT ON

DISTINCT

DISTINCT ON (Postgres-only)

NULL Handling

Three-Valued Logic

NULL Functions

NULLs in Aggregates and Sorting

CASE Expressions

Handy Postgres Extras

Interview Questions

Summary

Graph View

Table of Contents

Backlinks