DQL Syntax — SELECT, Joins, Set Operations, LIMIT/FETCH

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ { * | expression [ [ AS ] output_name ] } [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
    [ HAVING condition ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression
        [ ASC | DESC | USING operator ]
        [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS }
        { ONLY | WITH TIES } ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE }
        [ OF table_name [, ...] ]
        [ NOWAIT | SKIP LOCKED ] [...] ]

FROM → WHERE → GROUP BY → HAVING → SELECT (target list)
     → DISTINCT → UNION/INTERSECT/EXCEPT → ORDER BY → LIMIT/OFFSET

-- Every column from a single source.
SELECT * FROM orders;

-- All columns from one specific FROM-item with table.* notation.
SELECT o.*, c.name AS customer_name FROM orders o JOIN customers c ON c.id = o.customer_id;

-- Expressions, casts, aliases.
SELECT
    o.id,
    o.amount_cents / 100.0          AS amount_dollars,
    o.created_at::date              AS order_date,
    o.amount_cents > 10000          AS is_big_ticket
FROM orders o;

-- Bare aliases (the AS keyword is optional but recommended for clarity).
SELECT id "Order ID", amount_cents amt FROM orders;   -- legal but ugly

-- Scalar subquery: must return ≤ 1 row × 1 column.
SELECT
    c.id,
    c.name,
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

-- 1. A table (optionally with ONLY to skip inheritance children / partitions).
FROM orders
FROM ONLY orders                              -- only the parent, not partitions
FROM orders *                                 -- redundant; "*" is the historical "include children" marker

-- 2. A subquery (must be aliased).
FROM (SELECT id, amount_cents FROM orders WHERE created_at > now() - interval '7 days') o

-- 3. A function call.
FROM generate_series(1, 100) AS s(n)
FROM unnest(ARRAY['a','b','c']) AS letter
FROM jsonb_each(my_jsonb_col) AS j(k, v)

-- 4. A WITH-query reference.
WITH recent AS (SELECT * FROM orders WHERE created_at > now() - interval '7 days')
SELECT * FROM recent;

-- 5. A VALUES expression (synthetic rows).
FROM (VALUES (1,'a'), (2,'b'), (3,'c')) AS t(id, letter)

-- 6. ROWS FROM(...) (parallel columns from N functions, "zipped" row-wise).
FROM ROWS FROM (generate_series(1,3), generate_series(10,12)) AS r(a, b)

SELECT o.id, o.amount_cents
FROM orders AS o (id, customer_id, amount_cents, created_at)   -- column aliases
WHERE o.id = 1;

-- INNER (the default)
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;

-- LEFT — find orders without a customer row (data-quality probe)
SELECT o.id
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

-- FULL — for set difference / overlap queries
SELECT COALESCE(a.id, b.id) AS id,
       a.value AS value_left,
       b.value AS value_right
FROM table_a a
FULL JOIN table_b b USING (id);

-- CROSS — date-spine times region-spine
SELECT d.day, r.region
FROM generate_series(date '2026-01-01', date '2026-01-31', interval '1 day') AS d(day)
CROSS JOIN (VALUES ('US'),('EU'),('APAC')) AS r(region);

-- ON: the most general; the join columns appear once for each side.
SELECT o.id, o.customer_id, c.id AS c_id, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id;
--                                       ↑↑↑↑     ↑↑↑↑↑
-- Both o.customer_id and c.id are in the output.

-- USING: only one column per join-key in the output (the "merged" column).
SELECT id, customer_id, name
FROM orders JOIN customers USING (id, customer_id);
--             ↑ the USING columns collapse to one each;
--             the rest are appended in left-then-right order

-- NATURAL: USING on every commonly-named column. AVOID.
SELECT * FROM orders NATURAL JOIN customers;

-- Implicit cross product (comma-separated) — equivalent to CROSS JOIN.
SELECT *
FROM orders, customers
WHERE customers.id = orders.customer_id;

-- Top-3 most recent orders per customer.
SELECT c.id, recent.*
FROM customers c
CROSS JOIN LATERAL (
    SELECT id, amount_cents, created_at
    FROM orders
    WHERE customer_id = c.id      -- ← references c, only legal because of LATERAL
    ORDER BY created_at DESC
    LIMIT 3
) AS recent;

-- LATERAL with LEFT JOIN to keep customers who have zero orders.
SELECT c.id, recent.*
FROM customers c
LEFT JOIN LATERAL (
    SELECT id, amount_cents, created_at
    FROM orders
    WHERE customer_id = c.id
    ORDER BY created_at DESC
    LIMIT 3
) AS recent ON TRUE;

-- LATERAL is implicit for set-returning functions in FROM.
SELECT p.id, vertex
FROM polygons p, unnest(p.vertices) AS vertex;   -- LATERAL implied

-- "Re-use a complex expression several times" pattern.
SELECT o.id,
       calc.gross,
       calc.tax,
       calc.gross + calc.tax AS total
FROM orders o
CROSS JOIN LATERAL (
    SELECT (o.amount_cents * 1.0)            AS gross,
           round(o.amount_cents * 0.0875, 0) AS tax
) AS calc;

-- Plain WHERE predicates.
WHERE o.amount_cents > 10000
  AND o.created_at >= now() - interval '7 days'
  AND o.status IN ('paid','refunded')

-- EXISTS: typically the fastest pattern for "row exists in another table".
WHERE EXISTS (SELECT 1 FROM order_items oi WHERE oi.order_id = o.id)

-- NOT EXISTS: NULL-safe anti-join (preferred form).
WHERE NOT EXISTS (SELECT 1 FROM refunds r WHERE r.order_id = o.id)

-- IN with a list, IN with a subquery.
WHERE o.status IN ('paid','refunded')
WHERE o.customer_id IN (SELECT id FROM customers WHERE region = 'EU')

-- ANY / SOME / ALL (equivalent operators).
WHERE o.amount_cents = ANY (ARRAY[100, 200, 300])
WHERE o.amount_cents >= ALL (SELECT min_threshold FROM tiers WHERE active)

-- Basic grouping.
SELECT customer_id, COUNT(*) AS n, SUM(amount_cents) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount_cents) > 100000;

-- GROUP BY can reference SELECT-list position or alias (the latter is a PG extension).
SELECT customer_id, COUNT(*) FROM orders GROUP BY 1;             -- position
SELECT customer_id AS cid, COUNT(*) FROM orders GROUP BY cid;    -- alias (PG-only)

-- GROUPING SETS — explicit multiple groupings in one pass.
SELECT region, product, SUM(qty)
FROM sales
GROUP BY GROUPING SETS ((region, product), (region), (product), ());

-- ROLLUP — n+1 successively shorter prefix groupings.
SELECT region, product, SUM(qty)
FROM sales
GROUP BY ROLLUP (region, product);
-- → groupings: (region,product), (region), ()

-- CUBE — 2^n groupings (the power set).
SELECT region, product, channel, SUM(qty)
FROM sales
GROUP BY CUBE (region, product, channel);

-- DISTINCT: remove duplicate output rows (across the entire SELECT list).
SELECT DISTINCT region FROM sales;

-- DISTINCT ON (cols): for each unique combination of (cols), keep the FIRST row
-- as defined by ORDER BY. The ORDER BY must start with the same columns.
SELECT DISTINCT ON (customer_id) customer_id, id, created_at
FROM orders
ORDER BY customer_id, created_at DESC;
-- → most-recent order per customer.

-- UNION: A or B, duplicates removed (DEFAULT). Slow on big sets.
SELECT id FROM customers_a UNION SELECT id FROM customers_b;

-- UNION ALL: A or B, duplicates kept. Fast — use this unless you specifically need dedup.
SELECT id FROM customers_a UNION ALL SELECT id FROM customers_b;

-- INTERSECT: rows in both A and B (default DISTINCT; INTERSECT ALL is the multiset variant).
SELECT id FROM customers_a INTERSECT SELECT id FROM customers_b;

-- EXCEPT: rows in A but not B (default DISTINCT; EXCEPT ALL is multiset).
SELECT id FROM customers_a EXCEPT SELECT id FROM customers_b;

-- ORDER BY / LIMIT apply to the *combined* result; place them at the very end.
( SELECT id, name FROM customers_a
  UNION ALL
  SELECT id, name FROM customers_b )
ORDER BY id
LIMIT 100;

ORDER BY created_at DESC
ORDER BY amount_cents DESC NULLS LAST
ORDER BY customer_id, created_at DESC NULLS FIRST
ORDER BY name COLLATE "en_US"
ORDER BY some_expression USING <      -- sort with the < operator of the column's type
ORDER BY 1, 2 DESC                    -- by SELECT-list position (legal but discouraged)

-- Common mistake: silently NULLs-first.
SELECT name FROM users ORDER BY last_login_at DESC;

-- Almost always what you actually meant:
SELECT name FROM users ORDER BY last_login_at DESC NULLS LAST;

-- PostgreSQL form (always available; SQL-standard FETCH form is preferred for new code).
SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 100;

-- SQL:2008 form. Equivalent.
SELECT * FROM orders ORDER BY id
OFFSET 100 ROWS
FETCH NEXT 20 ROWS ONLY;

-- Worker claims up to 10 jobs at a time without blocking on jobs claimed by other workers.
BEGIN;

SELECT id, payload
FROM jobs
WHERE status = 'queued'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;

-- ... do the work, then ...

UPDATE jobs SET status = 'done' WHERE id = ANY (:claimed_ids);
COMMIT;

-- ~1% sample by row (more accurate, slow).
SELECT * FROM big_table TABLESAMPLE BERNOULLI (1);

-- ~1% sample by page (less accurate, fast).
SELECT * FROM big_table TABLESAMPLE SYSTEM (1);

-- Reproducible sample.
SELECT * FROM big_table TABLESAMPLE BERNOULLI (1) REPEATABLE (42);

-- Per-row index of a set-returning function.
SELECT * FROM unnest(ARRAY['a','b','c']) WITH ORDINALITY AS t(value, idx);
-- value | idx
-- ------+----
-- a     | 1
-- b     | 2
-- c     | 3

-- ROWS FROM — zip multiple set-returning functions side by side.
SELECT * FROM ROWS FROM (
    generate_series(1, 3),
    unnest(ARRAY['a','b','c'])
) AS t(num, letter);
-- num | letter
-- ----+-------
--   1 | a
--   2 | b
--   3 | c

-- VALUES as a stand-alone statement.
VALUES (1, 'a'), (2, 'b'), (3, 'c');

-- VALUES as a FROM-item (must be aliased).
SELECT v.id, v.label
FROM (VALUES (1, 'a'), (2, 'b'), (3, 'c')) AS v(id, label);

-- VALUES as a join lookup.
SELECT o.id, o.status, label.text
FROM orders o
LEFT JOIN (VALUES ('p','pending'), ('a','approved'), ('r','rejected')) AS label(code, text)
       ON label.code = o.status;

TABLE orders;                          -- equivalent to: SELECT * FROM orders
TABLE ONLY orders;                     -- exclude inheritance children / partitions

-- Useful in set operations:
TABLE customers_a UNION ALL TABLE customers_b;

