Truy vấn dữ liệu: SQL cơ bảnQuerying Data: SQL Fundamentals

Part of the PostgreSQL DBA Roadmap knowledge base.

Overview

Before a database administrator can tune a query, design an index, or reason about a slow execution plan, they have to be able to write the query in the first place. This topic is the hands-on counterpart to the relational theory covered in ../backend/en/08-relational-databases.md — it assumes you already know what a relation, a primary key, and normalization are, and instead focuses on the practical craft of writing SELECT statements against a real PostgreSQL database: filtering rows, joining tables, aggregating data, and ordering results.

roadmap.sh treats “Learn SQL” as a prerequisite step that sits before the PostgreSQL DBA path really begins, and that framing matters: SQL fluency is the baseline the entire rest of the roadmap builds on. You cannot understand EXPLAIN output, design a useful index, or write a safe migration if you cannot confidently express “give me these rows, filtered this way, joined to that table, grouped like this.” This note is that baseline, made concrete with PostgreSQL-flavored syntax and real tables. Where a topic goes deeper — window functions, CTEs, subqueries — it is deferred to ./05-advanced-querying.md; where a topic is about why a query is slow rather than how to write it, it is deferred to ./10-query-planning-and-performance-tuning.md.

Throughout this note we use two small example tables to keep everything concrete:

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    full_name   TEXT NOT NULL,
    email       TEXT NOT NULL UNIQUE,
    country     TEXT NOT NULL,
    signed_up_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    total_cents INTEGER NOT NULL,
    status      TEXT NOT NULL,       -- 'pending' | 'paid' | 'cancelled' | 'refunded'
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO customers (full_name, email, country) VALUES
    ('Alice Nguyen',  'alice@example.com',  'VN'),
    ('Bao Tran',      'bao@example.com',    'VN'),
    ('Carlos Ruiz',   'carlos@example.com', 'MX'),
    ('Dana White',    'dana@example.com',   'US'),
    ('Emeka Obi',     'emeka@example.com',  'NG');

INSERT INTO orders (customer_id, total_cents, status, created_at) VALUES
    (1, 2500, 'paid',      now() - interval '10 days'),
    (1, 1200, 'paid',      now() - interval '5 days'),
    (2, 4300, 'pending',   now() - interval '2 days'),
    (3, 900,  'cancelled', now() - interval '20 days'),
    (3, 3000, 'paid',      now() - interval '1 days'),
    (4, 1500, 'refunded',  now() - interval '15 days');
    -- note: Emeka (customer_id 5) has placed no orders yet

Fundamentals

SELECT basics

The simplest query names the columns you want from a table:

SELECT full_name, email FROM customers;

Use * sparingly (fine for ad-hoc exploration, risky in application code — it silently returns new columns and breaks positional assumptions):

SELECT * FROM customers;

AS aliases a column or expression to a friendlier name. The AS keyword itself is optional but improves readability:

SELECT
    full_name AS customer_name,
    total_cents / 100.0 AS total_dollars
FROM customers c
JOIN orders o USING (customer_id);

DISTINCT removes duplicate rows from the result set — useful for answering “which countries do we have customers in?” rather than “list every customer’s country”:

SELECT DISTINCT country FROM customers;

DISTINCT ON (a PostgreSQL extension, not standard SQL) keeps the first row per group according to ORDER BY — commonly used to fetch “the latest order per customer”:

SELECT DISTINCT ON (customer_id) customer_id, order_id, created_at
FROM orders
ORDER BY customer_id, created_at DESC;

LIMIT and OFFSET restrict how many rows come back and where the window starts — the bread and butter of pagination:

SELECT order_id, total_cents, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;   -- "page 3" of 20-row pages

Why OFFSET pagination degrades at scale. OFFSET 40 does not jump straight to row 41 — PostgreSQL must still scan and discard the first 40 matching rows before it can return the next 20. As the offset grows (page 1000, OFFSET 20000), the database does more and more throwaway work per request, and the cost grows roughly linearly with the offset. It is also unstable under concurrent writes: rows can shift between pages if data is inserted or deleted between requests. The standard fix is keyset pagination (a.k.a. “seek method”): instead of skipping N rows, you remember the last row’s sort key from the previous page and filter for rows strictly beyond it, e.g. WHERE created_at < $last_seen_created_at ORDER BY created_at DESC LIMIT 20. This lets the index seek directly to the right starting point regardless of how deep the “page” is. Keyset pagination and its trade-offs (no arbitrary “jump to page N”, requires a unique, indexed, monotonic sort key) are covered in more depth in ./10-query-planning-and-performance-tuning.md.

Filtering data with WHERE

WHERE restricts which rows survive before any grouping or selection happens. PostgreSQL supports the standard comparison operators plus a handful of convenience operators.

-- Basic comparisons
SELECT * FROM orders WHERE total_cents > 2000;
SELECT * FROM orders WHERE status != 'cancelled';
SELECT * FROM orders WHERE created_at < now() - interval '7 days';

-- BETWEEN — inclusive on both ends
SELECT * FROM orders WHERE total_cents BETWEEN 1000 AND 3000;

-- IN — membership test, cleaner than chained OR
SELECT * FROM orders WHERE status IN ('paid', 'refunded');

-- IS NULL / IS NOT NULL — never use = NULL, it always evaluates to NULL (unknown)
SELECT * FROM customers WHERE email IS NOT NULL;

-- LIKE / ILIKE — pattern matching; ILIKE is PostgreSQL's case-insensitive variant
SELECT * FROM customers WHERE full_name LIKE 'A%';       -- starts with 'A', case-sensitive
SELECT * FROM customers WHERE full_name ILIKE 'a%';      -- starts with 'a' or 'A'
SELECT * FROM customers WHERE email ILIKE '%@example.com';

LIKE wildcards are deliberately minimal: % matches any sequence of characters (including none), _ matches exactly one character. For anything more expressive — alternation, anchors, character classes, repetition — PostgreSQL’s POSIX regular expression operators take over:

-- ~   : matches regex, case-sensitive
-- ~*  : matches regex, case-insensitive
-- !~  : does not match regex, case-sensitive
-- !~* : does not match regex, case-insensitive

SELECT * FROM customers WHERE email ~ '^[a-z]+@example\.com$';
SELECT * FROM customers WHERE full_name ~* '^(alice|bao)';

Reach for LIKE/ILIKE for simple prefix/suffix/substring checks; reach for ~/~* when you need real regex power. Neither uses a plain B-tree index efficiently for %pattern% searches — that is a topic for indexing and full-text search, out of scope here.

Combining conditions. AND, OR, and NOT combine predicates, and PostgreSQL follows standard SQL precedence: NOT binds tightest, then AND, then OR. This trips people up constantly — always parenthesize when mixing AND and OR:

-- Almost certainly not what was intended: NOT then AND then OR
-- reads as: (status = 'paid' AND country = 'VN') OR country = 'US'
SELECT * FROM customers c
JOIN orders o USING (customer_id)
WHERE status = 'paid' AND country = 'VN' OR country = 'US';

-- What was probably intended: paid orders from customers in VN or US
SELECT * FROM customers c
JOIN orders o USING (customer_id)
WHERE status = 'paid' AND (country = 'VN' OR country = 'US');

Joining tables

Joins combine rows from two or more tables based on a related column, most commonly a foreign key. PostgreSQL supports the full standard set.

INNER JOIN returns only rows that have a match on both sides. A customer with no orders (Emeka) disappears entirely; an order with no matching customer (shouldn’t happen given the foreign key, but conceptually) would also disappear.

SELECT c.full_name, o.order_id, o.total_cents, o.status
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id;

Result (conceptually): every (customer, order) pair where the join condition holds — 6 rows, one per order. Emeka is absent because he has zero orders.

LEFT OUTER JOIN (LEFT JOIN) keeps every row from the left table, filling in NULL for right-side columns when there is no match:

SELECT c.full_name, o.order_id, o.total_cents
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;

Now Emeka appears once, with order_id and total_cents both NULL — useful for “find customers with no orders” queries (WHERE o.order_id IS NULL).

RIGHT OUTER JOIN (RIGHT JOIN) is the mirror image: keep every row from the right table, NULL-pad the left side. It is rarely used in practice because any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping table order, and most style guides prefer the latter for readability.

FULL OUTER JOIN keeps every row from both sides, NULL-padding whichever side has no match:

SELECT c.full_name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id;

This returns everything LEFT JOIN returns, plus any order rows that (hypothetically) had no matching customer.

A text diagram of which rows survive, for a customer with orders (Alice), a customer without orders (Emeka), and a hypothetical orphan order:

                 INNER JOIN     LEFT JOIN      RIGHT JOIN     FULL OUTER JOIN
Alice + orders   kept            kept           kept            kept
Emeka, no orders dropped         kept (NULLs)   dropped         kept (NULLs)
orphan order     dropped         dropped        kept (NULLs)    kept (NULLs)

CROSS JOIN pairs every row on the left with every row on the right — a Cartesian product, with no join condition at all:

SELECT c.full_name, s.status
FROM customers c
CROSS JOIN (VALUES ('pending'), ('paid'), ('cancelled'), ('refunded')) AS s(status);

That query is a legitimate use of CROSS JOIN (generating every customer × status combination, e.g. for a report template). The dangerous case is the accidental cross-join: writing a comma-separated FROM list, or an INNER JOIN, and forgetting the ON/WHERE condition that ties the tables together.

-- BUG: no join condition — this is silently a cross join.
-- With 5 customers and 6 orders, it returns 30 rows, not 6.
SELECT c.full_name, o.order_id
FROM customers c, orders o;

-- Same bug, different syntax — PostgreSQL will run this without complaint
SELECT c.full_name, o.order_id
FROM customers c JOIN orders o ON true;

This is one of the most common real-world SQL bugs: row counts silently multiply, aggregates over-count, and nothing errors — the query just returns wrong numbers. The defense is discipline: always write explicit JOIN ... ON <condition> syntax rather than comma-separated FROM lists, and treat an unexpectedly large row count as a signal to check every join condition.

Self-joins join a table to itself, useful for hierarchical or relative comparisons — e.g., finding pairs of customers from the same country:

SELECT a.full_name AS customer_a, b.full_name AS customer_b, a.country
FROM customers a
JOIN customers b
    ON a.country = b.country
    AND a.customer_id < b.customer_id;   -- avoid matching a row with itself or duplicate pairs

The a.customer_id < b.customer_id condition is the standard trick to avoid (Alice, Alice) self-matches and to avoid getting both (Alice, Bao) and (Bao, Alice).

Grouping and aggregation

GROUP BY collapses rows sharing the same value(s) in specified column(s) into single summary rows, typically paired with aggregate functions: COUNT, SUM, AVG, MIN, MAX.

SELECT
    c.country,
    COUNT(*)                AS order_count,
    SUM(o.total_cents)      AS total_revenue_cents,
    AVG(o.total_cents)      AS avg_order_cents,
    MIN(o.created_at)       AS first_order,
    MAX(o.created_at)       AS last_order
FROM customers c
JOIN orders o USING (customer_id)
GROUP BY c.country;

Every column in the SELECT list must either appear in GROUP BY or be wrapped in an aggregate function — PostgreSQL enforces this and will raise an error (column ... must appear in the GROUP BY clause or be used in an aggregate function) otherwise.

HAVING vs WHERE is a frequent point of confusion because both look like filters, but they operate at different logical stages: WHERE filters individual rows before grouping happens, while HAVING filters groups after aggregation has been computed. This means WHERE cannot reference an aggregate (WHERE COUNT(*) > 1 is illegal), and HAVING is typically used precisely to filter on an aggregate.

