August 10, 202637 min read

Intermediate SQL: 200 Commands and Techniques

JOINs, subqueries, CTEs, views, indexes, transactions, JSON and in-database programming: 200 items with practical examples.

sql
banco-de-dados
intermediario
referencia

Intermediate SQL: 200 Commands and Techniques

You already have SELECT, WHERE and GROUP BY down. These 200 items are what separates someone who queries data from someone who models and maintains a database: relationships, indexes, transactions, views, JSON and code running inside the database itself.

The reference is still PostgreSQL, with the differences for MySQL, SQL Server, Oracle and Firebird noted in the examples.

If an item looks too advanced right now, skip it and come back later. What matters is knowing it exists — when the problem shows up, you will remember where to look.

1–20 · JOINs: combining tables
21–35 · Subqueries
36–45 · CTEs: organising queries
46–53 · Set operations
54–63 · Views
64–81 · Indexes
82–97 · Transactions and concurrency
98–109 · Integrity and constraints
110–124 · Window functions: the essentials
125–138 · JSON inside the database
139–158 · Programming inside the database
159–170 · Modelling and normalisation
171–180 · Loading and moving data
181–190 · Users and permissions
191–200 · Metadata and maintenance

JOINs: combining tables

Normalised data lives apart. A JOIN is how you put it back together.

1. INNER JOIN

Brings back only the rows that exist on both sides. It is the most used JOIN.

SELECT ped.id,
       cli.nome,
       ped.total
FROM pedidos ped
INNER JOIN clientes cli
ON cli.id = ped.cliente_id;

2. LEFT JOIN

Brings back every row from the left; where there is no match, the right-hand columns come back NULL.

SELECT cli.nome,
       ped.id AS pedido
FROM clientes cli
LEFT JOIN pedidos ped
ON ped.cliente_id = cli.id;

3. RIGHT JOIN

The mirror of LEFT. In practice, swap the tables and use LEFT — it reads more easily.

SELECT cli.nome,
       ped.id
FROM pedidos ped
RIGHT JOIN clientes cli
ON cli.id = ped.cliente_id;

4. FULL OUTER JOIN

Brings back everything from both sides. MySQL does not have it: simulate it with LEFT UNION RIGHT.

SELECT cli.nome,
       ped.id
FROM clientes cli
FULL OUTER JOIN pedidos ped
ON ped.cliente_id = cli.id;

5. CROSS JOIN

The Cartesian product: every row of A with every row of B. Useful for generating combinations, dangerous by accident.

SELECT tim.nome,
       mes.mes
FROM times tim
CROSS JOIN meses mes;

6. SELF JOIN

The table with itself. The basis of hierarchies such as employee and manager.

SELECT fun.nome AS funcionario,
       ger.nome AS gerente
FROM funcionarios fun
LEFT JOIN funcionarios ger
ON ger.id = fun.gerente_id;

7. JOIN with several conditions

The ON accepts any expression, not only key equality.

SELECT *
FROM precos pre
INNER JOIN vigencias vig
ON vig.produto_id = pre.produto_id
AND pre.data BETWEEN vig.inicio AND vig.fim;

8. JOIN of three or more tables

Chain the JOINs in the order that makes sense for the relationship.

SELECT cli.nome,
       ped.id,
       ite.produto,
       ite.qtd
FROM clientes cli
INNER JOIN pedidos ped
ON ped.cliente_id = cli.id
INNER JOIN itens ite
ON ite.pedido_id = ped.id;

9. USING — the ON shortcut

When the column has the same name in both tables, USING shortens it and removes the duplicate from the result.

SELECT *
FROM pedidos
INNER JOIN clientes USING (cliente_id);

10. NATURAL JOIN

Joins automatically on every same-named column. Avoid it: any new column silently changes the result.

SELECT *
FROM pedidos
NATURAL JOIN clientes;  -- prefer an explicit ON

11. Anti-join with LEFT + IS NULL

Finds what has no match — customers with no orders.

SELECT cli.*
FROM clientes cli
LEFT JOIN pedidos ped
ON ped.cliente_id = cli.id
WHERE ped.id IS NULL;

12. Anti-join with NOT EXISTS

The same question, usually with a better plan and immune to NULL.

SELECT cli.*
FROM clientes cli
WHERE NOT EXISTS (SELECT 1 FROM pedidos ped WHERE ped.cliente_id = cli.id);

13. Semi-join with EXISTS

Whoever has at least one related row, without duplicating the rows as a JOIN would.

SELECT cli.*
FROM clientes cli
WHERE EXISTS (SELECT 1 FROM pedidos ped WHERE ped.cliente_id = cli.id);

14. A filter in the ON vs in the WHERE

In a LEFT JOIN it makes a difference: in the ON it filters the right-hand side; in the WHERE it accidentally becomes an INNER JOIN.

-- Keeps every customer
SELECT cli.nome,
       ped.id
FROM clientes cli
LEFT JOIN pedidos ped
ON ped.cliente_id = cli.id AND ped.status = 'pago';

-- Becomes an INNER JOIN (discards customers with no paid order)
SELECT cli.nome, ped.id
FROM clientes cli
LEFT JOIN pedidos ped
ON ped.cliente_id = cli.id
WHERE ped.status = 'pago';

15. JOIN with aggregation

Aggregate before joining so as not to multiply rows and inflate the sums.

SELECT cli.nome,
       ped_tot.total
FROM clientes cli
INNER JOIN (SELECT cliente_id, SUM(total) AS total
            FROM pedidos
            GROUP BY cliente_id) ped_tot
ON ped_tot.cliente_id = cli.id;

16. LATERAL JOIN

The right-hand subquery can see the left-hand columns. Ideal for 'the last 3 of each'.

SELECT cli.nome,
       ped_rec.*
FROM clientes cli
CROSS JOIN LATERAL (
  SELECT *
  FROM pedidos ped
  WHERE ped.cliente_id = cli.id
  ORDER BY ped.criado_em DESC
  LIMIT 3
) ped_rec;

17. APPLY — SQL Server

SQL Server's equivalent of LATERAL: CROSS APPLY and OUTER APPLY.

SELECT cli.nome,
       ped_rec.*
FROM clientes cli
CROSS APPLY (SELECT TOP 3 *
             FROM pedidos ped
             WHERE ped.cliente_id = cli.id
             ORDER BY ped.criado_em DESC) ped_rec;

18. JOIN through a link table

