SQL Patterns: Joins, Window Functions, MERGE, and Killing N+1

Problem Context

SQL is 50 years old in 2026 and still the highest-leverage skill in the data stack. Every NoSQL contender that promised to kill it — MongoDB, DynamoDB, Cassandra — eventually shipped a SQL-ish query layer (Mongo aggregation pipelines, PartiQL, CQL). The reason is boring and durable: declarative set-based logic is genuinely a better way to express most data operations than imperative loops.

Yet most application code is written by engineers who know just enough SQL to get a JOIN wrong. They use SELECT *, N+1 in ORMs, write correlated subqueries that scan a table per row, and reach for stored procedures because they don't know window functions exist. This guide is the patterns that separate "working" SQL from production SQL.

Concept Explanation

Think in relations: tables and query results are unordered sets of rows. SQL is the language for transforming sets:

• FROM / JOIN — combine sets (cartesian product, then filter).
• WHERE — filter rows.
• GROUP BY / HAVING — collapse rows into buckets, then filter buckets.
• SELECT — project columns (and compute aggregates / window functions).
• ORDER BY / LIMIT — finally impose ordering for the consumer.

The optimizer reorders all of this; you describe what, it picks how. The execution plan is your contract with it.

flowchart LR
    F["FROM / JOIN<br/>(combine)"] --> W["WHERE<br/>(filter rows)"]
    W --> G["GROUP BY<br/>(bucket)"]
    G --> H["HAVING<br/>(filter buckets)"]
    H --> S["SELECT<br/>(project + window)"]
    S --> O["ORDER BY / LIMIT<br/>(present)"]

    style F fill:#0078D4,color:#fff,stroke:#005a9e
    style O fill:#16a34a,color:#fff,stroke:#15803d

Implementation

Step 1: Solve N+1 with a single JOIN or LATERAL

-- Bad: ORM loops, one query per author
-- SELECT * FROM authors;
-- foreach author: SELECT * FROM books WHERE author_id = ?

-- Good: one round trip
SELECT a.id, a.name, b.id AS book_id, b.title
FROM authors a
LEFT JOIN books b ON b.author_id = a.id
ORDER BY a.id;

-- Better when you only need the latest N per author
SELECT a.id, a.name, b.title, b.published_at
FROM authors a
JOIN LATERAL (
    SELECT title, published_at FROM books
    WHERE author_id = a.id
    ORDER BY published_at DESC
    LIMIT 3
) b ON true;

Step 2: EXISTS for membership, JOIN for projection

-- "Customers who placed an order in 2026"
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id AND o.created_at >= '2026-01-01'
);

-- IN with a subquery is OK but EXISTS short-circuits and avoids dedup
-- JOIN would duplicate customers per matching order — wrong shape.

Step 3: Window functions instead of self-joins

-- Running total per customer
SELECT customer_id, created_at, total_cents,
       SUM(total_cents) OVER (
           PARTITION BY customer_id
           ORDER BY created_at
           ROWS UNBOUNDED PRECEDING
       ) AS lifetime_total
FROM orders;

-- Top N per group
SELECT * FROM (
    SELECT *,
           RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS r
    FROM products
) t WHERE r <= 5;

Step 4: Upsert (MERGE in 2026 is finally portable)

-- ANSI MERGE works in SQL Server, Postgres 15+, Oracle, Snowflake, BigQuery
MERGE INTO inventory AS t
USING (VALUES ('SKU-1', 10), ('SKU-2', 5)) AS s(sku, qty)
ON t.sku = s.sku
WHEN MATCHED THEN UPDATE SET qty = t.qty + s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty);

-- Postgres-flavored alternative (older, still common)
INSERT INTO inventory (sku, qty) VALUES ('SKU-1', 10)
ON CONFLICT (sku) DO UPDATE SET qty = inventory.qty + EXCLUDED.qty;

Step 5: Parameterize from C# (Dapper, no ORM tax)

using Dapper;
using Npgsql;

await using var conn = new NpgsqlConnection(connStr);

// Always parameterize — never string-concatenate user input
var orders = await conn.QueryAsync<Order>(
    """
    SELECT id, customer_id, total_cents, created_at
    FROM orders
    WHERE customer_id = @CustomerId AND created_at >= @Since
    ORDER BY created_at DESC
    LIMIT @Take
    """,
    new { CustomerId = customerId, Since = since, Take = 50 });

Step 6: Read the plan

-- Postgres
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- SQL Server
SET STATISTICS IO, TIME ON; SELECT ...;

-- Look for:
-- "Seq Scan" / "Table Scan"    → missing index or unused index
-- "Hash Join" on small + huge  → reverse the join order if possible
-- "Nested Loop" on large rows  → usually you want hash or merge join
-- "Sort"                       → consider an index that already orders

Common Pitfalls

1. SELECT * everywhere. Wastes network, defeats covering indexes, and breaks when columns are added. Project explicit columns.
2. Functions on indexed columns. WHERE LOWER(email) = 'x' kills the index. Either store normalized data or create a functional index: CREATE INDEX ON users (LOWER(email)).
3. Implicit type coercion. WHERE id = '42' on a bigint forces a cast on every row. Match types exactly.
4. OR across columns. WHERE a = 1 OR b = 2 often defeats indexes. Rewrite as UNION ALL of two single-column lookups.
5. NULL semantics surprises. NULL = NULL is unknown, not true. Use IS NULL, and rememberNOT IN with a NULL in the list returns no rows.
6. Trusting the ORM's SQL. Always log generated queries in dev. EF Core, Hibernate, Sequelize all emit pathological SQL when given the chance — eager loading, projection mistakes, polymorphic queries.

Practical Takeaways

• Think in sets, not loops. If you're writing a cursor or app-side loop, ask whether one query would do it.
• EXISTS for "does any match exist", JOIN for "give me the matching rows".
• Window functions replace 90% of self-joins and 100% of running-total subqueries.
• MERGE / ON CONFLICT is the canonical upsert in 2026 — works in Postgres 15+, SQL Server, Snowflake, BigQuery.
• Parameterize everything; never concatenate strings into SQL.
• Read the execution plan before tuning. EXPLAIN ANALYZE is the only honest profiler.
• Log every query your ORM emits in dev. The N+1 you find is the production incident you avoid.