-- WHERE removes rows first (only paid orders count toward the aggregates)
-- HAVING then removes whole groups (only countries with >1 paid order)
SELECT
    c.country,
    COUNT(*)           AS paid_order_count,
    SUM(o.total_cents) AS paid_revenue_cents
FROM customers c
JOIN orders o USING (customer_id)
WHERE o.status = 'paid'
GROUP BY c.country
HAVING COUNT(*) > 1
ORDER BY paid_revenue_cents DESC;

A mental shortcut that resolves most of the confusion: if the condition is about a single row’s raw column value (status = 'paid', total_cents > 500), it belongs in WHERE; if the condition is about a computed aggregate over a group (COUNT(*) > 1, SUM(total_cents) > 10000), it belongs in HAVING.

Operators

Beyond comparisons, PostgreSQL supports the usual arithmetic operators (+, -, *, /, %) and string concatenation with ||:

SELECT full_name || ' <' || email || '>' AS contact_line FROM customers;
SELECT total_cents, total_cents / 100.0 AS dollars, total_cents * 1.1 AS with_10pct_tax FROM orders;

PostgreSQL also ships operators tied to its richer types. Two worth knowing exist, even though deep usage is a specialized topic beyond this roadmap’s core scope:

-- Array containment: @> means "left array contains right array"
SELECT ARRAY[1,2,3] @> ARRAY[2,3];   -- true

-- JSONB accessors: -> returns JSONB, ->> returns text, @> tests containment
SELECT '{"country": "VN", "tags": ["vip", "b2b"]}'::jsonb -> 'country';        -- "VN" (jsonb)
SELECT '{"country": "VN", "tags": ["vip", "b2b"]}'::jsonb ->> 'country';       -- VN (text)
SELECT '{"country": "VN"}'::jsonb @> '{"country": "VN"}'::jsonb;              -- true

Full JSONB querying, indexing (GIN), and array operator classes deserve their own dedicated note; here it is enough to recognize the syntax when you see it.

Sorting with ORDER BY

ORDER BY sorts the final result set. Default direction is ascending (ASC); use DESC for descending. Sorting by multiple columns applies each as a tiebreaker for the ones before it:

SELECT full_name, country, signed_up_at
FROM customers
ORDER BY country ASC, signed_up_at DESC;   -- group by country, newest signups first within each

PostgreSQL treats NULL as larger than any non-NULL value by default when sorting ASC (nulls come last) and smaller when sorting DESC (nulls come first) — but this default is rarely what you want to rely on implicitly, so state it explicitly with NULLS FIRST / NULLS LAST:

SELECT order_id, status, created_at
FROM orders
ORDER BY created_at DESC NULLS LAST;

Putting it together: a worked composite query

A realistic reporting query touches most of the clauses above at once — find the top countries by paid revenue among customers who signed up in the last year, showing only countries with at least 2 paid orders, newest-heavy countries first:

SELECT
    c.country,
    COUNT(*)            AS paid_order_count,
    SUM(o.total_cents)   AS paid_revenue_cents
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
  AND c.signed_up_at > now() - interval '1 year'
GROUP BY c.country
HAVING COUNT(*) >= 2
ORDER BY paid_revenue_cents DESC
LIMIT 10;

It is tempting to read this top-to-bottom as PostgreSQL’s execution order, but that is not how it works. SQL is written in one order and logically processed in another — understanding the logical order is what makes HAVING vs WHERE, and why you can’t filter on a SELECT alias in WHERE, make sense:

