Joins, subqueries and CTEs in SQL Server
Real questions rarely live in one table. “Which customers have never ordered?”, “What did each customer spend?” and “Show the category tree” all combine rows from several tables or several queries. T-SQL gives you four tools for that: joins put tables side by side, subqueries nest one query inside another, common table expressions (CTEs) name an intermediate result, and set operators stack results on top of each other. This page covers each one with the same OrdersPortal data and shows when to pick which.
The samples use the dbo.Customers and dbo.Orders tables and rows defined in Sample data on the querying page: four customers (Chen Wei has no orders) and five orders (order 1004 is cancelled). No database was available while this page was written, so the samples were not run here; the syntax follows the Microsoft Learn T-SQL reference and the expected results were worked out from the sample data and cross-checked with SQLite where the SQL is portable.
Topics on this page
Section titled “Topics on this page”| Topic | Use it when |
|---|---|
| Joins | You need columns from two or more related tables in one row. |
| Subqueries | A condition or value depends on another query. |
| Common table expressions | You want to name a step, reuse it in one statement, or walk a hierarchy. |
| Set operators | You need to stack, intersect or subtract the results of two queries. |
A join matches rows from two tables using a condition in the ON clause, usually a foreign key equal to a primary key. The join type decides what happens to rows without a match:
INNER JOIN(or plainJOIN) keeps only rows that match on both sides.LEFT [OUTER] JOINkeeps every row from the left table and fills the right side withNULLwhere there is no match.RIGHT JOINis the mirror image; most teams rewrite it as aLEFT JOINfor readability.FULL [OUTER] JOINkeeps unmatched rows from both sides, which is useful when reconciling two lists.CROSS JOINreturns every combination of rows (a Cartesian product) and has noONclause; with 4 customers and 5 orders it returns 20 rows.CROSS APPLYandOUTER APPLYare T-SQL operators that run a table expression once per left row, for example “the latest two orders per customer”.
Where you put a condition matters for outer joins. A condition in ON decides which right-hand rows match; the same condition in WHERE runs after the join and removes rows, which can silently turn a LEFT JOIN into an inner join.
-- Every customer with their non-cancelled order count and total, including customers with noneSELECT c.Name, COUNT(o.Id) AS OrderCount, COALESCE(SUM(o.Amount), 0) AS TotalFROM dbo.Customers AS cLEFT JOIN dbo.Orders AS o ON o.CustomerId = c.Id AND o.Status <> 'Cancelled' -- in ON: keeps customers without matching ordersGROUP BY c.Id, c.NameORDER BY c.Id;Expected result:
Name OrderCount TotalAda Lopez 2 200.50Ben Ortiz 1 45.00Chen Wei 0 0.00Dana Fox 1 15.25Moving o.Status <> 'Cancelled' into a WHERE clause would drop Chen Wei, because his o.Status is NULL and NULL <> 'Cancelled' is unknown.
Pitfalls:
INNER JOINwhere you neededLEFT JOIN. Customers without orders disappear from the report and nobody notices until the totals are questioned.COUNT(*)after aLEFT JOIN. It counts theNULL-extended row, so Chen Wei would show 1 order. Count a right-hand column:COUNT(o.Id).- Row multiplication. Joining orders to order lines and to payments in one query multiplies lines by payments. Aggregate each child table first (in a CTE or derived table), then join the totals.
Subqueries
Section titled “Subqueries”A subquery is a SELECT inside another statement. A scalar subquery returns one value and can appear wherever an expression can, for example WHERE Amount > (SELECT AVG(Amount) FROM dbo.Orders). A multi-row subquery feeds IN, ANY or ALL. A derived table is a subquery in FROM and must have an alias. A correlated subquery refers to a column of the outer query, so logically it is evaluated once per outer row; the optimizer often rewrites it as a join.
EXISTS checks only whether the subquery returns any row, so the select list inside it does not matter (SELECT 1 is the convention). It is the safest way to express “has at least one” and, with NOT EXISTS, “has none”. A scalar subquery that returns more than one row fails at run time with error 512.
-- Customers who have never placed an orderSELECT c.NameFROM dbo.Customers AS cWHERE NOT EXISTS (SELECT 1 FROM dbo.Orders AS o WHERE o.CustomerId = c.Id);
-- Orders above the overall average order valueSELECT Id, AmountFROM dbo.OrdersWHERE Amount > (SELECT AVG(Amount) FROM dbo.Orders)ORDER BY Id;Expected result:
NameChen Wei
Id Amount1001 120.001004 300.00The average across all five orders is 112.15, so only orders 1001 and 1004 qualify.
Pitfalls:
NOT INwith a nullable column. If the subquery returns even oneNULL,x NOT IN (…)is never true and the query returns no rows, without an error. UseNOT EXISTS, or filterWHERE col IS NOT NULLinside the subquery.- Scalar subqueries in the select list. One per column, per row, is easy to write and can be slow; a join plus
GROUP BY, orOUTER APPLY, usually reads the child table once. - Error 512. “Subquery returned more than 1 value” means the data broke an assumption. Fix the assumption (add a key or
TOP (1) … ORDER BY), not only the symptom.
Common table expressions
Section titled “Common table expressions”Introduced in SQL Server 2005. A common table expression (CTE) is a named result set defined with WITH name AS (query) and visible only to the single SELECT, INSERT, UPDATE, DELETE or MERGE statement that follows it. Several CTEs can be chained with commas, and a later one can reference an earlier one, which lets you write a query as readable steps instead of nested derived tables. A CTE can also be used in CREATE VIEW.
A CTE is not a temporary table: its result is not stored, and each reference to it is expanded into the query, so referencing an expensive CTE twice can do the work twice. When a statement in a batch precedes the WITH, that statement must end with a semicolon; many people write ;WITH defensively.
A recursive CTE has an anchor member, UNION ALL, and a recursive member that references the CTE itself. It is the standard way to walk hierarchies such as categories, org charts or bills of materials. Recursion stops when the recursive member returns no rows; by default SQL Server stops with an error after 100 levels, which OPTION (MAXRECURSION n) on the outer statement changes (0 means no limit, maximum 32,767).
CREATE TABLE dbo.Categories ( Id INT NOT NULL PRIMARY KEY, Name NVARCHAR(50) NOT NULL, ParentId INT NULL REFERENCES dbo.Categories (Id));INSERT INTO dbo.Categories (Id, Name, ParentId)VALUES (1, N'All products', NULL), (2, N'Kitchen', 1), (3, N'Cookware', 2), (4, N'Garden', 1);
WITH CategoryTree AS ( SELECT Id, Name, ParentId, 1 AS Depth, CAST(Name AS NVARCHAR(400)) AS Path -- anchor member FROM dbo.Categories WHERE ParentId IS NULL UNION ALL SELECT c.Id, c.Name, c.ParentId, t.Depth + 1, CAST(t.Path + N' > ' + c.Name AS NVARCHAR(400)) -- recursive member FROM dbo.Categories AS c JOIN CategoryTree AS t ON c.ParentId = t.Id)SELECT Depth, PathFROM CategoryTreeORDER BY PathOPTION (MAXRECURSION 20);Expected result:
Depth Path1 All products2 All products > Garden2 All products > Kitchen3 All products > Kitchen > CookwarePitfalls:
- Type mismatch between anchor and recursive member. Without the
CASTto the sameNVARCHAR(400), the concatenated path is a different type from the anchor’s column and SQL Server raises error 240 (“Types don’t match between the anchor and the recursive part”). - Cycles. Bad data such as a category that is its own grandparent loops until
MAXRECURSIONstops it. Keep the limit low enough to fail fast, and add a constraint or a path check if cycles are possible. ORDER BYinside a CTE is not allowed unlessTOPorOFFSET/FETCHis also used, and even then it does not order the final result. Sort in the outer query.
Set operators
Section titled “Set operators”Set operators combine the results of two queries vertically. Both queries must return the same number of columns with compatible types, and the column names come from the first query.
UNION ALLappends every row from both queries. It is the cheapest and should be your default.UNIONappends and then removes duplicate rows, which costs a sort or hash.INTERSECTreturns distinct rows present in both results.EXCEPTreturns distinct rows from the first result that are not in the second.
Unlike = comparisons, set operators treat two NULLs as equal when they compare rows. An ORDER BY can appear only once, after the last query, and it sorts the combined result.
-- Which customers ordered in October, November, or both?WITH October AS (SELECT CustomerId FROM dbo.Orders WHERE OrderDate >= '2026-10-01' AND OrderDate < '2026-11-01'), November AS (SELECT CustomerId FROM dbo.Orders WHERE OrderDate >= '2026-11-01' AND OrderDate < '2026-12-01')SELECT 'Both months' AS Bucket, CustomerId FROM October INTERSECT SELECT 'Both months', CustomerId FROM NovemberUNION ALLSELECT 'October only', CustomerId FROM (SELECT CustomerId FROM October EXCEPT SELECT CustomerId FROM November) AS xORDER BY Bucket, CustomerId;Expected result:
Bucket CustomerIdBoth months 2October only 1The operators are evaluated left to right except that INTERSECT binds tighter than UNION and EXCEPT; the derived table around the EXCEPT makes the intended grouping explicit.
Pitfalls:
UNIONby habit. UseUNION ALLunless you really need de-duplication; on large results the difference is a full sort.- Positional matching. Columns are matched by position, not name, so swapping two columns in the second query silently mixes data when the types are compatible.
- Precedence.
A UNION B INTERSECT CmeansA UNION (B INTERSECT C). Add parentheses or derived tables.
Choosing between them
Section titled “Choosing between them”| Question | Prefer | Why |
|---|---|---|
| Columns from a parent and child table | JOIN |
One pass, readable, uses indexes on the keys. |
| “Has at least one” / “has none” | EXISTS / NOT EXISTS |
Stops at the first match and is safe with NULLs. |
| A one-off value for a filter | Scalar subquery | Clear intent; the optimizer evaluates it once if uncorrelated. |
| Several dependent steps in one statement | Chained CTEs | Reads top to bottom; nothing to clean up. |
| A step reused by several statements, or large and referenced twice | Temporary table #Name |
Stored once, can be indexed, has statistics. |
| A hierarchy of unknown depth | Recursive CTE | The only set-based option without procedural loops. |
| Two result lists combined | UNION ALL |
No de-duplication cost. |
Interview questions
Section titled “Interview questions”What is the difference between a condition in ON and the same condition in WHERE for a LEFT JOIN?
In ON it limits which right-hand rows match, and unmatched left rows are still returned with NULLs. In WHERE it filters after the join, so a condition on a right-hand column removes the NULL-extended rows and the query behaves like an inner join.
Why is NOT EXISTS preferred over NOT IN for anti-joins?
NOT IN compares with <> against every value; if any value is NULL the comparison is unknown for every row and nothing is returned. NOT EXISTS only asks whether a matching row exists, so NULLs cannot poison it, and it usually compiles to an efficient anti semi join.
Is a CTE materialized in SQL Server? No. It is expanded into the statement like an inline view, so each reference is evaluated as part of the plan. If an expensive CTE is referenced several times, or the intermediate result needs an index, load it into a temporary table instead.
What happens when a recursive CTE exceeds 100 levels?
The statement fails with error 530 when the default MAXRECURSION of 100 is reached, and the effects of the statement are rolled back. Set OPTION (MAXRECURSION n) on the outer statement to raise or lower the limit; 0 removes it.
Senior scenario: a monthly revenue report joins orders, order lines and refunds and reports revenue three times too high for some customers. How do you find and fix it?
That pattern is row multiplication: each order line is repeated once per refund (or the other way round). Check COUNT(*) against the expected number of lines for one affected order to confirm it. Fix it by aggregating each child table to the order grain first (lines per order in one CTE, refunds per order in another) and then joining the two totals to the orders, so every join is one-to-one or one-to-many from the order. Add a test that compares the report total with SUM over the lines table alone.
Related pages
Section titled “Related pages”- Querying data:
SELECT,WHERE,GROUP BYand the sample data used here. - Functions and stored procedures: table-valued functions that can be used with
CROSS APPLY. - Tables, constraints and indexes: foreign keys and the indexes that make joins fast.
- SQL Server interview questions