WITH cte_name AS (
SELECT ...
)
SELECT * FROM cte_name;
example
WITH monthly_revenue AS (
SELECT
DATE_TRUNC('month', order_date) AS month,
SUM(total_amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
month,
revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month,
revenue - LAG(revenue) OVER (ORDER BY month) AS growth
FROM monthly_revenue
ORDER BY month;
output
-- Month-over-month revenue with growth calculation
Note CTEs improve readability by breaking complex queries into named steps. Multiple CTEs can be chained: WITH a AS (...), b AS (SELECT ... FROM a) SELECT ... FROM b. In PostgreSQL 12+, the optimizer can inline CTEs for better performance.
ctewith clausecommon table expressionnamed subquerywith as
Recursive CTE
syntax
WITH RECURSIVE cte AS (
-- base case
SELECT ... WHERE condition
UNION ALL
-- recursive step
SELECT ... FROM cte JOIN ...
)
SELECT * FROM cte;
example
WITH RECURSIVE org_chart AS (
-- Base: top-level managers (no manager)
SELECT id, first_name, manager_id, 1 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: employees who report to someone already in the result
SELECT e.id, e.first_name, e.manager_id, oc.depth + 1
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY depth, first_name;
output
-- id | first_name | manager_id | depth
-- 1 | Alice | NULL | 1
-- 2 | Bob | 1 | 2
-- 5 | Carol | 1 | 2
-- 3 | Dave | 2 | 3
Note Always include a depth counter and a LIMIT or WHERE condition in the outer query to prevent infinite loops from circular references. MySQL 8+ and PostgreSQL support WITH RECURSIVE. Add CYCLE detection in PostgreSQL 14+ with CYCLE clause.
CASE
WHEN condition THEN result
WHEN condition THEN result
ELSE default
END
example
SELECT
order_id,
total_amount,
CASE
WHEN total_amount >= 500 THEN 'premium'
WHEN total_amount >= 100 THEN 'standard'
ELSE 'small'
END AS order_tier
FROM orders;
Note CASE evaluates conditions top-to-bottom and returns the first match. If no condition matches and there is no ELSE, it returns NULL. CASE works in SELECT, WHERE, ORDER BY, and even inside aggregate functions.
case whenconditional logicif then elseswitch case sql
NULLIF
syntax
NULLIF(expression1, expression2)
example
SELECT
product_name,
revenue,
cost,
revenue / NULLIF(cost, 0) AS margin_ratio
FROM product_stats;
output
-- Returns NULL instead of division-by-zero error when cost is 0
Note NULLIF returns NULL if the two expressions are equal; otherwise returns the first expression. The most common use is preventing division by zero: x / NULLIF(y, 0) gives NULL instead of an error.
nullifprevent division by zeronull if equalsafe divide
Views
syntax
CREATE VIEW view_name AS
SELECT ...;
CREATE OR REPLACE VIEW view_name AS
SELECT ...;
example
CREATE VIEW active_order_summary AS
SELECT
u.id AS user_id,
u.first_name,
COUNT(o.order_id) AS order_count,
SUM(o.total_amount) AS lifetime_value
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status <> 'cancelled'
GROUP BY u.id, u.first_name;
-- Use like a table:
SELECT * FROM active_order_summary WHERE lifetime_value > 1000;
output
-- Encapsulates a complex query as a reusable virtual table
Note Views do not store data — they are saved queries that execute when referenced. Use materialized views (PostgreSQL) for caching expensive aggregations. Updating through views is limited to simple, single-table views.
BEGIN;
UPDATE accounts SET balance = balance - 200.00
WHERE account_id = 1001;
UPDATE accounts SET balance = balance + 200.00
WHERE account_id = 1002;
-- If both succeed:
COMMIT;
-- If something goes wrong:-- ROLLBACK;
output
-- Both updates happen atomically, or neither does
Note Transactions ensure atomicity — all statements succeed together or fail together. Keep transactions short to avoid holding locks. PostgreSQL supports SAVEPOINT for partial rollbacks within a transaction. MySQL auto-commits by default; use START TRANSACTION explicitly.
SELECT columns FROM table1
UNION [ALL]
SELECT columns FROM table2;
example
SELECT first_name, email, 'customer' AS source
FROM customers
UNION ALL
SELECT first_name, email, 'employee' AS source
FROM employees;
output
-- Combined list of customers and employees
Note UNION removes duplicates (slower, requires sorting). UNION ALL keeps all rows (faster). Use UNION ALL unless you specifically need deduplication. Both queries must have the same number of columns with compatible types.
SELECT columns FROM table1
INTERSECT
SELECT columns FROM table2;
SELECT columns FROM table1
EXCEPT
SELECT columns FROM table2;
example
-- Customers who are also employees
SELECT email FROM customers
INTERSECT
SELECT email FROM employees;
-- Customers who are NOT employees
SELECT email FROM customers
EXCEPT
SELECT email FROM employees;
output
-- INTERSECT: emails in both tables
-- EXCEPT: emails only in customers
Note INTERSECT returns rows in both result sets. EXCEPT returns rows in the first set but not the second. MySQL 8.0.31+ supports these; earlier versions do not. SQL Server calls EXCEPT what PostgreSQL calls EXCEPT — they are the same. MINUS is Oracle's synonym for EXCEPT.
intersectexceptminusset differencecommon rowsrows in both
CREATE MATERIALIZED VIEW product_sales_summary AS
SELECT
p.product_name,
SUM(oi.quantity) AS total_sold,
SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM products p
JOIN order_items oi ON oi.product_id = p.id
GROUP BY p.product_name;
-- Refresh when data changes:
REFRESH MATERIALIZED VIEW CONCURRENTLY product_sales_summary;
output
-- Pre-computed summary table; queries are instant
Note Materialized views store results physically, unlike regular views. They must be manually refreshed. CONCURRENTLY allows refreshing without locking reads but requires a unique index. MySQL does not support materialized views natively.
-- PostgreSQL
column->>'key'-- text
column->'key'-- JSON
column @> '{"key": "value"}'-- containment-- MySQL
JSON_EXTRACT(column, '$.key')
column->>'$.key'
example
-- PostgreSQL
SELECT
id,
metadata->>'name' AS name,
metadata->'address'->>'city' AS city
FROM products
WHERE metadata @> '{"category": "electronics"}';
output
-- id | name | city
-- 1 | Widget Pro | Seattle
Note Use JSONB (not JSON) in PostgreSQL for indexing and efficient querying. Create a GIN index on JSONB columns: CREATE INDEX idx_meta ON products USING GIN (metadata). MySQL JSON functions use dollar-sign path syntax: $.key.nested.
json queryjsonbjson extractquery json columnjson field
Temporary Tables
syntax
CREATE TEMPORARY TABLE temp_name (
columns...
);
-- or
CREATE TEMP TABLE temp_name AS
SELECT ...;
example
CREATE TEMP TABLE high_value_users AS
SELECT u.id, u.first_name, SUM(o.total_amount) AS total_spent
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.first_name
HAVING SUM(o.total_amount) > 5000;
-- Use in subsequent queries:
SELECT * FROM high_value_users ORDER BY total_spent DESC;
output
-- Temp table exists only for the current session
Note Temporary tables are automatically dropped at the end of the session (or transaction, if ON COMMIT DROP is specified). They are visible only to the creating session. Useful for breaking up complex multi-step queries.