July 23, 20264 min read

Intermediate SQL: 20 Must-Know Techniques

JOINs, subqueries, VIEWs, transactions and more: 20 techniques that take you beyond basic SELECT.

sql
banco-de-dados
intermediario

Intermediate SQL: 20 Techniques Every Dev Should Master

Past the basics? These 20 techniques separate whoever "knows SELECT" from whoever really models and queries data: JOINs, subqueries, VIEWs, transactions and more — with ready-to-use examples.

1. INNER JOIN — joining tables

Combines rows from two tables where the condition matches. It brings back only what exists in both.

SELECT p.id, c.nome AS cliente, p.total
FROM pedidos p
INNER JOIN clientes c ON c.id = p.cliente_id;

2. LEFT / RIGHT JOIN

Brings all the rows from one side, even without a match (which becomes NULL).

-- Every customer, whether they have orders or not
SELECT c.nome, p.id AS pedido
FROM clientes c
LEFT JOIN pedidos p ON p.cliente_id = c.id;

3. FULL OUTER JOIN

Brings everything from both sides, matching where possible. (MySQL has no native one — use UNION.)

SELECT c.nome, p.id
FROM clientes c
FULL OUTER JOIN pedidos p ON p.cliente_id = c.id;

4. SELF JOIN — a table with itself

Useful for hierarchies (e.g. an employee and their manager).

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

5. HAVING — filtering groups

WHERE filters rows; HAVING filters groups (after the GROUP BY).

SELECT cliente_id, COUNT(*) AS qtd
FROM pedidos
GROUP BY cliente_id
HAVING COUNT(*) >= 5;

6. Subqueries

A query inside another one. Here, products above the average price.

SELECT nome, preco
FROM produtos
WHERE preco > (SELECT AVG(preco) FROM produtos);

7. EXISTS — is there a match?

Tests whether the subquery returns any row. Great for 'who has at least one...'.

SELECT c.nome
FROM clientes c
WHERE EXISTS (
  SELECT 1 FROM pedidos p WHERE p.cliente_id = c.id
);

8. UNION / UNION ALL

Stacks the results of two queries. UNION removes duplicates; UNION ALL does not (and is faster).

SELECT nome FROM clientes_br
UNION ALL
SELECT nome FROM clientes_pt;

9. CASE WHEN — conditional logic

An 'if/else' inside the SELECT. It creates derived columns.

SELECT nome, total,
  CASE
    WHEN total >= 1000 THEN 'VIP'
    WHEN total >= 200  THEN 'Frequente'
    ELSE 'Comum'
  END AS categoria
FROM pedidos;

10. COALESCE / NULLIF

COALESCE returns the first non-null value (great for defaults). NULLIF avoids division by zero.

SELECT nome, COALESCE(telefone, 'sem telefone') AS contato FROM clientes;

-- Avoids a division-by-zero error
SELECT total / NULLIF(qtd, 0) AS media FROM pedidos;

11. Foreign Key

Guarantees integrity: an order always points to a customer that exists.

CREATE TABLE pedidos (
  id BIGINT PRIMARY KEY,
  cliente_id INTEGER NOT NULL,
  total DECIMAL(10,2),
  FOREIGN KEY (cliente_id) REFERENCES clientes(id)
);

12. Indexes — CREATE INDEX

Speeds up searches on heavily filtered columns. It costs space and makes writes a little slower.

CREATE INDEX idx_pedidos_cliente ON pedidos(cliente_id);

-- Unique (prevents duplicates)
CREATE UNIQUE INDEX idx_clientes_email ON clientes(email);

13. Constraints — rules on the table

Let the database enforce the rules: NOT NULL, UNIQUE, CHECK, DEFAULT.

CREATE TABLE produtos (
  id INTEGER PRIMARY KEY,
  nome VARCHAR(120) NOT NULL,
  sku  VARCHAR(30) UNIQUE,
  preco DECIMAL(10,2) CHECK (preco >= 0),
  ativo BOOLEAN DEFAULT TRUE
);

14. Auto increment

IDs that grow on their own. The syntax changes per database.

-- PostgreSQL
id SERIAL PRIMARY KEY
-- or BIGSERIAL / GENERATED ALWAYS AS IDENTITY

-- MySQL
id INT AUTO_INCREMENT PRIMARY KEY

-- SQL Server
id INT IDENTITY(1,1) PRIMARY KEY

15. Date functions

Extract and calculate with dates. The syntax varies per database.

-- Now
SELECT CURRENT_TIMESTAMP;

-- Orders from the last 30 days (PostgreSQL)
SELECT * FROM pedidos
WHERE data >= CURRENT_DATE - INTERVAL '30 days';

-- Extract the year
SELECT EXTRACT(YEAR FROM data) AS ano FROM pedidos;

16. String functions

Manipulate text: concatenate, trim, uppercase, length.

SELECT
  UPPER(nome)                AS maiusculo,
  LENGTH(nome)               AS tamanho,
  SUBSTRING(nome, 1, 3)      AS iniciais,
  CONCAT(nome, ' <', email, '>') AS contato
FROM clientes;

17. CAST / CONVERT — converting types

Changes the type of a value (e.g. text to number).

SELECT CAST('2024-01-15' AS DATE)   AS data;
SELECT CAST(preco AS INTEGER)         AS preco_inteiro FROM produtos;

18. VIEWS — saved queries

A 'virtual table' based on a query. It simplifies and standardises access.

CREATE VIEW vw_faturamento_cidade AS
SELECT cidade, SUM(total) AS faturamento
FROM pedidos p
JOIN clientes c ON c.id = p.cliente_id
GROUP BY cidade;

SELECT * FROM vw_faturamento_cidade;

19. INSERT ... SELECT

Inserts the result of a query into another table. Great for copying or archiving data.

INSERT INTO clientes_arquivo (id, nome, email)
SELECT id, nome, email
FROM clientes
WHERE ultimo_acesso < '2022-01-01';

20. Transactions — all or nothing

Groups operations: either they all succeed (COMMIT), or none does (ROLLBACK). Essential when money is involved.

BEGIN;
  UPDATE contas SET saldo = saldo - 100 WHERE id = 1;
  UPDATE contas SET saldo = saldo + 100 WHERE id = 2;
COMMIT;
-- If something fails midway: ROLLBACK; undoes everything

Jhonatan Pinheiro

Loading page...