A many-to-many relationship goes through a third table.

SELECT art.titulo,
       tag.nome AS tag
FROM artigos art
INNER JOIN artigo_tag art_tag
ON art_tag.artigo_id = art.id
INNER JOIN tags tag
ON tag.id = art_tag.tag_id;

19. Careful with duplication

A one-to-many JOIN multiplies the rows on the 'one' side. Summing after that inflates the result.

-- Wrong: the total repeated per item
SELECT SUM(ped.total)
FROM pedidos ped
INNER JOIN itens ite
ON ite.pedido_id = ped.id;

-- Right
SELECT SUM(total)
FROM pedidos;

20. JOIN on a date range

Matches each event with the range in force. Heavily used with price and exchange-rate tables.

SELECT ven.id,
       cot.taxa
FROM vendas ven
INNER JOIN cotacoes cot
ON ven.data >= cot.inicio AND ven.data < cot.fim;

Subqueries

A query inside another: it filters, calculates and feeds the main query.

21. Scalar subquery

It returns a single value and can be used as if it were a column.

SELECT nome, (SELECT COUNT(*) FROM pedidos ped WHERE ped.cliente_id = cli.id) AS pedidos
FROM clientes cli;

22. Subquery in the WHERE

Filters by the list the subquery returns.

SELECT * FROM produtos
WHERE categoria_id IN (SELECT id FROM categorias WHERE ativa);

23. Subquery in the FROM

The subquery becomes a temporary (derived) table. It needs an alias.

SELECT cidade, media
FROM (SELECT cidade, AVG(total) AS media
      FROM pedidos
      GROUP BY cidade) AS med_cidade
WHERE media > 500;

24. Correlated subquery

It references the outer query's row; it runs once per row. Powerful, but expensive.

SELECT cli.nome
FROM clientes cli
WHERE (SELECT COUNT(*) FROM pedidos ped WHERE ped.cliente_id = cli.id) > 5;

25. EXISTS vs IN

With many rows, EXISTS usually wins; with small, fixed lists, IN is simpler.

SELECT * FROM clientes cli WHERE EXISTS (SELECT 1 FROM pedidos ped WHERE ped.cliente_id = cli.id);

26. NOT IN with NULL

If the subquery returns a NULL, NOT IN returns nothing. Use NOT EXISTS.

-- Dangerous
SELECT * FROM clientes WHERE id NOT IN (SELECT cliente_id FROM pedidos);

-- Safe
SELECT * FROM clientes cli WHERE NOT EXISTS (SELECT 1 FROM pedidos ped WHERE ped.cliente_id = cli.id);

27. ANY / SOME

Compares against any value in the returned list.

SELECT * FROM produtos WHERE preco > ANY (SELECT preco FROM produtos WHERE categoria = 'games');

28. ALL

The condition has to hold for every value in the list.

SELECT * FROM produtos WHERE preco >= ALL (SELECT preco FROM produtos WHERE categoria = 'games');

29. Subquery in the SELECT vs a JOIN

Sometimes an aggregated JOIN replaces the correlated subquery with a big performance gain.

SELECT cli.nome,
       COALESCE(ped_qtd.qtd, 0) AS pedidos
FROM clientes cli
LEFT JOIN (SELECT cliente_id, COUNT(*) AS qtd
           FROM pedidos
           GROUP BY cliente_id) ped_qtd
ON ped_qtd.cliente_id = cli.id;

30. Subquery with LIMIT

Takes a specific value, such as the customer's last order.

SELECT cli.nome,
  (SELECT ped.total FROM pedidos ped WHERE ped.cliente_id = cli.id ORDER BY ped.criado_em DESC LIMIT 1) AS ultimo
FROM clientes cli;

31. Subquery in an UPDATE

Calculates the new value from another table.

UPDATE produtos
SET estoque = (SELECT SUM(qtd) FROM movimentos mov WHERE mov.produto_id = produtos.id);

32. Subquery in a DELETE

Deletes based on a criterion coming from another table.

DELETE FROM carrinho
WHERE produto_id IN (SELECT id FROM produtos WHERE descontinuado);

33. Subquery in the HAVING

Compares the group's aggregation against a calculated value.

SELECT cidade, AVG(total) AS media
FROM pedidos GROUP BY cidade
HAVING AVG(total) > (SELECT AVG(total) FROM pedidos);

34. Row subquery

Compares several columns at once.

SELECT * FROM pedidos
WHERE (cliente_id, criado_em) IN (SELECT cliente_id, MAX(criado_em) FROM pedidos GROUP BY cliente_id);

35. When to swap it for a CTE

A subquery repeated in the same query? Extract it into a CTE: it reads better and is evaluated once.

WITH media AS (SELECT AVG(total) AS valor FROM pedidos)
SELECT * FROM pedidos, media WHERE total > media.valor;

CTEs: organising queries

WITH turns an unreadable query into named steps.

36. WITH — a simple CTE

Names an intermediate result and uses it right below.

WITH ativos AS (
  SELECT * FROM clientes WHERE ativo
)
SELECT COUNT(*) FROM ativos;

37. Several CTEs

Separate them with commas and build the query in stages.

WITH pagos AS (
  SELECT * FROM pedidos WHERE status = 'pago'
), por_cliente AS (
  SELECT cliente_id, SUM(total) AS total FROM pagos GROUP BY cliente_id
)
SELECT * FROM por_cliente ORDER BY total DESC LIMIT 10;

38. A CTE referencing another

Each CTE can see the previous ones — it is a transformation pipeline.

WITH base AS (SELECT * FROM vendas WHERE ano = 2026),
     resumo AS (SELECT regiao, SUM(valor) AS total FROM base GROUP BY regiao)
SELECT * FROM resumo WHERE total > 100000;

39. CTE in an UPDATE and a DELETE

Prepares the target set before changing it.

WITH antigos AS (
  SELECT id FROM sessoes WHERE criado_em < CURRENT_DATE - INTERVAL '90 days'
)
DELETE FROM sessoes WHERE id IN (SELECT id FROM antigos);

40. Recursive CTE

The CTE calls itself: it walks hierarchies and generates series.

WITH RECURSIVE hierarquia AS (
  SELECT id, nome, gerente_id, 1 AS nivel FROM funcionarios WHERE gerente_id IS NULL
  UNION ALL
  SELECT fun.id, fun.nome, fun.gerente_id, hie.nivel + 1
  FROM funcionarios fun
  INNER JOIN hierarquia hie
  ON fun.gerente_id = hie.id
)
SELECT *
FROM hierarquia
ORDER BY nivel;

