beginner~4h

SELECT Queries — WHERE, GROUP BY, HAVING, ORDER BY

The SELECT statement is the most used SQL statement. Understanding its execution order and clause interactions is critical for writing correct and efficient queries.

SELECT is the single most-used statement in SQL by a wide margin — nearly every other topic in this course (joins, subqueries, window functions) is really just an extension of what you can put inside a SELECT's clauses. Getting comfortable with WHERE/GROUP BY/HAVING/ORDER BY is the foundation everything else builds on.

Raw table data is rarely what a report or application screen actually needs — you need a filtered, grouped, sorted subset shaped for a specific question ("top 10 customers by spend this month"). Without a declarative way to express that shape, every consumer of the data would need to filter, group, and sort it themselves, in application code, inconsistently.

WHERE — filters individual rows before any grouping happens. GROUP BY — collapses matching rows into groups, one output row per group. HAVING — filters groups after they're formed (unlike WHERE, it can reference aggregate functions like COUNT/SUM). ORDER BY — sorts the final result set.

The SELECT statement is the most used SQL statement. Understanding its execution order and clause interactions is critical for writing correct and efficient queries.

Logical execution order (NOT the written order)

FROM / JOIN — identify tables and perform joins.
WHERE — filter rows before grouping.
GROUP BY — group remaining rows.
HAVING — filter groups (after GROUP BY).
SELECT — evaluate expressions and select columns.
DISTINCT — remove duplicates.
ORDER BY — sort the result.
LIMIT / OFFSET — paginate the result.