1. FROM — identify the source tables and evaluate joins, producing a combined row set (customers joined to orders).
2. WHERE — filter individual rows from that combined set (keep only status = 'paid' orders from recently-signed-up customers).
3. GROUP BY — collapse the remaining rows into groups sharing the same country.
4. HAVING — filter out entire groups that don’t meet the aggregate condition (COUNT(*) >= 2).
5. SELECT — compute the output expressions/aggregates for each surviving group.
6. ORDER BY — sort the final rows.
7. LIMIT / OFFSET — cut the result down to the requested window.

This explains two common gotchas: you cannot reference a SELECT-defined alias inside WHERE (because WHERE runs before SELECT computes it — though PostgreSQL does permit referencing SELECT aliases in ORDER BY and GROUP BY as a convenience extension), and you cannot filter on an aggregate in WHERE (because aggregation hasn’t happened yet at that stage).

Key Concepts

Column selection and projection discipline

Selecting only the columns you need (rather than *) is not just style — it reduces I/O when wide tables are involved, avoids breaking downstream code when columns are added later, and makes query intent explicit in code review.

The three-valued logic of NULL

NULL represents “unknown,” not zero or empty string, and it propagates through comparisons: NULL = NULL evaluates to NULL (not true), and NULL in WHERE is treated as “not a match,” so rows are silently excluded rather than raising an error. This is why IS NULL / IS NOT NULL exist as dedicated operators, and why NOT IN (subquery containing a NULL) is a classic footgun — if the subquery returns even one NULL, the entire NOT IN evaluates to unknown for every row and the query returns nothing. Prefer NOT EXISTS over NOT IN when the right-hand side might contain NULLs.

Join condition correctness as a first-class concern

Every join should be read by asking two questions: which rows from each side are guaranteed to survive, and what happens to rows with no match. INNER JOIN is the only join type that can silently drop legitimate data (any row lacking a match vanishes without a trace), which is exactly why “does this need to be a LEFT JOIN?” is one of the first questions worth asking when a report seems to be missing rows.

Aggregates ignore NULL (mostly)

COUNT(*) counts rows regardless of NULLs; COUNT(column) counts only rows where column is non-NULL; SUM, AVG, MIN, MAX all skip NULL values entirely rather than treating them as zero. This matters when reasoning about totals over sparse data — AVG(discount_cents) over a table where most orders have no discount (NULL, not 0) computes the average of applied discounts, not the average discount across all orders.

Best Practices

• Always use explicit JOIN ... ON syntax, never comma-separated FROM lists — it makes the join condition impossible to forget and easy to review.
• Alias tables in multi-table queries (customers c, orders o) so every column reference is unambiguous and queries stay readable as they grow.
• Parenthesize mixed AND/OR conditions explicitly rather than relying on precedence rules from memory — future readers (including future you) will not want to re-derive precedence.
• Prefer NOT EXISTS over NOT IN for subqueries whose result might contain NULL.
• Avoid SELECT * in application code and views; name columns explicitly so schema changes don’t silently change behavior downstream.
• Reach for keyset pagination instead of large OFFSET values on any endpoint or report that might page deep into a large table — see ./10-query-planning-and-performance-tuning.md for the mechanics.
• Use HAVING only for aggregate conditions; if a condition can be expressed on a raw column, push it into WHERE so it filters rows before the (more expensive) grouping step.
• Be explicit about NULLS FIRST/NULLS LAST in any ORDER BY where NULLs are possible and their position matters for correctness, rather than relying on the default.
• Test joins by checking row counts before and after adding a join — an unexpected jump in row count almost always means a missing or wrong join condition.

References

• PostgreSQL Documentation — Queries (SELECT)
• PostgreSQL Documentation — Table Expressions (FROM, WHERE, GROUP BY, HAVING, JOIN)
• PostgreSQL Documentation — Pattern Matching (LIKE, POSIX regex)
• PostgreSQL Documentation — Aggregate Functions
• PostgreSQL Documentation — JSON Functions and Operators
• PostgreSQL Wiki — Row Locking, and common pagination pitfalls
• Use the Index, Luke — pagination done right (keyset pagination)
• roadmap.sh — PostgreSQL DBA