SQL — Enterprise Reference

SQL Command Categories

Core Data Types

NULL Semantics — The Three-Valued Logic

SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. Any comparison with NULL yields UNKNOWN, which is treated as FALSE in WHERE clauses. This causes common bugs.

-- These never return rows — comparisons with NULL are UNKNOWN
SELECT * FROM orders WHERE deleted_at = NULL;   -- WRONG
SELECT * FROM orders WHERE deleted_at != NULL;  -- WRONG

-- Correct NULL checks
SELECT * FROM orders WHERE deleted_at IS NULL;
SELECT * FROM orders WHERE deleted_at IS NOT NULL;

-- COALESCE returns first non-NULL value
SELECT COALESCE(phone, mobile, 'N/A') AS contact
FROM customers;

-- NULLIF returns NULL when both args are equal (avoids div-by-zero)
SELECT revenue / NULLIF(quantity, 0) AS unit_price
FROM sales;
SQL

Constraints

ALTER TABLE order_items
  ADD CONSTRAINT fk_order
  FOREIGN KEY (order_id)
  REFERENCES orders(id)
  ON DELETE CASCADE    -- auto-delete children
  ON UPDATE NO ACTION; -- block PK changes

-- Other options: SET NULL, SET DEFAULT, RESTRICT
SQL

Join Types

-- LEFT JOIN with NULL check to find missing relationships
SELECT c.id, c.name
FROM       customers  c
LEFT JOIN  orders     o  ON o.customer_id = c.id
WHERE o.id IS NULL;  -- customers with NO orders

-- Self join: employee + their manager
SELECT
    e.name  AS employee,
    m.name  AS manager
FROM       employees e
LEFT JOIN  employees m  ON m.id = e.manager_id;
SQL

Common Table Expressions (CTEs)

CTEs are named subqueries defined with WITH. They improve readability, allow self-reference (recursive CTEs), and in many databases are evaluated once and reused.

-- Non-recursive CTE: clean up a complex SELECT
WITH
  monthly_revenue AS (
    SELECT
      DATE_TRUNC('month', order_date) AS month,
      SUM(amount)                       AS revenue
    FROM  orders
    GROUP BY 1
  ),
  prev_month AS (
    SELECT
      month,
      revenue,
      LAG(revenue) OVER (ORDER BY month) AS prev_revenue
    FROM monthly_revenue
  )
SELECT
  month,
  revenue,
  ROUND((revenue - prev_revenue) * 100.0 / prev_revenue, 2) AS pct_change
FROM prev_month
ORDER BY month;
SQL

-- Recursive CTE: walk an org-chart hierarchy
WITH RECURSIVE org_tree AS (
  -- Anchor: start from the CEO
  SELECT id, name, manager_id, 0 AS depth
  FROM   employees
  WHERE  manager_id IS NULL

  UNION ALL

  -- Recursive step: each employee's reports
  SELECT e.id, e.name, e.manager_id, t.depth + 1
  FROM   employees  e
  JOIN   org_tree   t  ON t.id = e.manager_id
)
SELECT
  REPEAT('  ', depth) || name AS hierarchy
FROM  org_tree
ORDER BY depth, name;
SQL — PostgreSQL

Aggregate Functions

-- HAVING filters on the result of aggregation
-- WHERE filters before aggregation — do not confuse them
SELECT
    customer_id,
    COUNT(*)                                          AS order_count,
    SUM(amount)                                       AS total_spent,
    AVG(amount)                                       AS avg_order,
    MAX(order_date)                                   AS last_order,
    STRING_AGG(status, ', ' ORDER BY order_date)    AS status_history
FROM    orders
WHERE   order_date >= '2024-01-01'
GROUP BY customer_id
HAVING  SUM(amount) > 1000    -- only high-value customers
ORDER BY total_spent DESC;
SQL

-- ROLLUP: sales totals by region + country, with subtotals
SELECT
    COALESCE(region, 'ALL REGIONS') AS region,
    COALESCE(country, 'ALL')         AS country,
    SUM(revenue)                      AS total
