SQL Master Cheatsheet

📝 SELECT Statements - Basis

Basic SELECT

Selecteer alle kolommen van een tabel:

SELECT * FROM customers;

Selecteer specifieke kolommen:

SELECT customer_id, first_name, email 
FROM customers;

DISTINCT & LIMIT

Unieke waarden selecteren:

SELECT DISTINCT country 
FROM customers;

Resultaten beperken:

-- PostgreSQL, MySQL, Snowflake
SELECT * FROM orders 
LIMIT 10;

-- SQL Server
SELECT TOP 10 * FROM orders;

Aliases & Calculations

Kolom aliases:

SELECT 
  first_name AS voornaam,
  last_name AS achternaam,
  email AS email_adres
FROM customers;

Berekeningen:

SELECT 
  product_name,
  price,
  quantity,
  price * quantity AS totaal_bedrag
FROM order_items;

🔍 WHERE Clause - Filteren

Operator	Beschrijving	Voorbeeld
=	Gelijk aan	WHERE country = 'Nederland'
!= of <>	Niet gelijk aan	WHERE status != 'cancelled'
>, <, >=, <=	Vergelijkingen	WHERE amount > 1000
BETWEEN	Tussen waarden	WHERE date BETWEEN '2024-01-01' AND '2024-12-31'
LIKE	Patroon matching	WHERE email LIKE '%@gmail.com'
IN	In lijst van waarden	WHERE country IN ('NL', 'BE', 'DE')
IS NULL	NULL waarden	WHERE phone IS NULL
AND, OR, NOT	Logische operators	WHERE status = 'active' AND amount > 100

-- Complex WHERE voorbeeld SELECT * FROM orders WHERE (status = 'completed' OR status = 'shipped') AND order_date >= '2024-01-01' AND total_amount > 50 AND customer_id IS NOT NULL;

🔗 JOINs - Tabellen Combineren

INNER JOIN

Alleen matching rijen van beide tabellen:

SELECT 
  o.order_id,
  o.order_date,
  c.first_name,
  c.last_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

LEFT JOIN

Alle rijen van eerste tabel + matching van tweede:

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

RIGHT JOIN & FULL JOIN

RIGHT JOIN: alle rijen van tweede tabel:

SELECT * 
FROM table_a a
RIGHT JOIN table_b b ON a.id = b.id;

FULL JOIN: alle rijen van beide tabellen:

-- PostgreSQL, SQL Server, Snowflake
SELECT * 
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.id;

SELF JOIN & CROSS JOIN

Tabel met zichzelf joinen:

SELECT 
  e1.name AS employee,
  e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

Cartesian product:

SELECT * 
FROM colors
CROSS JOIN sizes;

📊 GROUP BY & Aggregatie Functies

Aggregatie Functies

-- Basis aggregatie functies
SELECT 
  COUNT(*) AS total_orders,
  SUM(amount) AS total_amount,
  AVG(amount) AS avg_amount,
  MIN(amount) AS min_amount,
  MAX(amount) AS max_amount,
  STDDEV(amount) AS std_amount
FROM orders;

GROUP BY Basis

Groeperen op kolommen:

SELECT 
  country,
  COUNT(*) AS customer_count,
  AVG(order_value) AS avg_order_value
FROM customers
GROUP BY country
ORDER BY customer_count DESC;

HAVING Clause

Filteren op geaggregeerde waarden:

SELECT 
  department_id,
  COUNT(*) AS employee_count,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5
  AND AVG(salary) > 50000;

🧮 Window Functions - Geavanceerd

💡 Window Functions voeren berekeningen uit over een set rijen die gerelateerd zijn aan de huidige rij, zonder de resultaten te groeperen.

Functie	Beschrijving	Voorbeeld
ROW_NUMBER()	Uniek nummer voor elke rij	ROW_NUMBER() OVER (ORDER BY sales DESC)
RANK()	Rang met gaten bij gelijke waarden	RANK() OVER (PARTITION BY dept ORDER BY salary DESC)
DENSE_RANK()	Rang zonder gaten	DENSE_RANK() OVER (ORDER BY score)
NTILE(n)	Verdeelt rijen in n groepen	NTILE(4) OVER (ORDER BY revenue) AS quartile
LAG() / LEAD()	Toegang tot vorige/volgende rij	LAG(price, 1) OVER (ORDER BY date)
FIRST_VALUE() / LAST_VALUE()	Eerste/laatste waarde in window	FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY date)
SUM() OVER()	Running total	SUM(amount) OVER (ORDER BY date)
AVG() OVER()	Moving average	AVG(price) OVER (ORDER BY date ROWS 6 PRECEDING)

-- Complex window function voorbeeld SELECT employee_id, first_name, department, salary, AVG(salary) OVER(PARTITION BY department) AS dept_avg_salary, ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS salary_rank, LAG(salary, 1) OVER(PARTITION BY department ORDER BY hire_date) AS prev_salary, SUM(salary) OVER(PARTITION BY department ORDER BY hire_date) AS running_total FROM employees ORDER BY department, salary_rank;