41. A date series with a recursive CTE

Creates a calendar to fill in days with no sales in the report.

WITH RECURSIVE dias AS (
  SELECT DATE '2026-01-01' AS dia
  UNION ALL
  SELECT dia + 1 FROM dias WHERE dia < DATE '2026-01-31'
)
SELECT * FROM dias;

42. Careful with infinite recursion

Always guarantee a stopping condition; many databases let you limit the depth.

WITH RECURSIVE r AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM r WHERE n < 100   -- mandatory stop
)
SELECT COUNT(*) FROM r;

43. Materialised CTE

On PostgreSQL you control whether the CTE becomes a temporary table or is folded into the query.

WITH pesada AS MATERIALIZED (
  SELECT * FROM eventos WHERE tipo = 'compra'
)
SELECT COUNT(*) FROM pesada;

44. CTE vs subquery

A CTE wins on readability and reuse; a subquery sometimes wins on the plan. Measure both.

-- The same answer, different ways of writing it
WITH ped_tot AS (
  SELECT cliente_id, SUM(total) AS total
  FROM pedidos
  GROUP BY cliente_id
)
SELECT *
FROM ped_tot
WHERE total > 1000;

45. CTE with literal data

VALUES inside a CTE creates a support table without having to create a table.

WITH faixas(nome, minimo, maximo) AS (
  VALUES ('barato', 0, 50), ('medio', 50, 200), ('caro', 200, 999999)
)
SELECT prod.nome,
       fai.nome AS faixa
FROM produtos prod
INNER JOIN faixas fai
ON prod.preco >= fai.minimo AND prod.preco < fai.maximo;

Set operations

Stacking and comparing the results of different queries.

46. UNION

Stacks two results and removes duplicates (it costs a sort).

SELECT email FROM clientes
UNION
SELECT email FROM leads;

47. UNION ALL

Stacks without removing duplicates. Much faster — use it when no repetition is possible.

SELECT id, 'pedido' AS origem FROM pedidos
UNION ALL
SELECT id, 'orcamento' FROM orcamentos;

48. INTERSECT

Only what appears in both results.

SELECT email FROM clientes
INTERSECT
SELECT email FROM newsletter;

49. EXCEPT / MINUS

What is in the first and not in the second. On Oracle it is called MINUS.

SELECT email FROM leads
EXCEPT
SELECT email FROM clientes;

50. Compatibility rules

The queries need the same number of columns and compatible types, in the same order.

SELECT nome, email FROM clientes
UNION ALL
SELECT razao_social, contato FROM empresas;

51. ORDER BY on a union

It applies to the whole result and comes at the end, just once.

SELECT nome FROM clientes
UNION ALL
SELECT nome FROM fornecedores
ORDER BY nome;

52. Simulate a FULL JOIN on MySQL

MySQL has no FULL OUTER JOIN: use LEFT UNION RIGHT.

SELECT cli.nome,
       ped.id
FROM clientes cli
LEFT JOIN pedidos ped
ON ped.cliente_id = cli.id
UNION
SELECT cli.nome, ped.id
FROM clientes cli
RIGHT JOIN pedidos ped
ON ped.cliente_id = cli.id;

53. Compare two tables

EXCEPT in both directions shows exactly what differs between environments.

(SELECT * FROM producao.clientes EXCEPT SELECT * FROM homolog.clientes)
UNION ALL
(SELECT * FROM homolog.clientes EXCEPT SELECT * FROM producao.clientes);

Views

A saved query with a name: it hides complexity and standardises a business rule.

54. CREATE VIEW

Saves a query as if it were a virtual table.

CREATE VIEW vw_pedidos_pagos AS
SELECT ped.*, cli.nome AS cliente
FROM pedidos ped JOIN clientes cli ON cli.id = ped.cliente_id
WHERE ped.status = 'pago';

55. Query a view

Use it like any table — including in a JOIN.

SELECT * FROM vw_pedidos_pagos WHERE total > 500;

56. CREATE OR REPLACE VIEW

Updates the definition without having to drop it (the columns must be compatible).

CREATE OR REPLACE VIEW vw_pedidos_pagos AS
SELECT ped.*, cli.nome AS cliente, cli.cidade
FROM pedidos ped JOIN clientes cli ON cli.id = ped.cliente_id
WHERE ped.status = 'pago';

57. DROP VIEW

Removes the view. The data stays intact: a view stores nothing.

DROP VIEW IF EXISTS vw_pedidos_pagos;

58. A view to simplify a report

It encapsulates the JOIN and the rule, and the business team queries it without knowing the model.

CREATE VIEW vw_faturamento_mensal AS
SELECT DATE_TRUNC('month', criado_em) AS mes, SUM(total) AS faturamento
FROM pedidos WHERE status = 'pago'
GROUP BY 1;

59. A view as a security layer

Expose only the allowed columns and grant permission on the view, not on the table.

CREATE VIEW vw_clientes_publico AS
SELECT id, nome, cidade FROM clientes;   -- without email and phone

GRANT SELECT ON vw_clientes_publico TO app_leitura;

60. Updatable view

Simple views (one table, no aggregation) accept a direct INSERT/UPDATE.

CREATE VIEW vw_ativos AS SELECT * FROM clientes WHERE ativo;
UPDATE vw_ativos SET cidade = 'Campinas' WHERE id = 1;

61. WITH CHECK OPTION

Prevents writing through the view a row that would fall outside its filter.

CREATE VIEW vw_ativos AS
SELECT * FROM clientes WHERE ativo
WITH CHECK OPTION;

62. Materialised view

It stores the result on disk: fast to read, data slightly behind. It needs a refresh.

CREATE MATERIALIZED VIEW mv_faturamento AS
SELECT DATE_TRUNC('month', criado_em) AS mes, SUM(total) AS total
FROM pedidos GROUP BY 1;

63. REFRESH MATERIALIZED VIEW

Recalculates it. CONCURRENTLY avoids blocking reads (it requires a unique index).

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_faturamento;

Indexes

What turns a 30-second query into 3 milliseconds — and the INSERT into something slower.

64. CREATE INDEX

Creates an index on the column most used in filters and JOINs.

CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);

65. Composite index

Covers filters on several columns. The order matters: start with the most selective/most filtered one.

