Most developers use maybe 20 percent of SQL's capabilities.

They write SELECT, JOIN, and GROUP BY, and stop there.

But SQL has a second layer - features that turn a page of application code or three separate queries into a single clean statement.

Senior developers reach for these all the time. Many junior and mid-level developers have never seen them.

None of them is new or obscure. They are sitting in the database you already use, waiting to be picked up.

Today, I want to show you 10 rare SQL features every developer should know.

In this post, we will explore:

• Common Table Expressions (CTEs)
• Window functions
• LATERAL joins
• GROUPING SETS, ROLLUP, and CUBE
• The FILTER clause in aggregates
• UPSERT with INSERT ... ON CONFLICT
• JSON support
• Computed / generated columns
• TABLESAMPLE
• Partial indexes

Let's dive in.

All queries in this post were tested on the PostgreSQL database. Most of these features exist in other databases too, though the exact syntax differs - I will note the main differences as we go.

A complex query packed into a single statement is hard to read and even harder to change.

A Common Table Expression (CTE) lets you break that query into named, sequential steps using the WITH keyword. Each step reads like a temporary, named result you can build on.

sql
1WITH recent_shipments AS (
2    SELECT
3        s.id,
4        s.number,
5        s.carrier,
6        s.status,
7        s.created_at
8    FROM shipments.shipments s
9    WHERE s.created_at >= CURRENT_DATE - INTERVAL '30 days'
10),
11shipment_details AS (
12    SELECT
13        rs.number,
14        rs.carrier,
15        rs.status,
16        COUNT(si.id) AS total_items,
17        SUM(si.quantity) AS total_quantity
18    FROM recent_shipments rs
19    LEFT JOIN shipments.shipment_items si ON rs.id = si.shipment_id
20    GROUP BY rs.number, rs.carrier, rs.status
21)
22SELECT
23    number AS shipment_number,
24    carrier,
25    status,
26    total_items,
27    total_quantity
28FROM shipment_details
29ORDER BY total_quantity DESC;

This query has two named parts.

recent_shipments selects shipments from the last 30 days. shipment_details then builds on it, joining the items and aggregating counts and quantities. The final SELECT reads from the second CTE as if it were a table.

The result is a query you read top to bottom, like steps in a recipe, instead of using nested subqueries from the inside out.

CTEs also support recursion with WITH RECURSIVE, which is how you query hierarchical data like org charts and category trees.

A Common Table Expression can be used within a SELECT, INSERT, UPDATE, or DELETE statement.

Sometimes you need a calculation across related rows but still want every individual row in the result.

A GROUP BY collapses rows into one per group. A window function calculates across a set of rows - the window - while keeping each row intact.

sql
1SELECT
2    number,
3    carrier,
4    created_at,
5    ROW_NUMBER() OVER (PARTITION BY carrier ORDER BY created_at DESC) AS shipment_sequence,
6    RANK() OVER (PARTITION BY carrier ORDER BY created_at DESC) AS shipment_rank
7FROM shipments.shipments;
8
9SELECT
10    number,
11    status,
12    created_at,
13    LAG(status) OVER (ORDER BY created_at) AS previous_status,
14    LEAD(carrier) OVER (ORDER BY created_at) AS next_carrier
15FROM shipments.shipments;

The first query ranks each carrier's shipments by date. ROW_NUMBER() gives a unique sequence within each carrier (the PARTITION BY carrier), and RANK() does the same, but ties share a rank.

The second query uses LAG and LEAD to look at the previous and next row in order - here, the previous status and the next carrier - without a self-join.

Window functions are how you build running totals, rankings, moving averages, and row-to-row comparisons.

They are standard SQL and work in PostgreSQL, SQL Server, Oracle, and MySQL 8+.

A normal join matches two tables on a condition. It cannot run a separate query for each row of the first table.

A LATERAL join can. It lets a subquery on the right reference columns from the table on the left, running once per row - perfect for top-N-per-group problems.

