10 Rare SQL Features Every Developer Should Know

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.

---

👉 Read original article on my newsletter: https://antondevtips.com/blog/10-rare-sql-features-every-developer-should-know

1. Common Table Expressions (CTEs)

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.

WITH recent_shipments AS (
    SELECT
        s.id,
        s.number,
        s.carrier,
        s.status,
        s.created_at
    FROM shipments.shipments s
    WHERE s.created_at >= CURRENT_DATE - INTERVAL '30 days'
),
shipment_details AS (
    SELECT
        rs.number,
        rs.carrier,
        rs.status,
        COUNT(si.id) AS total_items,
        SUM(si.quantity) AS total_quantity
    FROM recent_shipments rs
    LEFT JOIN shipments.shipment_items si ON rs.id = si.shipment_id
    GROUP BY rs.number, rs.carrier, rs.status
)
SELECT
    number AS shipment_number,
    carrier,
    status,
    total_items,
    total_quantity
FROM shipment_details
ORDER 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.

2. Window Functions

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.

SELECT
    number,
    carrier,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY carrier ORDER BY created_at DESC) AS shipment_sequence,
    RANK() OVER (PARTITION BY carrier ORDER BY created_at DESC) AS shipment_rank
FROM shipments.shipments;

SELECT
    number,
    status,
    created_at,
    LAG(status) OVER (ORDER BY created_at) AS previous_status,
    LEAD(carrier) OVER (ORDER BY created_at) AS next_carrier
FROM 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+.

---

👉 Read original article on my newsletter: https://antondevtips.com/blog/10-rare-sql-features-every-developer-should-know