CREATE INDEX idx_pedidos_cliente_data ON pedidos (cliente_id, criado_em);

66. The leftmost-prefix rule

An index on (a, b) serves filters on a and on a + b, but not on b alone.

-- Uses the index
SELECT * FROM pedidos WHERE cliente_id = 1;
-- Does not
SELECT * FROM pedidos WHERE criado_em > CURRENT_DATE;

67. Unique index

Guarantees uniqueness and still serves as a search index.

CREATE UNIQUE INDEX uq_clientes_email ON clientes (email);

68. Partial index

Indexes only the rows that matter: smaller, faster and cheaper to maintain (PostgreSQL).

CREATE INDEX idx_pedidos_abertos ON pedidos (criado_em) WHERE status = 'aberto';

69. Expression index

Indexes the result of a function — necessary when the filter uses the function.

CREATE INDEX idx_clientes_email_lower ON clientes (LOWER(email));
SELECT * FROM clientes WHERE LOWER(email) = 'ana@email.com';

70. Descending index

Aligns the index with the most-used ordering, avoiding a sort on every query.

CREATE INDEX idx_pedidos_recentes ON pedidos (criado_em DESC);

71. Covering index

If the index contains every column of the query, the database never reads the table.

CREATE INDEX idx_cobertura ON pedidos (cliente_id) INCLUDE (total, status);

72. DROP INDEX

An index nobody uses only costs space and write time. Remove it.

DROP INDEX IF EXISTS idx_pedidos_cliente;

73. Create an index without locking

CONCURRENTLY (PostgreSQL) and ONLINE (SQL Server/Oracle) create it without blocking writes.

CREATE INDEX CONCURRENTLY idx_pedidos_status ON pedidos (status);

74. See a table's indexes

Each database exposes this in a different catalogue.

-- PostgreSQL
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'pedidos';

-- MySQL
SHOW INDEX FROM pedidos;

75. Indexes and writes

Every index has to be updated on each INSERT/UPDATE/DELETE. Too many indexes make writing slow.

-- Rule of thumb: index what you actually filter/sort by,
-- and review the unused indexes periodically.

76. An index unused because of a conversion

Comparing an indexed column against a different type discards the index.

-- Does not use the index (id is an integer)
SELECT * FROM pedidos WHERE id::text = '42';

-- Does
SELECT * FROM pedidos WHERE id = 42;

77. An index for text search

LIKE '%something%' does not use a B-tree index. A prefix ('something%') does; for the rest, use full-text or trigrams.

CREATE INDEX idx_produtos_nome ON produtos (nome varchar_pattern_ops);
SELECT * FROM produtos WHERE nome LIKE 'Note%';

78. GIN index for JSON and text

A structure of its own for composite content: JSONB, arrays and text search (PostgreSQL).

CREATE INDEX idx_config_dados ON configuracoes USING GIN (dados);

79. An index on a foreign key

Databases create an index on the PK, but not always on the FK. Without it, the JOIN and the parent's DELETE get slow.

CREATE INDEX idx_itens_pedido ON itens (pedido_id);

80. Duplicate indexes

An index on (a) is redundant if (a, b) already exists. Remove the smaller one.

-- (cliente_id) is covered by (cliente_id, criado_em)
DROP INDEX idx_pedidos_cliente;

81. REINDEX

Rebuilds indexes bloated by heavy updating.

REINDEX TABLE pedidos;

Transactions and concurrency

Guaranteeing that operations happen in full — or do not happen at all.

82. BEGIN — open a transaction

From here on, nothing is final until the COMMIT.

BEGIN;
UPDATE contas SET saldo = saldo - 100 WHERE id = 1;
UPDATE contas SET saldo = saldo + 100 WHERE id = 2;
COMMIT;

83. COMMIT

Confirms everything the transaction did.

COMMIT;

84. ROLLBACK

Undoes everything since the BEGIN. Your safety net.

BEGIN;
DELETE FROM clientes;   -- oops
ROLLBACK;

85. SAVEPOINT

An intermediate point: you can undo just part of the transaction.

BEGIN;
INSERT INTO log VALUES ('inicio');
SAVEPOINT p1;
DELETE FROM temporarios;
ROLLBACK TO p1;    -- undoes only the DELETE
COMMIT;

86. ACID

Atomicity, Consistency, Isolation and Durability — the guarantees a relational database gives.

-- Atomicity: all or nothing
-- Consistency: constraints always valid
-- Isolation: transactions do not disturb each other
-- Durability: once committed, it is written

87. READ COMMITTED

The default level on most: you only see what has already been committed.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

88. REPEATABLE READ

The same query returns the same result throughout the transaction.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

89. SERIALIZABLE

The strictest level: a result equivalent to running the transactions one after another. Safer, more conflict.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

90. Dirty read

Reading uncommitted data. It only happens under READ UNCOMMITTED — avoid it.

-- SQL Server: NOLOCK does a dirty read. Use it very knowingly.
SELECT * FROM pedidos WITH (NOLOCK);

91. SELECT FOR UPDATE

Locks the rows read until the end of the transaction, stopping another session changing them halfway.

BEGIN;
SELECT * FROM estoque WHERE produto_id = 1 FOR UPDATE;
UPDATE estoque SET qtd = qtd - 1 WHERE produto_id = 1;
COMMIT;

92. SKIP LOCKED

Skips locked rows instead of waiting. The basis of a work queue with several consumers.

SELECT * FROM fila WHERE status = 'pendente'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;

93. NOWAIT

Fails immediately instead of waiting for the lock.

SELECT * FROM contas WHERE id = 1 FOR UPDATE NOWAIT;

94. Deadlock

Two transactions waiting on each other. The database kills one of them. Prevent it by always accessing the tables in the same order.

-- Session A: contas -> pedidos
-- Session B: pedidos -> contas   <- deadlock risk
-- Standardise the access order

95. Lost update

Reading, calculating in the application and writing overwrites someone else's work. Update in SQL itself.

-- Fragile
SELECT saldo FROM contas WHERE id = 1;   -- the app adds
UPDATE contas SET saldo = 150 WHERE id = 1;

-- Safe
UPDATE contas SET saldo = saldo + 50 WHERE id = 1;

96. Optimistic locking

A version column detects a concurrent change without holding a lock.

UPDATE produtos SET preco = 99, versao = versao + 1
WHERE id = 1 AND versao = 7;   -- 0 rows = someone changed it first