FROM  sales
GROUP BY ROLLUP(region, country)
ORDER BY region, country;
SQL

Window functions compute a value across a set of rows related to the current row without collapsing them. Unlike GROUP BY, the original rows are preserved.

Ranking Functions

SELECT
    name,
    department,
    salary,
    ROW_NUMBER()  OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK()        OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    DENSE_RANK()  OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank,
    NTILE(4)      OVER (PARTITION BY department ORDER BY salary DESC) AS quartile
FROM  employees;

-- ROW_NUMBER: always unique (1,2,3,4,5)
-- RANK:       ties share same rank, gaps after (1,1,3,4,5)
-- DENSE_RANK: ties share same rank, no gaps   (1,1,2,3,4)
SQL

Offset & Frame Functions

SELECT
    order_date,
    revenue,
    -- Prior and next row values
    LAG(revenue, 1, 0)  OVER (ORDER BY order_date) AS prev_revenue,
    LEAD(revenue, 1, 0) OVER (ORDER BY order_date) AS next_revenue,
    -- First and last value in partition
    FIRST_VALUE(revenue) OVER (PARTITION BY customer_id ORDER BY order_date
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS first_order_value,
    -- 7-day rolling average
    AVG(revenue) OVER (ORDER BY order_date
                   ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d_avg,
    -- Running total
    SUM(revenue)  OVER (ORDER BY order_date
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM  daily_sales;
SQL

Practical Pattern — Top-N Per Group

-- Top 3 products by revenue, per category
WITH ranked AS (
    SELECT
        category,
        product_name,
        revenue,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn
    FROM  products
)
SELECT  category, product_name, revenue
FROM    ranked
WHERE   rn <= 3;
SQL

Index Internals

Most indexes are B-Tree structures. Each leaf page contains the indexed value(s) plus a pointer to the actual row. Lookups are O(log n); range scans walk leaf pages in order.

Creating Effective Indexes

-- Basic index
CREATE INDEX idx_orders_customer
    ON orders (customer_id);

-- Composite index — column order matters!
-- Best for: WHERE customer_id = ? AND status = ?
--       or: WHERE customer_id = ? (prefix match)
CREATE INDEX idx_orders_cust_status
    ON orders (customer_id, status);

-- Covering index: avoids going back to base table
CREATE INDEX idx_orders_covering
    ON orders (customer_id, order_date)
    INCLUDE   (amount, status);  -- SQL Server / PostgreSQL

-- Partial index (PostgreSQL): index only active orders
CREATE INDEX idx_active_orders
    ON orders (customer_id)
    WHERE status = 'active';

-- Filtered index (SQL Server equivalent)
CREATE INDEX idx_active_orders
    ON orders (customer_id)
    WHERE status = 'active';  -- SQL Server 2008+
SQL

The Selectivity & SARGability Rules

ACID Properties

Isolation Levels & Read Phenomena

-- Setting isolation level for a session
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 500
    WHERE  id = 1;

    UPDATE accounts SET balance = balance + 500
    WHERE  id = 2;

    -- Only commits if BOTH updates succeed
COMMIT;
SQL Server

Locking & Deadlocks

Reading Execution Plans

The query optimiser generates a plan — a tree of physical operators. Read the plan right-to-left, bottom-up. Arrows between operators show row flow; width indicates estimated rows.

-- SQL Server: show estimated plan
SET SHOWPLAN_XML ON;

-- SQL Server: capture actual plan + stats
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- PostgreSQL: full actual execution details
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE customer_id = 42;
SQL

Common Optimisation Patterns

-- 1. Avoid SELECT * in production — column projection reduces I/O
SELECT id, name, email  -- not SELECT *
FROM   customers
WHERE  status = 'active';

-- 2. EXISTS is faster than IN for correlated subqueries
--    (stops as soon as first match is found)
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
      AND o.amount > 1000
);

-- 3. Avoid row-by-row CURSOR operations — use set-based UPDATE
UPDATE orders
SET    status = 'expired'
WHERE  order_date < DATEADD(day, -90, GETDATE())
  AND  status     = 'pending';

-- 4. Batch large deletes to avoid log bloat & long lock holds
WHILE 1 = 1
BEGIN
    DELETE TOP (5000) FROM audit_log
    WHERE created_at < '2023-01-01';
    IF @@ROWCOUNT = 0 BREAK;
END;
SQL Server

Stored Procedures vs Functions

-- Inline Table-Valued Function (fast — inlined by optimiser)
CREATE FUNCTION dbo.GetOrdersByCustomer
    (@customer_id INT, @start_date DATE)
RETURNS TABLE
AS
RETURN (
    SELECT id, order_date, amount, status
    FROM   orders
    WHERE  customer_id = @customer_id
      AND  order_date  >= @start_date
);
GO

-- Usage: joins neatly with other tables
SELECT c.name, o.amount
FROM   customers c
CROSS APPLY dbo.GetOrdersByCustomer(c.id, '2024-01-01') o;
SQL Server

Views & Materialised Views

Triggers — Use Sparingly

-- AFTER INSERT trigger: maintain an audit log
CREATE TRIGGER trg_orders_audit
ON orders
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO orders_audit (order_id, action, actioned_at)
    SELECT id, 'INSERT', GETUTCDATE()
    FROM   INSERTED;  -- INSERTED is a virtual table of new rows
END;
SQL Server

Normal Forms

When to Denormalise

Normalisation is a correctness tool, not an absolute law. Denormalise deliberately when:

Surrogate vs Natural Keys

Table Partitioning

Partitioning splits a large table into smaller physical segments while keeping a single logical table. The most common strategy is range partitioning on date — each month or year lives on its own partition.

Temporal Tables (System-Versioned)

SQL Server 2016+ and PostgreSQL (via extensions) support temporal tables — the database automatically tracks the full history of every row change. No manual audit log needed.

-- Create a system-versioned temporal table
CREATE TABLE products (
    id           INT              PRIMARY KEY,
    name         NVARCHAR(200)   NOT NULL,
    price        DECIMAL(10,2)   NOT NULL,
    valid_from   DATETIME2        GENERATED ALWAYS AS ROW START,
    valid_to     DATETIME2        GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.products_history));

-- Query the state of a row at a point in time
SELECT * FROM products
FOR SYSTEM_TIME AS OF '2024-06-01'
WHERE id = 42;
SQL Server

JSON in SQL Server & PostgreSQL

-- SQL Server: extract from JSON column
SELECT
    id,
    JSON_VALUE(metadata, '$.shipping.city')   AS city,
    JSON_VALUE(metadata, '$.shipping.country') AS country
FROM  orders
WHERE JSON_VALUE(metadata, '$.priority') = 'high';

-- PostgreSQL JSONB: index a specific path
CREATE INDEX idx_metadata_priority
    ON orders ((metadata ->> 'priority'));

SELECT *
FROM  orders
WHERE metadata ->> 'priority' = 'high';  -- uses the index
SQL

High Availability & Replication Patterns

Upsert Patterns

-- SQL Server: MERGE statement (insert or update)
MERGE INTO products         AS target
USING      product_updates  AS source
    ON (target.sku = source.sku)
WHEN MATCHED THEN
    UPDATE SET price = source.price, name = source.name
WHEN NOT MATCHED THEN
    INSERT (sku, name, price) VALUES (source.sku, source.name, source.price)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;  -- remove products no longer in source

-- PostgreSQL: INSERT ... ON CONFLICT (cleaner for simple upserts)
INSERT INTO products (sku, name, price)
VALUES ('ABC-1', 'Widget', 9.99)
ON CONFLICT (sku)
DO UPDATE SET
    name  = EXCLUDED.name,
    price = EXCLUDED.price;
SQL

Performance Checklist for Enterprise Schemas