sql
1-- For each carrier, grab their single most recent shipment
2SELECT
3    c.carrier,
4    s.number,
5    s.status,
6    s.created_at
7FROM (
8    SELECT DISTINCT carrier
9    FROM shipments.shipments
10) c
11CROSS JOIN LATERAL (
12    SELECT
13        number,
14        status,
15        created_at
16    FROM shipments.shipments
17    WHERE carrier = c.carrier
18    ORDER BY created_at DESC
19    LIMIT 1
20) s;

For each distinct carrier, the lateral subquery selects that carrier's single most recent shipment (ORDER BY created_at DESC LIMIT 1).

The key is WHERE carrier = c.carrier - the inner query sees the outer row's carrier, which a plain subquery cannot do.

This is the cleanest way to express "the latest row per group" or "the top 3 per category" without a window function.

Note: SQL Server writes the same thing with CROSS APPLY (and OUTER APPLY for the left-join version). PostgreSQL uses CROSS JOIN LATERAL and LEFT JOIN LATERAL.

A report often needs several levels of summary at once: totals by carrier and status, subtotals per carrier, and a grand total.

The naive way is several queries glued together with UNION ALL.

GROUPING SETS, ROLLUP, and CUBE produce all those levels in a single query.

sql
1SELECT
2    carrier,
3    status,
4    COUNT(*) AS shipment_count,
5    SUM(si.quantity) AS total_quantity
6FROM shipments.shipments s
7LEFT JOIN shipments.shipment_items si ON s.id = si.shipment_id
8GROUP BY GROUPING SETS (
9    (carrier, status), -- by carrier & status
10    (carrier),         -- subtotal by carrier
11    (status),          -- subtotal by status
12    ()                 -- grand total
13);
14
15SELECT
16    carrier,
17    status,
18    DATE_TRUNC('month', created_at) AS month,
19    COUNT(*) AS shipment_count
20FROM shipments.shipments
21GROUP BY ROLLUP (
22    carrier,
23    status,
24    DATE_TRUNC('month', created_at)
25);

The first query lists exactly the groupings you need: by carrier and status, by carrier alone, by status alone, and the empty () for the grand total.

ROLLUP in the second query is shorthand for hierarchical subtotals: carrier, then carrier and status, then carrier, status, and month, down to the total.

CUBE generates every possible combination of the columns.

One query replaces four queries, and the database computes the levels in a single pass rather than scanning the table multiple times.

These are part of standard SQL and work in PostgreSQL, SQL Server, and Oracle.

You often need to count or sum only the rows that meet a condition, broken out side by side.

The FILTER clause applies a condition to a single aggregate, so each one counts a different subset - in one row, in one pass over the data.

sql
1SELECT
2    carrier,
3    COUNT(*) AS total_shipments,
4    COUNT(*) FILTER (WHERE status = 'delivered') AS delivered_count,
5    COUNT(*) FILTER (WHERE status = 'in_transit') AS in_transit_count,
6    COUNT(*) FILTER (WHERE status = 'pending') AS pending_count,
7    SUM(si.quantity) FILTER (WHERE status = 'delivered') AS delivered_quantity,
8    SUM(si.quantity) FILTER (WHERE status = 'pending') AS pending_quantity
9FROM shipments.shipments s
10LEFT JOIN shipments.shipment_items si ON s.id = si.shipment_id
11GROUP BY carrier;

Each COUNT(*) FILTER (WHERE ...) counts only the matching rows, so you get delivered, in-transit, and pending counts as separate columns per carrier.

COUNT(*) FILTER (WHERE status = 'delivered') is easier to read than the old trick with case: SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END).

The intent of the filter clause is much more obvious.

Note: FILTER is supported by PostgreSQL. SQL Server and MySQL do not have it - there you fall back to CASE inside the aggregate, like COUNT(CASE WHEN status = 'delivered' THEN 1 END).

Insert the row if it is new, update it if it already exists - a common need that usually takes a SELECT, an IF, and two code paths.

UPSERT does it in one atomic statement, with no race condition between the check and the write.

