SyntaxShelf
SQL

Advanced

SQL · 11 entries

CTE (Common Table Expression)

Syntax
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.

recursive ctehierarchy querytree traversalorg chartparent child recursive

CASE WHEN

Syntax
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;
Output
-- order_id | total_amount | order_tier
-- 101      | 750.00       | premium
-- 102      | 45.99        | small
-- 103      | 200.00       | standard

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.

create viewvirtual tablesaved queryview definition

Transactions

Syntax
BEGIN;
-- statements
COMMIT;
-- or
ROLLBACK;
Example
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.

transactionbegin commitrollbackatomic operationacid

UNION / UNION ALL

Syntax
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.

unioncombine queriesunion allmerge resultsappend rows

INTERSECT and EXCEPT

Syntax
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

Materialized View (PostgreSQL)

Syntax
CREATE MATERIALIZED VIEW view_name AS
SELECT ...;

REFRESH MATERIALIZED VIEW view_name;
Example
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.

materialized viewcached viewprecomputerefresh materialized

JSON Querying

Syntax
-- 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.

temp tabletemporary tablesession tablecreate temp