🌀 CTEs & Subqueries

Common Table Expressions (CTE)

Herbruikbare subqueries:

WITH monthly_sales AS (
  SELECT 
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY 1
),
yearly_summary AS (
  SELECT 
    EXTRACT(year FROM month) AS year,
    SUM(total_sales) AS yearly_total
  FROM monthly_sales
  GROUP BY 1
)
SELECT * FROM yearly_summary
ORDER BY year;

Recursive CTEs

Hiërarchische data verwerken:

WITH RECURSIVE org_hierarchy AS (
  -- Anchor member
  SELECT 
    employee_id,
    manager_id,
    first_name,
    1 AS level
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- Recursive member
  SELECT 
    e.employee_id,
    e.manager_id,
    e.first_name,
    oh.level + 1
  FROM employees e
  INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT * FROM org_hierarchy
ORDER BY level, employee_id;

Subqueries

Subquery in WHERE:

SELECT * 
FROM products
WHERE category_id IN (
  SELECT category_id 
  FROM categories 
  WHERE active = true
);

Subquery in SELECT:

SELECT 
  customer_id,
  first_name,
  (
    SELECT COUNT(*) 
    FROM orders o 
    WHERE o.customer_id = c.customer_id
  ) AS order_count
FROM customers c;

✏️ Data Manipulation (DML)

Statement	Syntaxis	Voorbeeld
INSERT	INSERT INTO table (columns) VALUES (values)	INSERT INTO users (name, email) VALUES ('Jan', 'jan@example.nl')
UPDATE	UPDATE table SET column = value WHERE condition	UPDATE products SET price = price * 1.1 WHERE category = 'electronics'
DELETE	DELETE FROM table WHERE condition	DELETE FROM logs WHERE created_at < '2023-01-01'
UPSERT (MERGE)	MERGE INTO target USING source ON condition WHEN MATCHED/MOT MATCHED	MERGE INTO products t USING new_products s ON t.id = s.id WHEN MATCHED UPDATE SET t.price = s.price WHEN NOT MATCHED INSERT (id, name) VALUES (s.id, s.name)

⚡ SQL Performance Tips

Indexing

Creëer indexes voor snellere queries:

-- Basis index
CREATE INDEX idx_customers_email ON customers(email);

-- Composite index
CREATE INDEX idx_orders_date_status 
ON orders(order_date, status);

-- Unique index
CREATE UNIQUE INDEX idx_users_username 
ON users(username);

Query Optimalisatie

1. Gebruik EXISTS i.p.v. IN voor subqueries

-- Beter
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM customers c 
  WHERE c.id = o.customer_id 
  AND c.country = 'NL'
);

2. Gebruik UNION ALL i.p.v. UNION als dubbele rijen geen probleem zijn

3. Vermijd SELECT * - specificeer alleen nodige kolommen

Explain Plans

Analyseer query performance:

-- PostgreSQL
EXPLAIN ANALYZE 
SELECT * FROM orders WHERE amount > 1000;

-- MySQL
EXPLAIN SELECT * FROM orders;

-- SQL Server
SET STATISTICS PROFILE ON;
SELECT * FROM orders;

🗄️ Database Specifieke Features

Database	Unieke Features	Voorbeeld
PostgreSQL	JSONB, ARRAY, RANGE types, Full-text search	SELECT * FROM products WHERE attributes @> '{"color": "red"}'
MySQL	GROUP_CONCAT, JSON functions, Window functions (8.0+)	SELECT GROUP_CONCAT(name SEPARATOR ', ') FROM products
SQL Server	STRING_AGG, OFFSET/FETCH, Temporal tables	SELECT STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name)
Snowflake	Zero-copy cloning, Time travel, Data sharing	CREATE DATABASE dev_clone CLONE production;
BigQuery	UNNEST, Standard SQL, ML in SQL	SELECT * FROM UNNEST([1,2,3,4,5]) AS numbers

📋 Snelle Referentie - Dagelijks Gebruik

-- Meest gebruikte query patronen -- 1. Data selecteren met filtering SELECT column1, column2 FROM table_name WHERE condition ORDER BY column1 DESC LIMIT 100; -- 2. Groeperen en aggregeren SELECT category, COUNT(*) AS count, AVG(price) AS avg_price FROM products GROUP BY category HAVING COUNT(*) > 10 ORDER BY avg_price DESC; -- 3. Joinen van meerdere tabellen SELECT o.order_id, c.first_name, p.product_name, oi.quantity FROM orders o JOIN customers c ON o.customer_id = c.customer_id JOIN order_items oi ON o.order_id = oi.order_id JOIN products p ON oi.product_id = p.product_id; -- 4. Window function voor ranking SELECT employee_id, first_name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank FROM employees;

Complete SQL Referentie voor Data Analysis & Engineering

Hoe te gebruiken: