10 Rare SQL Features Every Developer Should Know

Santanu Chandra

6 min read

·

Sep 15, 2026

--

4

Listen

Share

You already know JOIN and GROUP BY. These are the ones that turn 40 lines of application code into 4 lines of SQL.

Press enter or click to view image in full size

Most developers learn about 20% of SQL and stop. That 20% is enough to ship — but the remaining 80% is where the leverage lives. Below are ten features that are fully standard (or near-universal), rarely taught, and genuinely change how you write queries.

Examples use PostgreSQL syntax unless noted; I’ll flag dialect differences as we go.

1. FILTER — Conditional Aggregates Without CASE

Everyone writes conditional counts like this:

SELECT
  COUNT(CASE WHEN status = 'paid'     THEN 1 END) AS paid,
  COUNT(CASE WHEN status = 'refunded' THEN 1 END) AS refunded
FROM orders;

The FILTER clause does the same thing and actually reads like English:

SELECT
  COUNT(*) FILTER (WHERE status = 'paid')     AS paid,
  COUNT(*) FILTER (WHERE status = 'refunded') AS refunded,
  SUM(total) FILTER (WHERE created_at > now() - interval '30 days') AS last_30d_revenue
FROM orders;

It works with any aggregate — SUM, AVG, ARRAY_AGG, everything.

Dialect note: PostgreSQL and SQLite support FILTER. SQL Server and MySQL still need CASE.

2. LATERAL Joins — A for Loop Inside Your Query

A LATERAL join lets the subquery on the right reference columns from the table on the left. It's the answer to "get me the top 3 X for each Y," which is otherwise painful.

SELECT c.name, recent.title, recent.created_at
FROM customers c
CROSS JOIN LATERAL (
    SELECT o.title, o.created_at
    FROM orders o
    WHERE o.customer_id = c.id     -- ← references the outer table
    ORDER BY o.created_at DESC
    LIMIT 3
) AS recent;

Without LATERAL, you'd need a window function plus an outer filter. With it, the intent is obvious: for each customer, run this query.

Use LEFT JOIN LATERAL ... ON true if you want customers with zero orders to still appear. Dialect note: SQL Server calls this CROSS APPLY / OUTER APPLY.

3. DISTINCT ON — The Cleanest "Latest Row Per Group"

The most common reporting question in existence: give me the most recent record per user. Most people reach for a window function. PostgreSQL has a one-liner:

SELECT DISTINCT ON (user_id)
       user_id, status, created_at
FROM sessions
ORDER BY user_id, created_at DESC;

DISTINCT ON (user_id) keeps the first row for each user_id as determined by the ORDER BY. The rule to remember: your ORDER BY must start with the same columns as the DISTINCT ON.

The portable equivalent is considerably noisier:

SELECT user_id, status, created_at
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM sessions
) t
WHERE rn = 1;

4. Window Frames — ROWS BETWEEN Is Where the Power Is

Most people use OVER (PARTITION BY ... ORDER BY ...) and stop. The frame clause is the part that unlocks running totals, moving averages, and rolling windows.