This order matters: WHERE runs before GROUP BY (can't use aggregate functions in WHERE). HAVING runs after GROUP BY (can use aggregate functions). ORDER BY can reference column aliases defined in SELECT (executed after SELECT).

Key aggregate functions

COUNT(*), COUNT(col), SUM(), AVG(), MIN(), MAX(), STRING_AGG() (PostgreSQL), GROUP_CONCAT() (MySQL).

PostgreSQL SELECT execution

FROM clause processed — identify base tables and CTEs.
JOIN processing — nested loop, hash join, or merge join chosen by optimizer.
WHERE clause — applied as a filter predicate; index used if available.
GROUP BY — rows sorted or hashed into groups.
Aggregate functions computed per group.
HAVING — filter groups by aggregate conditions.
SELECT list computed — expressions, functions, subqueries.
DISTINCT — hash-based deduplication.
ORDER BY — sort; uses index if available (index scan in order).
LIMIT/OFFSET — slice the result set.

PostgreSQL uses statistics (pg_statistic) to estimate row counts and choose optimal execution paths.

Step 1: Start with FROM — identify your data source.

Step 2: Apply WHERE — filter before any aggregation.

Step 3: GROUP BY — specify grouping columns.

Step 4: HAVING — filter groups using aggregate conditions.

Step 5: SELECT — choose columns and expressions.

Step 6: ORDER BY — sort the output.

Step 7: LIMIT/OFFSET — paginate large result sets.

Write columns explicitly — never use SELECT * in production queries; adds parsing overhead and breaks with column changes.
Always filter early with WHERE — reduces rows before aggregation and JOIN operations.
Use EXPLAIN ANALYZE before shipping — verify query plan for production queries.
Prefer WHERE over HAVING when possible — WHERE filters rows; HAVING filters groups.
Index columns used in WHERE, JOIN ON, and ORDER BY — dramatically improves performance.
Use LIMIT for all user-facing queries — prevents runaway queries returning millions of rows.
Avoid functions on indexed columns in WHERE: WHERE UPPER(email) = 'X' prevents index use. Use: WHERE email = 'x' or create a functional index.
BETWEEN is inclusive on both ends — verify business logic aligns.
NULL comparisons use IS NULL / IS NOT NULL, not = NULL.
Use COALESCE() to handle NULLs in output: COALESCE(dept_id, -1).

Using WHERE with aggregate functions: WHERE COUNT(*) > 5 — use HAVING instead.
SELECT * in production — bandwidth waste; breaks if columns are added/removed.
Non-deterministic ORDER BY — if multiple rows have same ORDER BY value, result order is unpredictable. Add a unique column as tiebreaker.
OFFSET for deep pagination — OFFSET 1000000 scans and discards 1M rows. Use keyset pagination instead.
Implicit GROUP BY — in MySQL, non-aggregated columns in SELECT without GROUP BY are allowed (ONLY_FULL_GROUP_BY mode). Always be explicit.
HAVING without GROUP BY — syntactically valid (treats all rows as one group) but rarely intended.
Comparing NULLs with = — NULL = NULL is NOT TRUE in SQL; use IS NULL.
Integer division: SELECT 5/2 returns 2 in SQL. Use 5.0/2 or CAST(5 AS DECIMAL)/2.

Index on WHERE columns: CREATE INDEX idx_emp_dept ON employees(dept_id, salary) for WHERE dept_id = 1 AND salary > 80000.
Covering index: index includes all columns needed by query — no heap lookup.
Partial index: CREATE INDEX idx_pending ON orders(order_date) WHERE status = 'PENDING' — smaller index.
Avoid DISTINCT unless necessary — it requires an extra sort/hash step.
Use COUNT(1) vs COUNT() — identical performance in PostgreSQL; COUNT() counts all rows; COUNT(col) skips NULLs.
Keyset pagination: WHERE id > last_seen_id ORDER BY id LIMIT 10 — O(log n) vs OFFSET O(n).
pg_stat_statements extension: identifies slow queries by total_exec_time.

Never build a WHERE clause by concatenating raw user input into a SQL string — this is the textbook SQL injection vector. Always use parameterized queries/prepared statements, where the database treats user input strictly as a value, never as executable SQL syntax.

A slow SELECT is usually visible as elevated query latency in your APM/slow-query log before it becomes a user-facing complaint — track p95/p99 latency per distinct query shape, not just an overall average, since one bad query pattern can hide inside a healthy-looking average.

Set statement_timeout in PostgreSQL: SET LOCAL statement_timeout = '30s' — kills runaway queries.
Use connection pooling (PgBouncer) — prevents connection exhaustion.
Monitor slow query log: log_min_duration_statement = 1000 (1 second threshold).
Analyze query plans weekly: pg_stat_statements + auto_explain for production monitoring.
Use read replicas for heavy SELECT workloads — offload reporting queries.
Set work_mem per session for heavy sorts: SET LOCAL work_mem = '256MB'.

Write a query that shows each department's average salary, employee count, and percentage of total company salary. Use GROUP BY, HAVING, and subquery for total salary.
Find employees hired in the last 2 years whose salary is above their department's average. (Hint: correlated subquery or window function).
Implement keyset pagination: write a function that returns page N of employees ordered by salary DESC using keyset pagination. Compare EXPLAIN ANALYZE output with OFFSET-based pagination.
Find the top-3 highest-paid employees per department. Use HAVING, subquery, or window functions.

Logical execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.
WHERE filters rows (before GROUP BY); HAVING filters groups (after GROUP BY).
COUNT(*) counts all rows; COUNT(col) skips NULLs; COUNT(DISTINCT col) counts unique values.
NULL comparisons: use IS NULL / IS NOT NULL, never = NULL.
Keyset pagination over OFFSET for large tables — O(log n) vs O(n).
Index WHERE, JOIN ON, and ORDER BY columns for performance.
EXPLAIN ANALYZE is your best friend for query optimization.

Want a visual for this concept?

Generate a diagram tailored to “SELECT Queries — WHERE, GROUP BY, HAVING, ORDER BY” — the AI picks whichever visual (flowchart, comparison, sequence, etc.) best fits.

Practice quiz

Next Step

Continue to CASE Expressions, Conditional Logic & NULL-Safe Functions →← Back to all SQL chapters