-- Sample document: an array of orders, each with an array of items.
WITH docs(doc) AS (VALUES (
'{
  "orders": [
    {"id": 1, "items": [{"sku":"A","qty":2}, {"sku":"B","qty":1}]},
    {"id": 2, "items": [{"sku":"C","qty":5}]}
  ]
}'::jsonb))
SELECT j.*
FROM docs,
     JSON_TABLE(
         doc,
         '$.orders[*]' AS root
         COLUMNS (
             order_id  int     PATH '$.id',
             NESTED PATH '$.items[*]'
                 COLUMNS (
                     sku   text PATH '$.sku',
                     qty   int  PATH '$.qty',
                     line  FOR ORDINALITY
                 )
         )
     ) AS j;
-- order_id | sku | qty | line
-- ---------+-----+-----+------
--        1 | A   |   2 | 1
--        1 | B   |   1 | 2
--        2 | C   |   5 | 1

-- A. DISTINCT ON — fastest for N = 1, single-pass.
SELECT DISTINCT ON (customer_id)
    customer_id, id, amount_cents, created_at
FROM orders
ORDER BY customer_id, created_at DESC;

-- B. LATERAL — most flexible; handles N > 1 cleanly.
SELECT c.id AS customer_id, r.*
FROM customers c
CROSS JOIN LATERAL (
    SELECT id, amount_cents, created_at
    FROM orders
    WHERE customer_id = c.id
    ORDER BY created_at DESC
    LIMIT 3
) AS r;

-- C. Window function — readable, but materializes the full join then filters.
SELECT customer_id, id, amount_cents, created_at
FROM (
    SELECT customer_id, id, amount_cents, created_at,
           row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
    FROM orders
) s
WHERE rn <= 3;

-- Rows in staging that didn't make it to production (and vice versa).
( SELECT id FROM stage.orders EXCEPT SELECT id FROM prod.orders )
UNION ALL
( SELECT id FROM prod.orders EXCEPT SELECT id FROM stage.orders );

-- Row-level diff (every column equal).
SELECT row_to_json(s.*) AS only_in_stage
FROM stage.orders s
WHERE NOT EXISTS (
    SELECT 1 FROM prod.orders p
    WHERE p.id = s.id
      AND p.amount_cents IS NOT DISTINCT FROM s.amount_cents
      AND p.status        IS NOT DISTINCT FROM s.status
);

-- Estimate distinct customer_id values cheaply on a giant table.
SELECT COUNT(DISTINCT customer_id) * 100 AS est_distinct
FROM events TABLESAMPLE SYSTEM (1);

-- HLL via the hll extension is much more accurate but requires the extension.

-- First page.
SELECT id, created_at, amount_cents
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Subsequent page: pass the (created_at, id) tuple of the LAST row of the previous page.
SELECT id, created_at, amount_cents
FROM orders
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- One-shot random sample (different each call).
SELECT * FROM orders ORDER BY random() LIMIT 100;

-- Reproducible random sample using setseed + random().
BEGIN;
SELECT setseed(0.42);
SELECT * FROM orders ORDER BY random() LIMIT 100;
COMMIT;

-- For sampling much larger tables, prefer TABLESAMPLE BERNOULLI with REPEATABLE.
SELECT * FROM orders TABLESAMPLE BERNOULLI (0.1) REPEATABLE (42) LIMIT 100;

-- For each user, find the 10 most-similar users by trigram similarity of names.
SELECT u.id, sim.peer_id, sim.score
FROM users u
CROSS JOIN LATERAL (
    SELECT peer.id AS peer_id, similarity(u.name, peer.name) AS score
    FROM users peer
    WHERE peer.id <> u.id
    ORDER BY u.name <-> peer.name      -- requires pg_trgm
    LIMIT 10
) AS sim;

WITH inv(doc) AS (
  VALUES (
    '{"invoice_id": "INV-7", "items": [
        {"sku":"A","qty":2,"price_cents":1500},
        {"sku":"B","qty":1,"price_cents":4000}
      ]}'::jsonb)
)
SELECT t.*
FROM inv,
     JSON_TABLE(
         doc, '$' AS root
         COLUMNS (
             invoice_id text PATH '$.invoice_id',
             NESTED PATH '$.items[*]'
                 COLUMNS (
                     sku         text PATH '$.sku',
                     qty         int  PATH '$.qty',
                     price_cents int  PATH '$.price_cents',
                     line_no     FOR ORDINALITY
                 )
         )
     ) AS t;

-- Pre-PG17 equivalent.
SELECT
    doc ->> 'invoice_id'              AS invoice_id,
    item ->> 'sku'                    AS sku,
    (item ->> 'qty')::int             AS qty,
    (item ->> 'price_cents')::int     AS price_cents,
    line_no
FROM inv,
     LATERAL jsonb_array_elements(doc -> 'items') WITH ORDINALITY AS it(item, line_no);

-- One worker, one transaction. Many workers running this concurrently will not block one another.
BEGIN;

WITH next AS (
    SELECT id
    FROM jobs
    WHERE status = 'queued'
      AND run_after <= now()
    ORDER BY created_at
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
UPDATE jobs
SET status = 'running', started_at = now()
WHERE id = (SELECT id FROM next)
RETURNING id, payload;

-- ... worker does its thing using the returned id+payload ...

COMMIT;

-- Without LATERAL: window function (preferred when you need it across all rows).
SELECT id, customer_id, amount_cents,
       SUM(amount_cents) OVER (PARTITION BY customer_id ORDER BY created_at) AS running_total
FROM orders;

-- With LATERAL: useful when you only need running total for a *subset* of rows.
SELECT o.id, o.customer_id, o.amount_cents, r.running_total
FROM orders o
CROSS JOIN LATERAL (
    SELECT SUM(amount_cents) AS running_total
    FROM orders o2
    WHERE o2.customer_id = o.customer_id
      AND o2.created_at  <= o.created_at
) AS r
WHERE o.id IN (SELECT id FROM flagged_orders);