Queries and Joins

SELECT u.email, p.title
FROM users u
JOIN posts p ON p.author_id = u.id;            -- INNER, the default

SELECT u.email, count(p.id) AS n
FROM users u
LEFT JOIN posts p ON p.author_id = u.id        -- keep every user
GROUP BY u.id;

SELECT p.title, u.email
FROM posts p
RIGHT JOIN users u ON u.id = p.author_id;      -- 3.39+

SELECT u.email, p.title
FROM users u
FULL OUTER JOIN posts p ON p.author_id = u.id; -- 3.39+

SELECT * FROM users CROSS JOIN roles;          -- every pair
SELECT * FROM users NATURAL JOIN profiles;     -- joins on same-named columns
SELECT * FROM users JOIN profiles USING(id);   -- one shared column name

-- FULL OUTER JOIN a b ON a.k=b.k, portable to any version
SELECT a.k AS ak, a.v, b.k AS bk, b.w FROM a LEFT JOIN b ON b.k = a.k
UNION ALL
SELECT NULL, NULL, b.k, b.w FROM b
WHERE NOT EXISTS (SELECT 1 FROM a WHERE a.k = b.k);

SELECT sqlite_version();     -- check before relying on RIGHT/FULL

-- Legal in SQLite, an error in Postgres. Which title do you get? Undefined.
SELECT author_id, title, count(*) FROM posts GROUP BY author_id;

-- The newest post per author, defined behavior
SELECT author_id, title, max(created_at) AS latest
FROM posts
GROUP BY author_id;

SELECT author_id, title, created_at
FROM (
  SELECT author_id, title, created_at,
         row_number() OVER (PARTITION BY author_id
                            ORDER BY created_at DESC) AS rn
  FROM posts
)
WHERE rn = 1;

SELECT author_id, count(*) AS n, sum(views) AS v, avg(views) AS a
FROM posts
WHERE status = 'published'
GROUP BY author_id
HAVING count(*) >= 3 AND sum(views) > 1000
ORDER BY v DESC;

SELECT
  count(*) AS all_posts,
  count(*) FILTER (WHERE status='published') AS live,
  total(views) AS views_total,
  group_concat(DISTINCT status) AS statuses
FROM posts;

SELECT
  author_id, title, views,
  row_number() OVER w                          AS rn,
  rank()       OVER w                          AS rnk,
  dense_rank() OVER w                          AS drnk,
  sum(views)   OVER (PARTITION BY author_id)   AS author_total,
  views - lag(views) OVER w                    AS delta,
  first_value(title) OVER w                    AS top_title,
  ntile(4) OVER w                              AS quartile
FROM posts
WINDOW w AS (PARTITION BY author_id ORDER BY views DESC);

SELECT day, amount,
       sum(amount) OVER (ORDER BY day
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running,
       avg(amount) OVER (ORDER BY day
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)         AS ma7
FROM daily
ORDER BY day;

WITH live AS (
  SELECT * FROM posts WHERE status = 'published'
), per_author AS (
  SELECT author_id, count(*) AS n FROM live GROUP BY author_id
)
SELECT u.email, pa.n
FROM per_author pa JOIN users u ON u.id = pa.author_id
ORDER BY pa.n DESC;

-- A number series, the workhorse for generating rows
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 10
)
SELECT n FROM seq;

-- Every day in a month
WITH RECURSIVE d(day) AS (
  SELECT date('2026-01-01')
  UNION ALL
  SELECT date(day, '+1 day') FROM d WHERE day < '2026-01-31'
)
SELECT day FROM d;

