SQL – Query Formatting & Style

SQL is code — it deserves the same readability standards as any other programming language. Well-formatted SQL is scannable at a glance, easy to diff in code review, and immediately communicates the query's structure.

Keywords — Uppercase

All SQL keywords and reserved words must be UPPERCASE. All identifiers (table names, column names, aliases) must be lowercase. This distinction makes the structure of a query immediately visible.

-- ✅ Keywords uppercase, identifiers lowercase
SELECT
    o.id,
    o.total_amount,
    u.email        AS user_email
FROM   orders o
JOIN   users  u ON u.id = o.user_id
WHERE  o.order_status = 'confirmed'
  AND  o.created_at  >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

-- ❌ Lowercase keywords — hard to distinguish structure from data
select o.id, o.total_amount from orders o
join users u on u.id = o.user_id where o.order_status = 'confirmed';

-- ❌ Mixed case — inconsistent and harder to scan
Select o.id From orders o Where order_status = 'confirmed';

Indentation and Alignment

Use 4 spaces per indentation level. Align major clauses at the left margin with right-aligned keywords so columns align vertically — creating a visual structure that makes queries scannable:

-- ✅ Right-aligned clause keywords — columns align cleanly
SELECT
    o.id,
    o.user_id,
    o.total_amount,
    o.order_status,
    o.created_at
FROM   orders o
JOIN   users  u ON u.id = o.user_id
WHERE  o.order_status = 'confirmed'
  AND  o.deleted_at  IS NULL
  AND  o.created_at  >= NOW() - INTERVAL '30 days'
ORDER BY o.created_at DESC;

-- ✅ Subqueries — indent one level inside
SELECT
    p.id,
    p.name,
    p.price
FROM  products p
WHERE p.id IN (
    SELECT DISTINCT product_id
    FROM   order_items
    WHERE  created_at >= NOW() - INTERVAL '7 days'
);

One Clause Per Line

Each major SQL clause starts on its own line. With more than two columns in a SELECT, each column gets its own line. Each AND/OR condition also gets its own line:

-- ✅ Each clause and condition on its own line
SELECT
    u.id,
    u.email,
    u.first_name,
    u.last_name,
    u.created_at
FROM   users u
WHERE  u.is_active          = TRUE
  AND  u.has_verified_email = TRUE
  AND  u.created_at        >= '2025-01-01'
  AND  u.role              IN ('admin', 'standard_user')
ORDER BY u.last_name  ASC,
         u.first_name ASC;

-- ❌ Everything on one line — unreadable
SELECT u.id, u.email, u.first_name, u.last_name FROM users u WHERE u.is_active = TRUE AND u.has_verified_email = TRUE ORDER BY u.last_name;

Table Aliases

Use short, consistent aliases — typically the initial letter(s) of the table name:

-- ✅ Short, consistent aliases
SELECT
    o.id              AS order_id,
    o.total_amount,
    u.email           AS user_email,
    p.name            AS product_name,
    oi.quantity
FROM   orders      o
JOIN   users       u  ON u.id  = o.user_id
JOIN   order_items oi ON oi.order_id = o.id
JOIN   products    p  ON p.id  = oi.product_id
WHERE  o.order_status = 'confirmed';

-- ✅ Self-join — use descriptive role aliases
SELECT
    emp.id        AS employee_id,
    emp.full_name AS employee_name,
    mgr.full_name AS manager_name
FROM   employees emp
LEFT JOIN employees mgr ON mgr.id = emp.manager_id;

Column Aliases

Always use the AS keyword. Aliases are snake_case:

-- ✅ Explicit AS keyword, snake_case alias
SELECT
    u.first_name || ' ' || u.last_name  AS full_name,
    COUNT(o.id)                          AS order_count,
    SUM(o.total_amount)                  AS total_spent,
    MAX(o.created_at)                    AS last_order_at
FROM  users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.first_name, u.last_name;

SELECT * — Never in Production Code

-- ❌ SELECT * in application or migration code
SELECT * FROM orders WHERE user_id = $1;

-- ✅ Name every column you need
SELECT id, user_id, total_amount, order_status, created_at
FROM   orders
WHERE  user_id = $1;

The only acceptable use of SELECT *

SELECT * is acceptable for ad-hoc exploratory queries in a database console, and SELECT COUNT(*) for row counting — where * is idiomatic and treated efficiently by the optimiser.