97. Short transactions

An open transaction holds locks and blocks others. Open late, close early, and never wait on external I/O inside it.

-- Avoid: BEGIN; ... HTTP call ... COMMIT;
-- Make the external call outside the transaction.

Integrity and constraints

Rules the database enforces on its own — they apply to any application that writes to it.

98. Name the constraints

An explicit name makes the database's error say exactly which rule was violated.

ALTER TABLE pedidos
ADD CONSTRAINT ck_pedidos_total_positivo CHECK (total >= 0);

99. Foreign key with an action

Define what happens to the children when the parent changes or disappears.

ALTER TABLE itens ADD CONSTRAINT fk_itens_pedido
FOREIGN KEY (pedido_id) REFERENCES pedidos (id)
ON DELETE CASCADE ON UPDATE CASCADE;

100. ON DELETE SET NULL

Keeps the child, but clears the reference.

ALTER TABLE funcionarios ADD CONSTRAINT fk_gerente
FOREIGN KEY (gerente_id) REFERENCES funcionarios (id) ON DELETE SET NULL;

101. ON DELETE RESTRICT

Prevents deleting the parent while there are children. It is usually the safest default.

ALTER TABLE pedidos ADD CONSTRAINT fk_pedidos_cliente
FOREIGN KEY (cliente_id) REFERENCES clientes (id) ON DELETE RESTRICT;

102. CHECK across several columns

Validates the coherence between fields of the same row.

ALTER TABLE reservas
ADD CONSTRAINT ck_periodo CHECK (fim > inicio);

103. CHECK with a list of values

A simple alternative to the ENUM type.

ALTER TABLE pedidos
ADD CONSTRAINT ck_status CHECK (status IN ('pendente','pago','cancelado'));

104. Composite UNIQUE

The combination has to be unique, not each column on its own.

ALTER TABLE inscricoes
ADD CONSTRAINT uk_aluno_turma UNIQUE (aluno_id, turma_id);

105. Partial UNIQUE

Uniqueness only for a subset — one active record per customer, for instance.

CREATE UNIQUE INDEX uq_assinatura_ativa ON assinaturas (cliente_id) WHERE ativa;

106. Disable and re-enable

On large loads, disabling temporarily speeds things up — but revalidate afterwards.

ALTER TABLE pedidos DROP CONSTRAINT fk_pedidos_cliente;
-- load
ALTER TABLE pedidos ADD CONSTRAINT fk_pedidos_cliente
FOREIGN KEY (cliente_id) REFERENCES clientes (id);

107. Deferred constraint

DEFERRABLE postpones the check to the COMMIT — it solves circular inserts.

ALTER TABLE a ADD CONSTRAINT fk_a_b FOREIGN KEY (b_id) REFERENCES b (id)
DEFERRABLE INITIALLY DEFERRED;

108. Find the orphans

Before creating the FK, find the rows that would break the rule.

SELECT ped.*
FROM pedidos ped
LEFT JOIN clientes cli
ON cli.id = ped.cliente_id
WHERE cli.id IS NULL;

109. ENUM

A type with a fixed list of values. PostgreSQL and MySQL have it natively; on the others, use a CHECK.

CREATE TYPE status_pedido AS ENUM ('pendente','pago','cancelado');
ALTER TABLE pedidos ALTER COLUMN status TYPE status_pedido USING status::status_pedido;

Window functions: the essentials

They aggregate without collapsing the rows. Once you understand them, you cannot live without them.

110. OVER() — the window

Calculates over a set of rows, but keeps every row in the result.

SELECT nome, total, SUM(total) OVER () AS total_geral FROM pedidos;

111. PARTITION BY

Divides into groups and calculates within each one, with no GROUP BY.

SELECT cliente_id, total,
       SUM(total) OVER (PARTITION BY cliente_id) AS total_cliente
FROM pedidos;

112. ROW_NUMBER

Numbers the rows within the partition. The basis for 'the most recent of each'.

SELECT ped.cliente_id,
       ped.criado_em,
       ROW_NUMBER() OVER (PARTITION BY ped.cliente_id ORDER BY ped.criado_em DESC) AS ordem
FROM pedidos ped;

113. Take the last of each group

Number them and filter by number 1 — the most used pattern with a window function.

SELECT *
FROM (
  SELECT ped.*,
         ROW_NUMBER() OVER (PARTITION BY ped.cliente_id ORDER BY ped.criado_em DESC) AS ordem
  FROM pedidos ped
) ped_num
WHERE ordem = 1;

114. RANK

Ranking with ties: two first places skip the second.

SELECT nome, total, RANK() OVER (ORDER BY total DESC) AS posicao FROM vendedores;

115. DENSE_RANK

The same as RANK, but without skipping positions after a tie.

SELECT nome, total, DENSE_RANK() OVER (ORDER BY total DESC) AS posicao FROM vendedores;

116. NTILE

Divides the rows into N bands of similar size — quartiles, deciles.

SELECT nome, total, NTILE(4) OVER (ORDER BY total) AS quartil FROM clientes;

117. LAG — the previous row

Brings the previous row's value. A comparison against last month comes out in one line.

SELECT mes, total, LAG(total) OVER (ORDER BY mes) AS mes_anterior FROM faturamento;

118. LEAD — the next row

The same idea, looking forwards.

SELECT mes, total, LEAD(total) OVER (ORDER BY mes) AS proximo FROM faturamento;

119. Percentage variation

LAG + arithmetic solves the classic 'how much did it grow?'.

SELECT mes, total,
  ROUND(100.0 * (total - LAG(total) OVER (ORDER BY mes)) / NULLIF(LAG(total) OVER (ORDER BY mes), 0), 1) AS variacao
FROM faturamento;

120. Running total

A progressive sum along the ordering.

SELECT dia, valor,
       SUM(valor) OVER (ORDER BY dia ROWS UNBOUNDED PRECEDING) AS acumulado
FROM caixa;

121. Moving average

The average of the last N rows — it smooths the chart's series.

SELECT dia, valor,
  AVG(valor) OVER (ORDER BY dia ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS media_7d
FROM metricas;

122. FIRST_VALUE and LAST_VALUE

The window's first and last value. Careful: LAST_VALUE needs an explicit frame.

SELECT cliente_id, criado_em,
  FIRST_VALUE(total) OVER (PARTITION BY cliente_id ORDER BY criado_em) AS primeiro
FROM pedidos;

123. Named WINDOW

Defined the same window three times? Name it once and reuse it.

SELECT cliente_id,
       SUM(total) OVER w AS soma,
       AVG(total) OVER w AS media
FROM pedidos
WINDOW w AS (PARTITION BY cliente_id);

124. Window vs GROUP BY

GROUP BY reduces the number of rows; the window preserves them. Use the window when you need the detail and the total together.

SELECT cliente_id, total,
       ROUND(100.0 * total / SUM(total) OVER (PARTITION BY cliente_id), 1) AS pct_do_cliente
FROM pedidos;

JSON inside the database

Semi-structured fields without giving up SQL — as long as they are used with judgement.

125. JSON and JSONB columns

On PostgreSQL, JSONB is binary, indexable and faster to query. Prefer JSONB.

CREATE TABLE eventos (id BIGSERIAL PRIMARY KEY, payload JSONB NOT NULL);

126. Insert JSON

Pass the document as text: the database validates the syntax.

INSERT INTO eventos (payload) VALUES ('{"tipo":"compra","valor":199.9}');

127. Read a field

-> returns JSON; ->> returns text (which is what you want for comparing).

SELECT payload->>'tipo' AS tipo, (payload->>'valor')::numeric AS valor FROM eventos;

128. Nested path

#>> navigates several levels at once.

SELECT payload#>>'{cliente,email}' AS email FROM eventos;

129. Filter by a JSON field

It works like any WHERE — but index it if it is a frequent query.

SELECT * FROM eventos WHERE payload->>'tipo' = 'compra';

130. The containment operator

@> asks whether the JSON contains that fragment. It is what the GIN index accelerates.

SELECT * FROM eventos WHERE payload @> '{"tipo":"compra"}';

131. Check whether a key exists

? tests whether the key exists in the document.

SELECT * FROM eventos WHERE payload ? 'cupom';

132. Index JSONB

Without an index, filtering JSON scans the whole table.

CREATE INDEX idx_eventos_payload ON eventos USING GIN (payload);

133. Index on a specific field

Leaner than indexing the whole document, when you always filter on the same field.

CREATE INDEX idx_eventos_tipo ON eventos ((payload->>'tipo'));

134. Update a field

jsonb_set swaps a value without rewriting the document in the application.

UPDATE eventos SET payload = jsonb_set(payload, '{status}', '"processado"') WHERE id = 1;

135. Remove a key

The - operator returns the document without the key.

UPDATE eventos SET payload = payload - 'temporario' WHERE id = 1;

136. Expand an array into rows

Turns a JSON array into rows so you can handle it with normal SQL.

SELECT eve.id, item
FROM eventos eve, jsonb_array_elements(eve.payload->'itens') AS item;

137. Build JSON in the SELECT

Returns the result already in the format the API needs.

SELECT jsonb_build_object('id', id, 'nome', nome, 'cidade', cidade) FROM clientes;

138. When NOT to use JSON

If the field is queried, filtered and joined on all the time, it deserves to be a real column.

-- Bad: the price inside the JSON, used in every report
-- Good: a preco DECIMAL(12,2) column + an index

Programming inside the database

Procedures, functions and triggers: logic running close to the data.

139. CREATE FUNCTION

A function that takes parameters and returns a value, usable inside a SELECT.

CREATE FUNCTION total_do_cliente(p_id INTEGER) RETURNS NUMERIC AS $$
  SELECT COALESCE(SUM(total), 0) FROM pedidos WHERE cliente_id = p_id;
$$ LANGUAGE SQL;

140. Use the function

Call it like any built-in function.

SELECT nome, total_do_cliente(id) AS total FROM clientes;

141. A function in PL/pgSQL

For logic with variables, loops and conditionals.

CREATE FUNCTION reajuste(p_preco NUMERIC, p_pct NUMERIC) RETURNS NUMERIC AS $$
BEGIN
  IF p_pct > 50 THEN RAISE EXCEPTION 'Reajuste acima do permitido';
  END IF;
  RETURN ROUND(p_preco * (1 + p_pct / 100), 2);
END;
$$ LANGUAGE plpgsql;

142. CREATE PROCEDURE

A procedure performs actions (and can control the transaction); it does not return a value like a function.

CREATE PROCEDURE limpar_sessoes() LANGUAGE SQL AS $$
  DELETE FROM sessoes WHERE criado_em < CURRENT_DATE - INTERVAL '90 days';
$$;

143. CALL

Runs the procedure.

CALL limpar_sessoes();

144. Output parameters

Procedures can return values through INOUT parameters.

CREATE PROCEDURE contar(INOUT total INTEGER) LANGUAGE plpgsql AS $$
BEGIN
  SELECT COUNT(*) INTO total FROM clientes;
END; $$;

145. Variables and SELECT INTO

Stores a query's result in a variable.

DECLARE v_total NUMERIC;
SELECT SUM(total) INTO v_total FROM pedidos;

146. IF / ELSE

A conditional inside the database code.

IF v_total > 1000 THEN
  RAISE NOTICE 'Meta batida';
ELSE
  RAISE NOTICE 'Faltam %', 1000 - v_total;
END IF;

147. LOOP and FOR

Loops to process row by row — use them only when you cannot solve it as a set.

FOR ped IN SELECT id FROM pedidos WHERE status = 'pendente' LOOP
  UPDATE pedidos SET status = 'processando' WHERE id = ped.id;
END LOOP;

148. RAISE — messages and errors

Logs a warning or stops with an exception.

RAISE NOTICE 'Processando cliente %', v_id;
RAISE EXCEPTION 'Saldo insuficiente para o cliente %', v_id;

149. Exception handling

Catches the error and decides what to do.

BEGIN
  INSERT INTO clientes (email) VALUES ('duplicado@email.com');
EXCEPTION WHEN unique_violation THEN
  RAISE NOTICE 'E-mail já cadastrado';
END;

150. CREATE TRIGGER

Code fired automatically by an INSERT, UPDATE or DELETE.

CREATE TRIGGER trg_atualiza_data
BEFORE UPDATE ON produtos
FOR EACH ROW EXECUTE FUNCTION set_atualizado_em();

151. Trigger function

On PostgreSQL, the trigger calls a function that returns TRIGGER.

CREATE FUNCTION set_atualizado_em() RETURNS TRIGGER AS $$
BEGIN
  NEW.atualizado_em := CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

152. BEFORE vs AFTER

BEFORE can change the row before it is written; AFTER is for side effects (logging, queuing).

CREATE TRIGGER trg_log AFTER INSERT ON pedidos
FOR EACH ROW EXECUTE FUNCTION registrar_log();

153. NEW and OLD

Inside the trigger, NEW is the new row and OLD is the old one.

IF NEW.preco <> OLD.preco THEN
  INSERT INTO historico_precos (produto_id, de, para) VALUES (OLD.id, OLD.preco, NEW.preco);
END IF;

154. Audit trigger

Stores who changed what and when, without depending on the application.

CREATE FUNCTION auditar() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO auditoria (tabela, operacao, dados, quando)
  VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(NEW), CURRENT_TIMESTAMP);
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