sql
1ALTER TABLE shipments.shipments
2ADD CONSTRAINT shipments_number_unique UNIQUE (number);
3
4INSERT INTO shipments.shipments
5(
6    id, number, order_id, address_street, address_city, address_zip,
7    carrier, receiver_email, status, created_at, updated_at
8)
9VALUES
10(
11    '550e8400-e29b-41d4-a716-446655440000', 'SH-2024-001', 'ORD-2024-001',
12    '123 Main St', 'New York', '10001', 'FedEx', '[email protected]',
13    'pending', NOW(), NOW()
14)
15ON CONFLICT (number) DO UPDATE
16SET
17    carrier = EXCLUDED.carrier,
18    status = EXCLUDED.status,
19    updated_at = GREATEST(shipments.updated_at, EXCLUDED.updated_at);

First, we add a unique constraint on number, on which the conflict is detected.

Then INSERT ... ON CONFLICT (number) DO UPDATE tries to insert; if a row with that number already exists, it updates instead.

The EXCLUDED pseudo-table holds the values you tried to insert, so carrier = EXCLUDED.carrier means "use the new carrier."

GREATEST(shipments.updated_at, EXCLUDED.updated_at) keeps the later of the two timestamps.

One statement, no duplicate rows, and no read-modify-write race between concurrent callers.

Note: this is PostgreSQL syntax. The SQL standard (SQL Server, Oracle) uses the MERGE statement; MySQL uses INSERT ... ON DUPLICATE KEY UPDATE.

Not all data is relational. Sometimes you need to store a flexible, semi-structured payload - an event, a webhook body, a settings blob.

PostgreSQL stores JSON natively in the JSONB type and lets you query inside it, so you do not need a separate document database for occasional JSON. Or stringified JSON as strings and further working with them in your backend code, losing all the index capabilities.

sql
1CREATE TABLE shipments.events (
2    id SERIAL PRIMARY KEY,
3    payload JSONB NOT NULL
4);
5
6-- Insert sample data
7INSERT INTO shipments.events (payload) VALUES
8('{"type":"click","coordinates":[{"x":10,"y":20},{"x":15,"y":25}]}'),
9('{"type":"hover","coordinates":[{"x":5,"y":30}]}'),
10('{"type":"scroll","coordinates":[{"x":0,"y":100},{"x":0,"y":200},{"x":0,"y":300}]}');
11
12-- Extract simple JSON fields
13SELECT
14    payload ->> 'type' AS event_type,
15    payload -> 'coordinates' -> 0 ->> 'x' AS first_x,
16    payload -> 'coordinates' -> 0 ->> 'y' AS first_y
17FROM shipments.events;

The events table stores a JSONB payload. The query then reaches into it: ->> extracts a value as text, and -> extracts a nested JSON object or array element, so payload -> 'coordinates' -> 0 ->> 'x' reads the x of the first coordinate.

JSONB is stored in a parsed binary form and can be indexed, so you can filter and extract without scanning whole documents.

Note: SQL Server queries JSON with JSON_VALUE and OPENJSON; the SQL standard adds JSON_TABLE (in Oracle, MySQL, and PostgreSQL 17+) to turn a JSON array directly into relational rows.

When a column's value is always derived from other columns, computing it in application code is error-prone - every write path has to remember the formula.

A generated column moves that formula into the table definition, so the database calculates and stores it automatically.

sql
1CREATE TABLE shipments.shipping_costs (
2    id SERIAL PRIMARY KEY,
3    shipment_id UUID NOT NULL,
4    base_rate DECIMAL(10,2) NOT NULL,
5    weight_kg DECIMAL(8,2) NOT NULL,
6    distance_km DECIMAL(10,2) NOT NULL,
7    fuel_surcharge_rate DECIMAL(5,4) NOT NULL DEFAULT 0.15,
8
9    -- Generated columns
10    weight_cost DECIMAL(10,2) GENERATED ALWAYS AS (weight_kg * 2.50) STORED,
11    distance_cost DECIMAL(10,2) GENERATED ALWAYS AS (distance_km * 0.85) STORED,
12    fuel_surcharge DECIMAL(10,2) GENERATED ALWAYS AS (base_rate * fuel_surcharge_rate) STORED,
13    total_cost DECIMAL(10,2) GENERATED ALWAYS AS (
14        base_rate
15        + (weight_kg * 2.50)
16        + (distance_km * 0.85)
17        + (base_rate * fuel_surcharge_rate)
18    ) STORED,
19
20    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
21
22    FOREIGN KEY (shipment_id) REFERENCES shipments.shipments(id)
23);