JOINs — Always Explicit

Always write the JOIN type explicitly:

-- ✅ Explicit JOIN types
SELECT ...
FROM   orders      o
INNER JOIN users   u  ON u.id = o.user_id
LEFT  JOIN reviews r  ON r.order_id = o.id;

-- ❌ Implicit comma join — old, ambiguous, error-prone
SELECT ... FROM orders o, users u WHERE o.user_id = u.id;

Type	Use When
INNER JOIN	Both sides must have a matching row
LEFT JOIN	Keep all left rows; right side may be NULL
RIGHT JOIN	Rarely needed — restructure to a LEFT JOIN
FULL OUTER JOIN	Keep all rows from both sides
CROSS JOIN	Cartesian product — use intentionally and sparingly

WHERE Clause Formatting

Each filter condition gets its own line. AND/OR goes at the start of the continuation line. Use parentheses to group OR conditions explicitly:

-- ✅ One condition per line; AND/OR at line start
SELECT id, email, order_status
FROM   orders
WHERE  order_status IN ('pending', 'confirmed')
  AND  created_at   BETWEEN '2026-01-01' AND '2026-03-31'
  AND  deleted_at   IS NULL
  AND  total_amount > 0;

-- ✅ OR groups wrapped in parentheses
WHERE  (order_status = 'pending'   AND created_at < NOW() - INTERVAL '24 hours')
    OR (order_status = 'confirmed' AND is_gift = TRUE);

GROUP BY — Named Columns, Not Positions

-- ✅ Named columns — explicit and refactor-safe
SELECT
    DATE_TRUNC('month', created_at) AS revenue_month,
    currency_code,
    COUNT(*)                        AS order_count,
    SUM(total_amount)               AS total_revenue
FROM  orders
WHERE order_status = 'delivered'
GROUP BY DATE_TRUNC('month', created_at),
         currency_code
ORDER BY revenue_month DESC;

-- ❌ Positional GROUP BY — changes meaning silently when SELECT reorders
GROUP BY 1, 2
ORDER BY 1 DESC;

CTEs Over Nested Subqueries

Use WITH clauses to break complex queries into named, readable steps:

-- ✅ CTEs — each step named and readable
WITH confirmed_orders AS (
    SELECT
        user_id,
        total_amount,
        created_at
    FROM  orders
    WHERE order_status = 'confirmed'
      AND created_at  >= NOW() - INTERVAL '30 days'
),

user_totals AS (
    SELECT
        user_id,
        COUNT(*)          AS order_count,
        SUM(total_amount) AS total_spent
    FROM  confirmed_orders
    GROUP BY user_id
)

SELECT
    u.id,
    u.email,
    ut.order_count,
    ut.total_spent
FROM  users       u
JOIN  user_totals ut ON ut.user_id = u.id
WHERE ut.total_spent > 500
ORDER BY ut.total_spent DESC;

Window Functions

Format window functions with the OVER clause on its own indented block when it has multiple parts:

-- ✅ Short window clause — inline
SELECT
    id,
    total_amount,
    SUM(total_amount) OVER (PARTITION BY user_id)  AS user_total,
    RANK()            OVER (ORDER BY total_amount DESC) AS overall_rank
FROM orders;

-- ✅ Complex window frame — split across lines for readability
SELECT
    id,
    user_id,
    total_amount,
    created_at,
    SUM(total_amount) OVER (
        PARTITION BY user_id
        ORDER BY     created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )                                               AS running_total,
    AVG(total_amount) OVER (
        PARTITION BY user_id
        ORDER BY     created_at
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    )                                               AS rolling_7_avg
FROM orders;

-- ✅ Named window clause — when the same window is reused
SELECT
    id,
    total_amount,
    SUM(total_amount)  OVER w AS running_total,
    AVG(total_amount)  OVER w AS running_avg,
    COUNT(*)           OVER w AS order_sequence
FROM  orders
WINDOW w AS (
    PARTITION BY user_id
    ORDER BY     created_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
);

DML Formatting — INSERT, UPDATE, DELETE

-- ✅ INSERT — column list always required, values aligned
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
    ($1, $2, $3, $4),
    ($1, $5, $6, $7);

-- ✅ INSERT ... SELECT
INSERT INTO order_archive (order_id, user_id, total_amount, archived_at)
SELECT
    id,
    user_id,
    total_amount,
    NOW()
FROM  orders
WHERE created_at < NOW() - INTERVAL '2 years'
  AND order_status IN ('delivered', 'cancelled');

-- ✅ UPSERT — ON CONFLICT with clear formatting
INSERT INTO product_inventory (product_id, warehouse_id, quantity)
VALUES ($1, $2, $3)
ON CONFLICT (product_id, warehouse_id)
DO UPDATE SET
    quantity   = product_inventory.quantity + EXCLUDED.quantity,
    updated_at = NOW();

-- ✅ INSERT ... RETURNING — always capture the generated ID
INSERT INTO orders (user_id, total_amount, order_status)
VALUES ($1, $2, 'pending')
RETURNING id, created_at;

-- ✅ UPDATE — SET values aligned, WHERE clause formatted
UPDATE orders
SET    order_status = 'confirmed',
       confirmed_at = NOW(),
       updated_at   = NOW()
WHERE  id          = $1
  AND  order_status = 'pending';

-- ✅ DELETE — always include a WHERE clause; RETURNING for audit
DELETE FROM cart_items
WHERE  cart_id    = $1
  AND  product_id = $2
RETURNING id, quantity;

Never DELETE or UPDATE without a WHERE clause

A DELETE FROM orders without a WHERE clause deletes every row in the table. In psql, set AUTOCOMMIT to off and always verify with SELECT before executing DML on production.

DDL Formatting — CREATE TABLE

-- ✅ Consistent DDL formatting
CREATE TABLE order_items (
    -- Identity column
    id           BIGINT        PRIMARY KEY GENERATED ALWAYS AS IDENTITY,

    -- Foreign keys
    order_id     BIGINT        NOT NULL,
    product_id   BIGINT        NOT NULL,

    -- Data columns
    quantity     INTEGER       NOT NULL,
    unit_price   NUMERIC(12,2) NOT NULL,

    -- Audit columns — always last
    created_at   TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    updated_at   TIMESTAMPTZ   NOT NULL DEFAULT NOW(),

    -- Named constraints — at the bottom
    CONSTRAINT order_items_order_fkey
        FOREIGN KEY (order_id)
        REFERENCES orders(id)
        ON DELETE CASCADE,

    CONSTRAINT order_items_product_fkey
        FOREIGN KEY (product_id)
        REFERENCES products(id)
        ON DELETE RESTRICT,

    CONSTRAINT order_items_quantity_check
        CHECK (quantity > 0),

    CONSTRAINT order_items_unit_price_check
        CHECK (unit_price >= 0)
);

-- Always add a table comment for non-obvious tables
COMMENT ON TABLE order_items
    IS 'Individual line items belonging to an order. Immutable after order confirmation.';

Comments

-- ✅ Brief single-line comment — explains a non-obvious filter
SELECT id, email FROM users WHERE is_active = TRUE;
-- soft-deleted users excluded via deleted_at IS NULL in the view

-- ✅ Block comment for complex query documentation
/*
 * Monthly revenue report for the Finance Dashboard.
 * Excludes cancelled and refunded orders per accounting policy FIN-2024-03.
 * Refreshed nightly via pg_cron at 02:00 UTC.
 */
CREATE MATERIALIZED VIEW mv_monthly_revenue AS
    SELECT
        DATE_TRUNC('month', created_at)                          AS revenue_month,
        currency_code,
        SUM(total_amount)                                        AS gross_revenue,
        SUM(total_amount) - COALESCE(SUM(refund_amount), 0)      AS net_revenue,
        COUNT(*)                                                 AS order_count
    FROM  orders
    WHERE order_status NOT IN ('cancelled', 'refunded')
    GROUP BY 1, 2;

Formatting Quick Reference

Rule	Standard
Keywords	UPPERCASE
Identifiers	lowercase / snake_case
Indentation	4 spaces
SELECT columns	One per line when more than two
WHERE conditions	One per line; AND/OR at line start
JOIN type	Always explicit: INNER JOIN, LEFT JOIN
GROUP BY	Named columns — never positional
SELECT *	Banned in production/application code
Aliases	AS required; snake_case
Complex queries	CTEs over nested subqueries
Window functions	OVER (...) on its own block when multi-line
DML	Column list always required in INSERT; WHERE always required in UPDATE/DELETE
DDL	Named constraints at bottom of CREATE TABLE; COMMENT ON TABLE for non-obvious tables