155. Disable a trigger

On large loads, switch it off temporarily — and remember to switch it back on.

ALTER TABLE pedidos DISABLE TRIGGER trg_log;
-- load
ALTER TABLE pedidos ENABLE TRIGGER trg_log;

156. DROP FUNCTION / TRIGGER

Remove what is no longer used: dead code in the database is worse than in the application.

DROP TRIGGER IF EXISTS trg_log ON pedidos;
DROP FUNCTION IF EXISTS registrar_log();

157. Careful with triggers

A trigger is invisible to whoever reads the application. Use it for integrity and auditing, not for complex business rules.

-- Good: filling in atualizado_em, auditing
-- Bad: calculating a commission, sending an email, calling an API

158. Deterministic function

Mark it as IMMUTABLE when the result depends only on the parameters: it allows indexing and caching.

CREATE FUNCTION slug(t TEXT) RETURNS TEXT AS $$
  SELECT LOWER(REPLACE(t, ' ', '-'));
$$ LANGUAGE SQL IMMUTABLE;

Modelling and normalisation

Model decisions you make in an afternoon and live with for years.

159. 1NF — first normal form

No lists inside a column: each field stores a single value.

-- Bad: telefones = '1199..., 1198...'
CREATE TABLE telefones (cliente_id INT, numero VARCHAR(20));

160. 2NF — second normal form

No column depends on only part of the composite key.

-- The product's name does not depend on the order: it leaves the items table
CREATE TABLE itens (pedido_id INT, produto_id INT, qtd INT, PRIMARY KEY (pedido_id, produto_id));

161. 3NF — third normal form

A column cannot depend on another ordinary column, only on the key.

-- The city and state depend on the postcode, not on the customer
CREATE TABLE ceps (cep CHAR(8) PRIMARY KEY, cidade VARCHAR(80), estado CHAR(2));

162. Denormalise on purpose

Duplicating data to speed up reads is valid — as long as it is a conscious decision with a synchronisation routine.

ALTER TABLE pedidos ADD COLUMN cliente_nome VARCHAR(120);  -- historical snapshot

163. One-to-many

The foreign key lives on the 'many' side.

CREATE TABLE pedidos (
  id SERIAL PRIMARY KEY,
  cliente_id INTEGER NOT NULL REFERENCES clientes (id)
);

164. Many-to-many

It needs a link table with both keys.

CREATE TABLE aluno_curso (
  aluno_id INT REFERENCES alunos (id),
  curso_id INT REFERENCES cursos (id),
  PRIMARY KEY (aluno_id, curso_id)
);

165. One-to-one

Split optional or sensitive data into another table with the same key.

CREATE TABLE cliente_documentos (
  cliente_id INT PRIMARY KEY REFERENCES clientes (id),
  cpf VARCHAR(14)
);

166. Natural vs surrogate key

A surrogate key (a sequential id) is stable; a natural key (a tax ID, an email) changes more than you think.

CREATE TABLE clientes (
  id BIGSERIAL PRIMARY KEY,      -- surrogate
  cpf VARCHAR(14) UNIQUE         -- natural, with guaranteed uniqueness
);

167. Soft delete

Marking instead of deleting preserves history — but it requires filtering in every query.

ALTER TABLE clientes ADD COLUMN excluido_em TIMESTAMP;
SELECT * FROM clientes WHERE excluido_em IS NULL;

168. History table

Stores the state over time, instead of overwriting it.

CREATE TABLE precos_historico (
  produto_id INT, preco DECIMAL(12,2),
  inicio DATE NOT NULL, fim DATE
);

169. A naming convention

Pick a convention (snake_case, singular or plural) and stick to it. Consistency is worth more than the choice itself.

-- tables in the plural, columns in snake_case, FKs as <table>_id
CREATE TABLE pedidos (id SERIAL PRIMARY KEY, cliente_id INT);

170. Coherent types across tables

The FK and the PK need the same type, otherwise the JOIN converts and loses the index.

-- clientes.id BIGINT  ->  pedidos.cliente_id BIGINT (not INTEGER)

Loading and moving data

Importing, exporting and moving volume without bringing the database down.

171. COPY — import a CSV

PostgreSQL's native bulk load, orders of magnitude faster than a row-by-row INSERT.

COPY clientes (nome, email) FROM '/tmp/clientes.csv' WITH (FORMAT csv, HEADER true);

172. COPY TO — export

The same route, in the other direction.

COPY (SELECT * FROM pedidos WHERE status = 'pago') TO '/tmp/pagos.csv' WITH (FORMAT csv, HEADER true);

173. \copy on the client

A psql variant that reads/writes on the client machine, with no need for access to the server's disk.

\copy clientes FROM 'clientes.csv' WITH (FORMAT csv, HEADER true)

174. LOAD DATA INFILE — MySQL

MySQL's equivalent of COPY.

LOAD DATA INFILE '/tmp/clientes.csv'
INTO TABLE clientes FIELDS TERMINATED BY ',' IGNORE 1 LINES;

175. BULK INSERT — SQL Server

Bulk loading on SQL Server.

BULK INSERT clientes FROM 'C:\dados\clientes.csv'
WITH (FIRSTROW = 2, FIELDTERMINATOR = ',');

176. Staging table

Import raw into a temporary table, validate, and only then move it to the final table.

CREATE TABLE stg_clientes (nome TEXT, email TEXT);
-- COPY into stg_clientes
INSERT INTO clientes (nome, email)
SELECT nome, LOWER(email) FROM stg_clientes WHERE email LIKE '%@%';

177. Deduplicate during the load

DISTINCT ON (PostgreSQL) or ROW_NUMBER handle duplicates from the source file.