Each GENERATED ALWAYS AS (...) STORED column is computed from the other columns whenever a row is inserted or updated. total_cost adds the base rate, weight cost, distance cost, and fuel surcharge - and you can never forget to recalculate it, because you cannot write to it directly.

STORED means the value is physically saved (and can be indexed), rather than recomputed on every read.

Note: SQL Server calls these computed columns, written as total_cost AS (...) with PERSISTED for the stored equivalent. MySQL uses the same GENERATED ALWAYS AS syntax as PostgreSQL.

Running an exploratory query against a huge table is slow when you only need a feel for the data, not every row.

TABLESAMPLE returns a random sample of the table, reading a fraction of it instead of scanning everything.

sql
1-- Sample for testing queries on large tables
2SELECT carrier, COUNT(*)
3FROM shipments.shipments TABLESAMPLE SYSTEM (5)
4GROUP BY carrier;
5
6-- Get a random sample with a specific seed for reproducible results
7SELECT * FROM shipments.shipments
8TABLESAMPLE BERNOULLI (10)
9REPEATABLE (12345);
10
11-- Sample with WHERE clause (applied after sampling)
12SELECT * FROM shipments.shipments
13TABLESAMPLE BERNOULLI (20)
14WHERE status = 'pending';
15
16-- Sample with joins
17SELECT
18    s.number, s.carrier, sc.total_cost
19FROM shipments.shipments s
20TABLESAMPLE SYSTEM (10)
21JOIN shipments.shipping_costs sc ON s.id = sc.shipment_id;

TABLESAMPLE SYSTEM (5) samples roughly 5 percent of the table by reading random pages - fast, but block-based. BERNOULLI (10) samples about 10 percent row-by-row, which is more statistically even but slower.

REPEATABLE (12345) fixes the random seed, so the same sample comes back on every run - useful for reproducible tests.

It is built for quick checks, profiling, and testing queries on large tables without paying for a full scan.

Note: TABLESAMPLE is part of standard SQL; PostgreSQL ships the SYSTEM and BERNOULLI methods, and SQL Server supports TABLESAMPLE SYSTEM as well.

An index over an entire table costs storage and slows writes - even when your queries only ever touch a small slice of the rows.

A partial index covers just the rows that match a condition, so it is smaller, faster to scan, and cheaper to maintain.

sql
1-- Create a partial index for active/pending shipments
2CREATE INDEX idx_shipments_pending_carrier
3ON shipments.shipments (carrier, created_at)
4WHERE status IN ('pending', 'in_transit');
5
6-- Create a partial index for specific carrier queries
7CREATE INDEX idx_shipments_fedex_status
8ON shipments.shipments (status, updated_at)
9WHERE carrier = 'FedEx';
10
11-- This query will use idx_shipments_pending_carrier
12SELECT number, carrier, created_at
13FROM shipments.shipments
14WHERE status = 'pending'
15  AND carrier = 'FedEx'
16ORDER BY created_at DESC;
17
18-- This query will use idx_shipments_fedex_status
19SELECT number, status, updated_at
20FROM shipments.shipments
21WHERE carrier = 'FedEx'
22  AND status IN ('delivered', 'pending')
23ORDER BY updated_at DESC;

The first index covers only pending and in-transit shipments; the second only FedEx rows. Queries that match those conditions use the matching index, and because each index holds a fraction of the table, lookups and maintenance are quicker.

Partial indexes shine for hot subsets - active records, a soft-delete is_deleted = false filter, or one high-traffic status - where most queries care about a small, predictable slice.

Note: SQL Server calls these filtered indexes, with the same CREATE INDEX ... WHERE syntax. For a full tour of index types, see Optimizing SQL Performance with Indexing Strategies.

None of these features is exotic.

They are standard SQL, sitting in the database you already run, and each one replaces a chunk of application code or a stack of queries with something simpler. You do not need every one of these on day one.

Pick the one that fixes a problem you have right now - a complex nested query, a slow report, a read-modify-write race - and add the rest as you meet them.

Hope you find this newsletter useful. See you next time.