SELECT
  day,
  revenue,
  SUM(revenue) OVER (
      ORDER BY day
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  AVG(revenue) OVER (
      ORDER BY day
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales;

A 7-day moving average in one expression, no self-join, no temp table.

Gotcha: the default frame when you supply ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats ties as one group. If your ordering column has duplicates, ROWS and RANGE give different answers. Be explicit.

5. Recursive CTEs — Trees, Graphs, and Generated Series

WITH RECURSIVE walks hierarchies without a single loop in application code.

WITH RECURSIVE org_chart AS (
    -- anchor: the top of the tree
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL
UNION ALL
    -- recursive step: everyone reporting to someone already found
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT repeat('  ', depth - 1) || name AS chart, depth
FROM org_chart
ORDER BY depth;

The same pattern generates sequences — useful for filling date gaps in reports:

WITH RECURSIVE dates AS (
    SELECT DATE '2026-01-01' AS d
    UNION ALL
    SELECT d + 1 FROM dates WHERE d < DATE '2026-01-31'
)
SELECT * FROM dates;

Always make sure the recursive branch has a terminating condition, or you’ll hit the recursion limit.

6. GROUPING SETS, ROLLUP, and CUBE — Multiple Aggregation Levels in One Pass

Need totals by region, by product, and a grand total? Don’t UNION ALL three queries — the table gets scanned three times.

SELECT region, product, SUM(revenue) AS revenue
FROM sales
GROUP BY GROUPING SETS (
    (region, product),   -- detail
    (region),            -- subtotal per region
    ()                   -- grand total
);

ROLLUP (region, product) is shorthand for exactly that hierarchy. CUBE (region, product) produces every combination.

Use GROUPING() to tell a real NULL from a subtotal placeholder:

SELECT
  CASE WHEN GROUPING(region) = 1 THEN 'ALL REGIONS' ELSE region END AS region,
  SUM(revenue)
FROM sales
GROUP BY ROLLUP (region);

7. MERGE — Upsert, Properly

MERGE handles insert-or-update-or-delete in a single atomic statement. It's now in the SQL standard and supported by PostgreSQL 15+, SQL Server, Oracle, and others.

MERGE INTO inventory AS target
USING incoming_shipment AS source
   ON target.sku = source.sku
WHEN MATCHED AND source.quantity = 0 THEN
    DELETE
WHEN MATCHED THEN
    UPDATE SET quantity = target.quantity + source.quantity
WHEN NOT MATCHED THEN
    INSERT (sku, quantity) VALUES (source.sku, source.quantity);

PostgreSQL’s older INSERT ... ON CONFLICT is still excellent for simple upserts, but it can't express the DELETE branch.

8. RETURNING — Stop Querying Twice

Almost nobody outside the Postgres world knows about this, and it’s a shame. Any INSERT, UPDATE, or DELETE can hand back the affected rows.

INSERT INTO users (email, username)
VALUES ('dev@example.com', 'sharp_dev')
RETURNING id, created_at;

No second round trip to fetch the generated ID. It composes with CTEs too, which is how you write an atomic “move rows between tables”:

WITH archived AS (
    DELETE FROM sessions
    WHERE created_at < now() - interval '90 days'
    RETURNING *
)
INSERT INTO sessions_archive
SELECT * FROM archived;

Dialect note: SQL Server has OUTPUT, which is similar in spirit.

9. VALUES as a Table — Inline Lookup Tables and Bulk Ops

VALUES isn't just for INSERT. It's a table constructor you can join against.

SELECT o.id, o.status, labels.display_name
FROM orders o
JOIN (VALUES
    ('paid',     'Paid ✅'),
    ('pending',  'Awaiting payment'),
    ('refunded', 'Refunded ↩')
) AS labels(status, display_name)
  ON o.status = labels.status;

That’s a lookup table with no migration, no extra table, no CASE chain. It also gives you clean bulk updates:

UPDATE products p
SET price = v.new_price
FROM (VALUES (1, 19.99), (2, 24.50), (3, 8.00)) AS v(id, new_price)
WHERE p.id = v.id;

10. IS DISTINCT FROM — Null-Safe Comparison

This one fixes a bug you’ve probably shipped. In SQL, NULL = NULL is not true — it's NULL. So this quietly misses rows:

-- ❌ never matches rows where either side is NULL
SELECT * FROM users WHERE old_email <> new_email;

IS DISTINCT FROM treats NULL as a comparable value:

-- ✅ correctly catches NULL → 'x', 'x' → NULL, and 'x' → 'y'
SELECT * FROM users WHERE old_email IS DISTINCT FROM new_email;

It’s the single best tool for change-detection queries, audit triggers, and “what actually changed in this row” logic.

Dialect note: MySQL spells it <=> (null-safe equal, the inverse). SQL Server has no direct equivalent — use EXISTS (SELECT a EXCEPT SELECT b) or explicit IS NULL handling.

Where to Start

If you only adopt two of these, make them FILTER and IS DISTINCT FROM — they're tiny changes that immediately make queries shorter and more correct. Once those feel natural, LATERAL and window frames are the two that will most change what you consider possible in a single query.

The broader point: SQL is a far more expressive language than the subset most of us use daily. Every one of these features exists because someone hit the same wall you’re hitting now — and the answer was already in the database, not in your application code.

Which of these was new to you? The RETURNING-inside-a-CTE trick is usually the one that gets the strongest reaction.