Skip to content
Tutorials Center
My learning

Topics

Lesson 16 of 39 ยท Joins, CTEs and Window Functions

Set Operators and Subqueries in SQL Server

Combine result sets with UNION, UNION ALL, INTERSECT and EXCEPT, and nest queries with scalar, IN, EXISTS and correlated subqueries and derived tables in T-SQL.

Joins combine tables side by side. Set operators stack the results of two queries on top of each other, and subqueries put one query inside another so you can answer a question in steps: "customers who spent more than the average", "products never ordered". This lesson covers UNION, UNION ALL, INTERSECT and EXCEPT, then the kinds of subqueries and when EXISTS beats IN.

UNION and UNION ALL

UNION ALL appends the rows of the second query to the first. UNION does the same and then removes duplicates, which requires an extra sort or hash step.

SQL
SELECT first_name, last_name, N'customer' AS role FROM dbo.customers
UNION ALL
SELECT first_name, last_name, N'employee' FROM dbo.employees;

The rules:

  • Both queries need the same number of columns, with compatible data types in matching positions.
  • Column names come from the first query.
  • ORDER BY goes once, at the very end, and can use the first query's column names or positions.
  • Prefer UNION ALL unless you really need to remove duplicates; it is faster and never changes the row count.
SQL UNION ALL versus UNION ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

INTERSECT and EXCEPT

  • INTERSECT returns rows that appear in both results.
  • EXCEPT returns rows from the first result that do not appear in the second.

Both remove duplicates and treat two NULLs as equal (unlike =), which makes them handy for comparing data sets:

SQL
-- Countries where we have customers but no employee offices
SELECT country FROM dbo.customers
EXCEPT
SELECT country FROM dbo.offices;

Precedence: INTERSECT is evaluated before UNION and EXCEPT; use parentheses when you mix them. SQL Server does not have EXCEPT ALL or INTERSECT ALL.

SQL INTERSECT and EXCEPT ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Scalar subqueries

A subquery that returns exactly one value can be used anywhere a value fits: in the select list, the WHERE clause or an expression. If it returns more than one row, SQL Server raises an error; if none, the value is NULL.

SQL
SELECT name, price,
       price - (SELECT AVG(price) FROM dbo.products) AS diff_from_average
FROM dbo.products
WHERE price > (SELECT AVG(price) FROM dbo.products);
SQL Above-average products ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

IN and NOT IN with subqueries

A subquery returning one column can feed IN:

SQL
SELECT first_name FROM dbo.customers
WHERE id IN (SELECT customer_id FROM dbo.orders WHERE status = N'pending');

NOT IN has the notorious NULL problem: if the subquery returns even one NULL, x NOT IN (...) is never true, because comparing with NULL is unknown. Either filter the nulls out (WHERE col IS NOT NULL) or use NOT EXISTS.

EXISTS and NOT EXISTS

EXISTS (subquery) is true if the subquery returns at least one row. It stops at the first match and ignores the select list (SELECT 1 is the convention). It handles NULL safely, so it is the right tool for "has / has no related row":

SQL
SELECT c.id, c.first_name
FROM dbo.customers AS c
WHERE NOT EXISTS (SELECT 1 FROM dbo.orders AS o WHERE o.customer_id = c.id);

This is a correlated subquery: the inner query refers to the outer row (c.id) and is logically evaluated per row, although the optimizer usually turns it into an efficient semi-join or anti-join.

SQL Products never ordered (NOT EXISTS) ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Derived tables

A subquery in the FROM clause is a derived table. It must have an alias, and every column needs a name. Derived tables let you aggregate first and then join or filter the result:

SQL
SELECT c.first_name, t.total_spent
FROM dbo.customers AS c
JOIN (SELECT o.customer_id, SUM(i.quantity * i.unit_price) AS total_spent
      FROM dbo.orders AS o
      JOIN dbo.order_items AS i ON i.order_id = o.id
      GROUP BY o.customer_id) AS t
  ON t.customer_id = c.id
WHERE t.total_spent > 500;

Derived tables cannot be referenced twice in the same query, which is one reason to use a CTE instead. Neither performs better by itself; they are logically equal.

SQL Aggregate first, then join ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

ANY, SOME and ALL

> ALL (subquery) means greater than every value returned; > ANY (or SOME) means greater than at least one. They are less common than EXISTS, but price > ALL (SELECT price FROM ... WHERE category_id = 1) reads naturally.

Common mistakes

  • Using UNION when UNION ALL is enough, paying for a needless duplicate removal.
  • NOT IN with a nullable column. Prefer NOT EXISTS.
  • A scalar subquery that returns several rows, causing error 512 at run time as data grows.
  • Mismatched column order in a UNION: the types line up by position, not by name.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the difference between UNION and UNION ALL?

    UNION performs duplicate elimination, which costs extra work. UNION ALL simply concatenates.
  2. 2. Which operator returns rows in the first result that are not in the second?

    EXCEPT is the T-SQL name. MINUS is Oracle's name for the same operation.
  3. 3. Why can NOT IN return no rows even when matches should exist?

    x NOT IN (1, NULL) can never be true. Use NOT EXISTS or filter out NULLs.
  4. 4. What must a derived table in the FROM clause have?

    Every derived table needs a table alias, and all its columns need names.

Summary

  • UNION ALL appends; UNION also deduplicates; INTERSECT and EXCEPT compare result sets.
  • Set operators need the same column count and compatible types; one final ORDER BY.
  • Scalar subqueries return one value; IN tests membership; EXISTS tests presence and is NULL-safe.
  • Correlated subqueries refer to the outer row; derived tables need aliases.
  • Prefer NOT EXISTS to NOT IN when NULLs are possible.

Next: CTEs and Recursive Queries