INSERT INTO clientes (email, nome)
SELECT DISTINCT ON (email) email, nome FROM stg_clientes ORDER BY email, nome;

178. Update in batches

An UPDATE of millions of rows at once locks the table. Split it into blocks.

UPDATE pedidos SET processado = true
WHERE id IN (SELECT id FROM pedidos WHERE NOT processado LIMIT 10000);

179. Temporary table

It exists only in the session. Ideal for intermediate ETL steps.

CREATE TEMP TABLE tmp_resultado AS
SELECT cliente_id, SUM(total) AS total FROM pedidos GROUP BY cliente_id;

180. Generate test data

Synthetic data to measure performance at a realistic volume.

INSERT INTO pedidos (cliente_id, total, criado_em)
SELECT (random()*1000)::int, (random()*500)::numeric(12,2),
       CURRENT_DATE - (random()*365)::int
FROM generate_series(1, 100000);

Users and permissions

The least privilege possible: the application does not need to own the database.

181. CREATE USER

Creates a user/role with a password.

CREATE USER app_web WITH PASSWORD 'senha-forte-aqui';

182. GRANT on a table

Grants only the necessary operations.

GRANT SELECT, INSERT, UPDATE ON pedidos TO app_web;

183. Read-only GRANT

The classic profile for BI and reporting.

GRANT CONNECT ON DATABASE loja TO bi_leitura;
GRANT USAGE ON SCHEMA public TO bi_leitura;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO bi_leitura;

184. REVOKE

Removes a granted permission.

REVOKE DELETE ON pedidos FROM app_web;

185. Roles and inheritance

Group permissions into a role and grant the role to the users.

CREATE ROLE leitura;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO leitura;
GRANT leitura TO analista1, analista2;

186. Default permission for future tables

Without it, every new table needs a manual GRANT.

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO leitura;

187. Column-level permission

You can release only some of the table's columns.

GRANT SELECT (id, nome, cidade) ON clientes TO app_web;

188. Row Level Security

Each user sees only their own rows — multi-tenancy guaranteed by the database.

ALTER TABLE pedidos ENABLE ROW LEVEL SECURITY;
CREATE POLICY p_tenant ON pedidos
  USING (tenant_id = current_setting('app.tenant')::int);

189. See the permissions

A quick audit of who can do what.

SELECT grantee, privilege_type FROM information_schema.role_table_grants
WHERE table_name = 'pedidos';

190. Never use the superuser in the application

The application's account should not be able to create/drop tables or read data from other schemas.

-- Wrong: a DATABASE_URL with the postgres user
-- Right: a dedicated user, with a minimum GRANT

Metadata and maintenance

Asking the database about itself — and keeping it healthy.

191. List the tables

The standard information_schema catalogue works on most databases.

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

192. List the columns

The type, nullability and default of each column.

SELECT column_name, data_type, is_nullable
FROM information_schema.columns WHERE table_name = 'pedidos';

193. See a table's definition

Client shortcuts that save you querying the catalogue.

\d pedidos            -- psql
SHOW CREATE TABLE pedidos;   -- MySQL
sp_help 'pedidos';           -- SQL Server

194. The size of the tables

Finds out who is taking up the disk.

SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS tamanho
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

195. Estimated row count

COUNT(*) on a giant table is expensive; the catalogue's estimate is instantaneous.

SELECT reltuples::bigint AS estimativa FROM pg_class WHERE relname = 'pedidos';

196. ANALYZE — update the statistics

The optimiser decides the plan based on them. Stale statistics produce a bad plan.

ANALYZE pedidos;

197. VACUUM

Recovers the space of removed rows on PostgreSQL (MVCC).

VACUUM ANALYZE pedidos;

198. Active connections

See who is connected and what is running.

SELECT pid, usename, state, query FROM pg_stat_activity WHERE state <> 'idle';

199. End a stuck query

Cancels (or kills) a session that is holding the database.

SELECT pg_cancel_backend(12345);    -- asks it to cancel
SELECT pg_terminate_backend(12345); -- ends the connection

200. The slowest queries

The pg_stat_statements extension shows where the time is really going.

SELECT query, calls, mean_exec_time
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

Jhonatan Pinheiro

Loading page...

Intermediate SQL: 200 Commands and Techniques

Intermediate SQL: 200 Commands and Techniques

Contents

JOINs: combining tables

1. INNER JOIN

2. LEFT JOIN

3. RIGHT JOIN

4. FULL OUTER JOIN

5. CROSS JOIN

6. SELF JOIN

7. JOIN with several conditions

8. JOIN of three or more tables

9. USING — the ON shortcut

10. NATURAL JOIN

11. Anti-join with LEFT + IS NULL

12. Anti-join with NOT EXISTS

13. Semi-join with EXISTS

14. A filter in the ON vs in the WHERE

15. JOIN with aggregation

16. LATERAL JOIN

17. APPLY — SQL Server

18. JOIN through a link table

19. Careful with duplication

20. JOIN on a date range

Subqueries

21. Scalar subquery

22. Subquery in the WHERE

23. Subquery in the FROM

24. Correlated subquery

25. EXISTS vs IN

26. NOT IN with NULL

27. ANY / SOME

28. ALL

29. Subquery in the SELECT vs a JOIN

30. Subquery with LIMIT

31. Subquery in an UPDATE

32. Subquery in a DELETE

33. Subquery in the HAVING

34. Row subquery

35. When to swap it for a CTE

CTEs: organising queries

36. WITH — a simple CTE

37. Several CTEs

38. A CTE referencing another

39. CTE in an UPDATE and a DELETE

40. Recursive CTE

41. A date series with a recursive CTE

42. Careful with infinite recursion

43. Materialised CTE

44. CTE vs subquery

45. CTE with literal data

Set operations

46. UNION

47. UNION ALL

48. INTERSECT

49. EXCEPT / MINUS

50. Compatibility rules

51. ORDER BY on a union

52. Simulate a FULL JOIN on MySQL

53. Compare two tables

Views

54. CREATE VIEW

55. Query a view

56. CREATE OR REPLACE VIEW

57. DROP VIEW

58. A view to simplify a report

59. A view as a security layer

60. Updatable view

61. WITH CHECK OPTION

62. Materialised view

63. REFRESH MATERIALIZED VIEW

Indexes

64. CREATE INDEX

65. Composite index

66. The leftmost-prefix rule

67. Unique index

68. Partial index

69. Expression index

70. Descending index

71. Covering index