The query language through the eyes of a retail system developer: joins, grouping, subqueries and CTEs, window functions, procedures and functions, working with sets instead of loops. The main dialect is T-SQL; where PostgreSQL behaves differently, it gets one line here, and the details are in PostgreSQL vs SQL Server.
Junior+
What kinds of JOIN are there and how do they differ?
A JOIN combines rows of two tables using the condition in ON. The types differ in what happens to rows without a match.
Type
What it returns
INNER JOIN
only rows that have a match
LEFT JOIN
all rows on the left; no match — NULL on the right
RIGHT JOIN
the same for the right table
FULL JOIN
all rows of both tables
CROSS JOIN
every combination, no condition
How to use
Receipts with the store name — INNER JOIN.
All stores, even without sales — LEFT JOIN from stores to sales.
A “store × day” grid for a report with zeros — CROSS JOIN with a calendar.
Common mistake
A condition on the right table of a LEFT JOIN in WHERE: WHERE s.SoldOn = '2026-10-01'. Stores without sales have NULL there, the comparison is not true, and they disappear. Such a filter belongs in ON.
Takeaway: the JOIN type decides what happens to unmatched rows; a filter on the right table of a LEFT JOIN goes into ON.
How it works
Logically, the server builds all pairs of rows and keeps those where the ON condition is true.
An outer join adds the unmatched rows of the preserved side, with NULL in place of the other side.
Only then does WHERE filter the result.
That is why for an INNER JOIN a condition in ON and in WHERE is equivalent, but for a LEFT JOIN it is not.
When to choose what
Task
Construct
Is there a match; no columns needed from the right
EXISTS
What exists in only one of two sources
FULL JOIN and a check for NULL
Top-N or a function of each row’s columns
CROSS APPLY or OUTER APPLY
EXISTS is a semi join: a left row is returned once, however many matches exist. CROSS APPLY behaves like an INNER JOIN, OUTER APPLY like a LEFT JOIN. A RIGHT JOIN is usually rewritten as a LEFT JOIN — it reads more easily.
Pitfalls
Row multiplication. A one-to-many join repeats the header row for every detail, so SUM(h.Total) over receipt headers counts the total several times.
NULL in the key.NULL = NULL is not true, so rows with NULL in the join column never find a match.
LEFT, then INNER.A LEFT JOIN B ... JOIN C ON C.Id = B.CId drops rows of A without B: the inner join demands a match again.
Rule of thumb
Filters on the preserved side go into WHERE, filters on the joined side into ON. An existence check is EXISTS, not a JOIN with DISTINCT.
Takeaway: an outer join adds back unmatched rows after ON but before WHERE — that is where nearly all the pitfalls come from.
Under the hood
A logical JOIN does not prescribe an algorithm. The optimizer picks Nested Loops (few outer rows, an index on the inner side), Merge Join (both inputs sorted on the key) or Hash Match (large unsorted inputs). Details are in Indexes and query plans.
APPLY logically invokes the right side for each row on the left. A subquery or an inline function is expanded into the plan, and the optimizer is free to rewrite it as a regular join.
Join elimination. The optimizer removes a LEFT JOIN to a table whose columns are not used if the key on the right is unique. It removes an INNER JOIN if there is a trusted (WITH CHECK) foreign key on a NOT NULL column.
Cost
Row multiplication means not only a wrong total but also extra reads and memory for sorts and hash tables.
JOIN with DISTINCT first multiplies rows, then collapses them with a sort or a hash. EXISTS stops at the first match.
Trade-offs
CROSS APPLY (SELECT TOP (1) ... ORDER BY ...) is fast with an index on key and date and a small number of outer rows. On millions of rows, numbering in a single pass wins.
In PostgreSQL the same is written as CROSS JOIN LATERAL and LEFT JOIN LATERAL ... ON true.
How to check in production
In the actual plan, compare estimated and actual row counts after the join: an order-of-magnitude jump signals multiplication.
If a join was eliminated, the table is simply absent from the plan; if it is there, check that the keys are unique and trusted.
Takeaway: a JOIN is a logical set operation; the optimizer picks the algorithm, but row counts and where the condition goes are on you.
GROUP BY splits rows into groups with equal values of the listed columns.
An aggregate function collapses a group into one value: COUNT, SUM, AVG, MIN, MAX.
HAVING is a filter on groups; aggregates are allowed in it.
Logical order
FROM and JOIN — the source of rows.
WHERE — row filter.
GROUP BY — groups.
HAVING — group filter.
SELECT — expressions and aliases.
DISTINCT, then ORDER BY, then TOP or OFFSET.
How to use
Stores with revenue over 100: GROUP BY StoreId, then HAVING SUM(Amount) > 100 and ORDER BY Revenue DESC by alias.
Common mistake
Writing WHERE SUM(Amount) > 100. At the WHERE step there are no groups yet — you need HAVING.
Takeaway:WHERE filters rows, HAVING filters groups, and SELECT aliases are visible only in ORDER BY.
How it works
After GROUP BY, each result row is a group. SELECT may contain grouping columns, aggregates and expressions over them; any other column is an error.
Aggregates skip NULL: COUNT(*) counts rows, COUNT(Amount) counts only non-null values, AVG divides by the number of non-NULL values.
In SQL Server, AVG over int returns int: an average of 400.5 becomes 400. For a fraction use AVG(Amount * 1.0) or a cast to decimal.
All NULL values in a grouping column fall into one group, even though NULL = NULL is not true.
When to choose what
Condition
Where it goes
On source columns
WHERE: fewer rows reach grouping
On an aggregate
HAVING
On a SELECT alias
wrap the query in a subquery and filter outside
Pitfalls
Conditional aggregation. Revenue per category in one row is computed with SUM(CASE WHEN Category = 'Milk' THEN Amount END). In PostgreSQL it is shorter: SUM(Amount) FILTER (WHERE ...).
Row order. Only the outer ORDER BY guarantees it. Without it, any order is possible, even if it “was always by key”.
Rule of thumb
Filter as early as possible: WHERE before HAVING. Move a repeated expression into a subquery or CROSS APPLY (VALUES (...)) instead of copying it into SELECT and GROUP BY.
Takeaway: the order FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY explains which name and which condition is available where.
Under the hood
The logical order is the order of name binding. Microsoft’s documentation says explicitly that the query processor chooses the physical order and it may differ.
The optimizer moves HAVING conditions without aggregates below the grouping and pushes filters closer to the table read.
Grouping is done by Stream Aggregate (input sorted on the grouping keys, for example by an index) or Hash Aggregate (an in-memory hash table).
Cost
Hash Aggregate and sorts get a memory grant before they start. If the row estimate is too low, data spills to tempdb and the plan shows a spill warning.
An index whose key starts with the GROUP BY columns gives a Stream Aggregate with no sort.
Trade-offs
The evaluation order of predicates is not guaranteed. WHERE ISNUMERIC(Code) = 1 AND CAST(Code AS int) > 100 can fail; the safe version is TRY_CAST(Code AS int) > 100.
GROUPING SETS, ROLLUP and CUBE compute totals and subtotals in one pass. The GROUPING() function tells a total row apart from a group with a real NULL.
PostgreSQL allows a SELECT alias in GROUP BY and columns functionally dependent on the group’s primary key; SQL Server does not.
How to check in production
In the actual plan, look at the aggregate operator and spill warnings on Sort and Hash.
A large gap between estimated and actual rows before aggregation is a reason to check statistics; details are in Indexes and query plans.
Takeaway: the logical order explains name visibility, while the optimizer picks the physical one, so never rely on the evaluation order of conditions.
T-SQL
-- Receipts: store and amount (NULL means the receipt is still open)
SELECT StoreId,
COUNT(*) AS Receipts, -- all rows in the group
COUNT(Amount) AS Closed, -- only non-NULL
SUM(Amount) AS Revenue,
AVG(Amount) AS AvgCheck -- int: the fractional part is dropped
A CTE splits a complex query into steps with clear names, so it reads top to bottom like code.
Common mistake
WHERE ProductId NOT IN (SELECT ProductId FROM Stops) returns nothing when Stops contains NULL: a comparison with NULL gives neither “yes” nor “no”. Use NOT EXISTS.
Takeaway: a CTE is a named subquery; for “not in the list” use NOT EXISTS, not NOT IN.
How it works
Three-valued logic. A condition is TRUE, FALSE or UNKNOWN. Any comparison with NULL, even NULL = NULL, yields UNKNOWN, and WHERE lets through only TRUE.
NOT IN.x NOT IN (2, NULL) expands to x <> 2 AND x <> NULL. The second part is UNKNOWN, so the whole condition is not TRUE for any row.
Recursion. The anchor runs first. Then the recursive part joins to the previous step’s result until it returns an empty set. UNION ALL glues all steps together.
Materialization. SQL Server does not store a CTE: two references mean two executions.
When to choose what
Task
Choice
Does a related row exist
EXISTS or NOT EXISTS
A short list of constants
IN (1, 2, 3)
Steps of a complex query
CTE
An intermediate result needed many times
temp table #t
Pitfalls
The statement before WITH must end with ;, otherwise it is a syntax error.
MAXRECURSION defaults to 100: deeper means an error. OPTION (MAXRECURSION 0) removes the limit, and with it the protection against infinite loops.
Status <> 'Closed' does not return rows with NULL in Status. Add OR Status IS NULL or, since SQL Server 2022, write Status IS DISTINCT FROM 'Closed'.
Rule of thumb
For “not in the list” use NOT EXISTS. Declare columns NOT NULL where NULL makes no sense.
Takeaway:NULL turns a comparison into UNKNOWN, so NOT IN and <> lose rows, while NOT EXISTS and IS DISTINCT FROM do not.
Under the hood
SQL Server expands a CTE into the query like a view. Per the documentation, the result is not materialized: every outer reference re-executes the definition.
In PostgreSQL 12+ a side-effect-free CTE is inlined if it is referenced once and computed once if referenced several times. MATERIALIZED and NOT MATERIALIZED override that decision.
In the plan, a recursive CTE is a spool with a stack (WITH STACK) and nested loops: the recursive part processes rows one at a time.
NOT IN over a nullable column adds NULL checks to the plan; NOT EXISTS gives a single anti semi join.
Cost
A CTE with three references reads the tables three times. A temp table is computed once and has statistics, while a table variable has no column statistics.
Recursion without an index on ParentId reads the table on every iteration.
Trade-offs
Storing a hierarchy
Pro
Con
ParentId and a recursive CTE
simple, easy to change
traversal is expensive
hierarchyid
a subtree is an index range
moving a branch is expensive
Closure table
ancestors and descendants via a plain JOIN
an extra table to maintain on changes
Three-valued logic applies to constraints too: CHECK rejects only FALSE, so NULL passes it. A SQL Server unique index allows one NULL, PostgreSQL allows any number, and since version 15 you can use NULLS NOT DISTINCT.
How to check in production
In the plan, look for the same table read repeatedly by different CTE references.
Searching sys.sql_modules.definition finds suspicious NOT IN (SELECT patterns.
Takeaway: a CTE improves readability but does not cache its result, and NULL in negations is handled with NOT EXISTS or a NOT NULL constraint.
T-SQL
DECLARE @Products TABLE (ProductId intNOT NULL);
DECLARE @Stops TABLE (ProductId intNULL); -- a stop list with an "empty" row
INSERT @Products VALUES (1), (2), (3);
INSERT @Stops VALUES (2), (NULL);
SELECT ProductId FROM @Products
WHERE ProductId NOTIN (SELECT ProductId FROM @Stops);
-- empty: 1 <> NULL yields UNKNOWN, and the row is dropped
SELECTp.ProductIdFROM @Products AS p
WHERENOTEXISTS (SELECT1FROM @Stops AS s WHEREs.ProductId=p.ProductId);
-- 1, 3
T-SQL
-- All subcategories of "Food" with their nesting level
A window function is a function with OVER (...). It sees a “window” of rows around the current one and returns a value for each row.
Function
What it does
ROW_NUMBER()
row number: 1, 2, 3
RANK(), DENSE_RANK()
position, taking ties into account
LAG(), LEAD()
value from the previous or next row
SUM() OVER (...)
sum over the window, for example a running one
How to use
PARTITION BY StoreId — compute separately for each store.
ORDER BY SoldOn — the order of rows within a store.
LAG(Amount) OVER (PARTITION BY StoreId ORDER BY SoldOn) — the previous day’s revenue next to the current one.
Common mistake
Writing WHERE ROW_NUMBER() OVER (...) = 1. Window functions are allowed only in SELECT and ORDER BY, so you number rows in a CTE or subquery and filter outside.
Takeaway: a window function adds a value computed over its window to each row and does not reduce the number of rows.
How it works
PARTITION BY divides rows into partitions; the calculation restarts in each one.
ORDER BY sets the order within a partition.
The frame (ROWS or RANGE) selects rows relative to the current one. If there is an ORDER BY but no frame, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW applies.
Ties
Function
For revenue 900, 700, 700, 300
ROW_NUMBER
1, 2, 3, 4 — order among ties is undefined
RANK
1, 2, 2, 4 — with a gap
DENSE_RANK
1, 2, 2, 3 — no gap
When to choose what
Top-N per group and removing duplicates — ROW_NUMBER in a CTE and a filter on rn in the outer query.
“Everyone who shares a podium place” — RANK or DENSE_RANK.
Change versus the previous period — LAG; a running total — SUM(...) OVER (... ROWS UNBOUNDED PRECEDING).
Pitfalls
Default RANGE. Rows with the same ORDER BY key enter the frame together: two receipts on the same day get the same “running” total.
LAST_VALUE. With the default frame it returns the current row. You need ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
Nondeterminism.ROW_NUMBER over a non-unique key numbers ties arbitrarily — add a unique column to ORDER BY.
Rule of thumb
For running totals, always spell out the frame: ROWS UNBOUNDED PRECEDING.
Takeaway: a window is a partition, an order and a frame; almost all mistakes come from the default frame and a non-unique sort.
Under the hood
Window functions are evaluated at the SELECT step, after GROUP BY and HAVING, so they work on top of aggregates: SUM(SUM(Amount)) OVER () puts the total revenue next to each store’s revenue.
The plan needs input sorted by PARTITION BY and ORDER BY: either a Sort operator or an index in the right order.
In row mode the frame is held by a Window Spool: for ROWS with a frame under 10,000 rows it is in memory, for RANGE and large frames it is a worktable in tempdb.
In batch mode, aggregates are computed by Window Aggregate with no spool. Batch mode kicks in with columnstore, and since SQL Server 2019 also on regular tables at compatibility level 150.
Cost
Sorting a large input means a memory grant and a risk of spilling to tempdb.
RANGE instead of ROWS in a running total over a unique key gives the same result but many times more reads.
Trade-offs
A POC index (partition, order, covering: key from the PARTITION BY and ORDER BY columns, the rest in INCLUDE) removes the sort but makes writes more expensive.
Top-1 per group via CROSS APPLY (SELECT TOP (1) ...) wins when there are few groups and an index; ROW_NUMBER wins when there are many groups.
SQL Server 2022 added WINDOW (compatibility level 160) and IGNORE NULLS for FIRST_VALUE, LAST_VALUE, LAG, LEAD. RANGE with a numeric offset exists in PostgreSQL 11+ but not in SQL Server.
How to check in production
In the actual plan: a Sort before the Window Spool, the execution mode (Batch or Row), spill warnings.
SET STATISTICS IO ON: heavy Worktable reads point to RANGE or a wide frame.
Takeaway: a window function costs a sort and a spool; an explicit ROWS frame, a POC index and batch mode make it cheaper than self-joins and cursors.
WHERE Rn <=2; -- a window function cannot be used in the WHERE of the inner query
-- 1 Milk 900 1 1 1
-- 1 Bread 700 2 2 2 -- Eggs is cut: Rn = 3 although Rnk = 2; Tea has Rnk = 4, DenseRnk = 3
-- 2 Coffee 650 1 1 1
-- 2 Milk 400 2 2 2
Likely follow-ups
How does RANK differ from DENSE_RANK? — RANK skips positions after ties, DENSE_RANK does not.
Why is the running total the same on two rows? — The default RANGEframe groups rows with equal sort keys.
How do you remove duplicates, keeping one row each? — ROW_NUMBER() OVER (PARTITION BY key ...) in a CTE and a DELETE from the CTE where the number is greater than 1.
How do you find continuous sales periods (gaps and islands)? — The difference between the date and ROW_NUMBER is constant within a period; group by it.
Can you filter on a window function in WHERE? — No, only in an outer query: windows are computed at the SELECT step.
A business operation that changes data — a procedure.
A reusable selection with a parameter — a table-valued function; without a parameter — a view.
Auditing and checks that a constraint cannot express — a trigger.
Common mistake
Assuming a trigger fires once per row. In SQL Server it fires once per statement, and the inserted and deleted tables hold all changed rows. SELECT @id = Id FROM inserted silently handles only one of them.
Takeaway: a procedure changes data, functions and views feed data to queries, and a trigger reacts to changes.
How it works
Inline TVF — a single RETURN (SELECT ...) expression. The optimizer substitutes it into the query like a view and sees indexes and statistics.
Multi-statement TVF — fills a table variable with several statements. The optimizer estimates it at 100 rows, and before SQL Server 2014 at one.
Scalar UDF — before SQL Server 2019 it runs once per row and makes the whole plan single-threaded.
Procedure — compiled on first call; the plan is cached and reused.
Error handling
At the start of the procedure — SET XACT_ABORT, NOCOUNT ON.
BEGIN TRY, BEGIN TRANSACTION, the changes, COMMIT.
In BEGIN CATCH — IF @@TRANCOUNT > 0 ROLLBACK; and THROW;.
Without XACT_ABORT, some errors roll back only one statement, the transaction continues, and you can commit half an operation.
Pitfalls
THROW honors XACT_ABORT, RAISERROR does not. New code uses THROW, and the statement before it ends with ;.
TRY/CATCH does not catch compile errors and name resolution errors at the same level.
An AFTER trigger runs in the same transaction: a ROLLBACK inside it undoes the original statement too.
Rule of thumb
Return a table from a function only through an inline TVF. Rewrite a multi-statement one as an inline TVF or a procedure with a temp table.
Takeaway: inline TVFs and views are transparent to the optimizer, scalar and multi-statement functions are not; a procedure without XACT_ABORT and TRY/CATCH is unsafe.
Under the hood
Before SQL Server 2019, a scalar UDF is interpreted, row-by-row execution. The optimizer barely accounts for its cost, and parallelism is disallowed.
SQL Server 2019 at compatibility level 150 turns an eligible UDF into a subquery inside the plan. Functions with GETDATE(), table variables, CTEs, multiple RETURN statements and a number of other constructs are not inlined; the list of restrictions grew in cumulative updates.
Since SQL Server 2017 (level 140), interleaved execution first materializes a multi-statement TVF and then optimizes the rest of the query on the actual row count. It works only in read-only queries.
Cost
SET STATISTICS IO does not show reads inside a scalar UDF: the query looks cheap but takes minutes.
A trigger extends the transaction of every write to the table and hides logic from whoever reads the procedure.
Trade-offs
An inline TVF gives reuse without losing transparency, but only a single SELECT fits inside.
An indexed view stores its result and speeds up aggregates but makes every write to the base tables more expensive.
Change history is kept with temporal tables (SQL Server 2016+) instead of triggers, and change export with Change Tracking or CDC.
In PostgreSQL the main unit is the function; procedures with COMMIT inside appeared in version 11, and triggers are FOR EACH ROW or FOR EACH STATEMENT.
How to check in production
sys.dm_exec_function_stats (SQL Server 2016+) shows the call count and CPU of every scalar function that was not inlined.
Transactions left open after timeouts show up in sys.dm_tran_session_transactions and DBCC OPENTRAN.
Takeaway: choose the object by whether the optimizer can see inside it, and XACT_ABORT ON closes the hole left by client timeouts.
Why is a scalar UDF in SELECT slow? — Before SQL Server 2019 it runs once per row, prevents parallelism and is invisible in STATISTICS IO.
How does an inline TVF differ from a multi-statement one? — The inline one expands into the plan; the multi-statement one fills a table variable with a fixed row estimate.
How many times does a trigger fire on an UPDATE of a thousand rows? — Once; all rows are in inserted and deleted.
Why SET XACT_ABORT ON if you have TRY/CATCH? — CATCH does not catch a client timeout, while XACT_ABORT rolls the transaction back.
Why is THROW better than RAISERROR? — Without arguments it rethrows the original error, and it honors XACT_ABORT.
Row by row — a cursor or a WHILE loop over rows, or in C# a loop that issues a separate UPDATE per row.
Set-based — one statement over a set: UPDATE ... FROM ... JOIN, INSERT ... SELECT, MERGE.
Why
Every statement has a fixed cost: a plan, locks, a commit. Row-by-row code pays it on every row, and from an application it adds a network call: 100,000 calls at 1 ms each are 100 seconds of network alone.
How to use
Reprice products from a price list — UPDATE p SET Price = n.Price FROM Products AS p JOIN NewPrices AS n ON ....
Pass a list of rows from C# — a table-valued parameter, in one call.
Common mistake
A cursor out of procedural-language habit: “for each product, find the price and update it”. Almost any such loop is one join and one UPDATE.
Takeaway: think in sets: one statement over a set instead of a loop over rows.
How it works
A cursor keeps a position in a result set and runs the loop body on every row. If you really need one, declare it LOCAL FAST_FORWARD — forward-only and read-only.
MERGE matches a source against a target table in one pass and runs UPDATE, INSERT or DELETE per WHEN branch. OUTPUT $action shows what was done to each row.
A TVP is a parameter of a table type (CREATE TYPE ... AS TABLE); in a procedure it is declared READONLY.
MERGE pitfalls
Two source rows for one target row is an error: a row cannot be updated twice. The source must be unique by key.
ON holds only the matching key. Filtering the target in ON gives wrong results; filter in the WHEN ... AND branches.
Without WITH (HOLDLOCK), concurrent upserts of the same key hit primary key violations.
MERGE must end with ; (error 10713) and does not get simple parameterization, so replace literals with parameters.
When to choose what
Volume from .NET
Method
A few values
regular parameters
Up to thousands of rows
TVP or JSON with OPENJSON (SQL Server 2016+)
Hundreds of thousands of rows
SqlBulkCopy into a staging table, then set-based
Rule of thumb
A cursor only where each row needs a separate call: a procedure, dynamic SQL across databases. Everything else — one statement or batches.
Takeaway: set-based means one statement over a set; MERGE and TVPs help, but each has its own safety rules.
Under the hood
Every commit waits for the log to be written to disk. A row-by-row INSERT in autocommit means N log flushes and WRITELOG waits; one statement means one.
Set-based code gives the optimizer choices: a hash join instead of N index seeks, parallelism, batch mode.
If one statement takes 5,000 or more locks on a single table or index, SQL Server tries to replace them with a lock on the whole table.
Trade-offs
Approach
Pro
Con
One statement
minimal overhead
long transaction, log growth, escalation
Batches by key
short locks, the log gets freed
more code, no overall atomicity
Row by row
control over each row
overhead and a round trip per row
A batch is selected by a key range or by TOP (N) with a condition on an index. Otherwise each batch rescans the table.
In the FULL recovery model, only log backups free the log between batches.
Upsert without MERGE: UPDATE ... WITH (UPDLOCK, SERIALIZABLE), and if @@ROWCOUNT = 0 then INSERT, all in one transaction. PostgreSQL has the atomic INSERT ... ON CONFLICT for this, and MERGE since version 15.
A TVP has no column statistics: for large volumes, copy the data into a temp table.
How to check in production
Query Store or sys.dm_exec_query_stats: a query with millions of executions at a fraction of a millisecond each is row-by-row processing from an application.
Frequent WRITELOG waits mean small commits; sys.dm_exec_cursors shows open cursors.
Takeaway: set-based processing removes per-row overhead, and batching keeps one statement from becoming everyone else’s problem.
T-SQL
CREATETABLE #SalesLog (Id intIDENTITYPRIMARY KEY, SoldAt datetime2NOT NULL);