-- Walk a parent tree downward, tracking depth and path
WITH RECURSIVE tree(id, name, parent_id, depth, path) AS (
  SELECT id, name, parent_id, 0, name
    FROM nodes WHERE parent_id IS NULL
  UNION ALL
  SELECT n.id, n.name, n.parent_id, t.depth + 1, t.path || ' > ' || n.name
    FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SELECT depth, path FROM tree ORDER BY path;

WITH expensive AS MATERIALIZED (SELECT ... )    -- compute once
SELECT * FROM expensive a JOIN expensive b ON ...;

SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 40;
SELECT * FROM posts ORDER BY created_at DESC LIMIT 40, 20;  -- MySQL form: offset, limit
SELECT * FROM posts ORDER BY random() LIMIT 1;              -- a random row

-- Page 1
SELECT id, created_at, title FROM posts
ORDER BY created_at DESC, id DESC LIMIT 20;
-- Next page, using the last row's values
SELECT id, created_at, title FROM posts
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC LIMIT 20;

SELECT a FROM t1 UNION     SELECT a FROM t2;   -- dedupes
SELECT a FROM t1 UNION ALL SELECT a FROM t2;   -- keeps duplicates, faster
SELECT a FROM t1 INTERSECT SELECT a FROM t2;
SELECT a FROM t1 EXCEPT    SELECT a FROM t2;

SELECT
  CASE WHEN views > 1000 THEN 'hot'
       WHEN views > 100  THEN 'warm'
       ELSE 'cold' END                       AS heat,
  CASE status WHEN 'draft' THEN 0 ELSE 1 END AS is_live,
  iif(views > 100, 'popular', 'quiet')       AS simple,   -- 3.32+
  coalesce(nickname, name, 'anonymous')      AS display,
  ifnull(nickname, name)                     AS two_arg,
  nullif(views, 0)                           AS views_or_null,
  max(1, 2, 3)                               AS scalar_max,
  min(views, 100)                            AS capped
FROM posts;

SELECT NULL = NULL;               -- (empty, meaning NULL)
SELECT NULL IS NULL;              -- 1
SELECT 1 IS NOT 2;                -- 1, works on non-NULL values too
SELECT 5 NOT IN (1, NULL);        -- NULL, so a WHERE on it matches nothing
SELECT 5 NOT IN (SELECT x FROM t WHERE x IS NOT NULL);   -- the fix
SELECT 1/0, 1.0/0;                -- both NULL, SQLite does not raise
SELECT views / nullif(total, 0) FROM stats;              -- safe division

SELECT
  length('héllo'),              -- 5, characters for text
  length(x'0102'),              -- 2, bytes for a blob
  octet_length('héllo'),        -- 6, bytes. 3.43+
  upper('abc'), lower('ABC'),   -- ASCII only, not Unicode-aware
  trim('  x  '), ltrim('xxa','x'), rtrim('axx','x'),
  substr('abcdef', 2, 3),       -- 'bcd', 1-based
  substr('abcdef', -2),         -- 'ef', negative counts from the end
  instr('abcdef', 'cd'),        -- 3, 0 when absent
  replace('a-b-c', '-', '+'),   -- 'a+b+c'
  'a' || 'b' || 'c',            -- concatenation. NULL || 'x' is NULL
  concat('a', NULL, 'b'),       -- 'ab', skips NULLs. 3.44+
  concat_ws('-', 'a', NULL, 'b'),  -- 'a-b'. 3.44+
  printf('%s has %d at %.2f', 'x', 3, 1.5),
  format('%05d', 42),           -- '00042'. 3.38+ alias of printf
  hex('ab'), quote('it''s'),
  char(65, 66), unicode('A'),
  group_concat(status, ', '),
  string_agg(status, ', ')       -- 3.44+ alias of group_concat
FROM posts;

SELECT
  abs(-3), round(2.567, 2), ceil(2.1), floor(2.9),   -- ceil/floor 3.35+
  cast(7 AS REAL)/2, 7%2, 7/2,
  random(), abs(random()) % 100,        -- random() is a signed 64-bit integer
  randomblob(16), hex(randomblob(16)),  -- a random id
  min(3,1,2), max(3,1,2),
  sqrt(16), pow(2,10), ln(exp(1)), log(100), log2(8),  -- math funcs 3.35+
  sqlite_version(), sqlite_source_id(),
  total_changes(), changes(), last_insert_rowid();

SELECT 'Hello' LIKE 'hello';        -- 1, LIKE ignores ASCII case
SELECT 'HÉLLO' LIKE 'héllo';        -- 0, non-ASCII case is NOT folded
SELECT 'Hello' GLOB 'hello';        -- 0
SELECT 'Hello' GLOB 'H*o';          -- 1
SELECT 'a.c' LIKE 'a\.c' ESCAPE '\';  -- escape a literal % or _
SELECT 'abc' REGEXP 'a.c';          -- Error: no such function: REGEXP

CREATE TABLE u(e TEXT COLLATE NOCASE);
CREATE INDEX ue ON u(e);
EXPLAIN QUERY PLAN SELECT * FROM u WHERE e LIKE 'ada%';
-- SEARCH u USING COVERING INDEX ue (e>? AND e<?)

CREATE TABLE v(e TEXT);
CREATE INDEX ve ON v(e);
EXPLAIN QUERY PLAN SELECT * FROM v WHERE e LIKE 'ada%';
-- SCAN v                                     <- full scan
EXPLAIN QUERY PLAN SELECT * FROM v WHERE e GLOB 'ada*';
-- SEARCH v USING COVERING INDEX ve (e>? AND e<?)

EXPLAIN QUERY PLAN
SELECT u.email, p.title FROM users u
JOIN posts p ON p.author_id = u.id
WHERE u.email = 'a@b.c' ORDER BY p.created_at DESC;