SQL SET Operations, PIVOT, UNPIVOT, Dynamic SQL, and Cursors: Combining Results, Reshaping Data, and Advanced Patterns

You know how to query one table. You know how to JOIN two tables. But what if you need to STACK results from two queries on top of each other? What if you need to turn rows into columns for a report? What if you need to build a query dynamically at runtime?

These are the advanced patterns that complete your SQL toolkit: SET operations (UNION, INTERSECT, EXCEPT) combine result sets vertically. PIVOT/UNPIVOT reshape data between rows and columns. Dynamic SQL builds queries as strings and executes them. Cursors process rows one at a time (and why you should almost never use them).

Think of SET operations like combining playlists. UNION merges two playlists and removes duplicate songs. UNION ALL merges them but keeps duplicates. INTERSECT keeps only songs that appear in BOTH playlists. EXCEPT keeps songs that are in playlist A but NOT in playlist B. Same songs (rows), different combination rules.

Table of Contents

• The Sample Data
• UNION — Combine and Remove Duplicates
• UNION ALL — Combine and Keep Duplicates
• INTERSECT — Only in Both
• EXCEPT — In First But Not Second
• UNION vs JOIN: Different Operations
• SET Operations Rules
• Real-World SET Operation Patterns
• PIVOT — Turn Rows into Columns
• UNPIVOT — Turn Columns into Rows
• PIVOT Without the PIVOT Keyword (CASE WHEN)
• Dynamic SQL — Building Queries at Runtime
• EXEC vs sp_executesql
• Dynamic SQL Use Cases
• SQL Injection Warning
• Cursors — Row-by-Row Processing
• Why Cursors Are Almost Always Wrong
• When Cursors Are Acceptable
• Common Mistakes
• Interview Questions
• Wrapping Up

The Sample Data

-- Current employees
CREATE TABLE employees (
    emp_id INT, name VARCHAR(50), department VARCHAR(50), salary DECIMAL(10,2), city VARCHAR(50)
);
INSERT INTO employees VALUES
(1001, 'Naveen', 'Engineering', 105000, 'Toronto'),
(1002, 'Shrey', 'Engineering', 95000, 'Toronto'),
(1003, 'Vrushab', 'Engineering', 92000, 'Vancouver'),
(1004, 'Vishnu', 'Analytics', 91000, 'Ottawa'),
(1005, 'Ravi', 'Analytics', 85000, 'Calgary'),
(1006, 'Priya', 'Analytics', 85000, 'Toronto');

-- Contractors (some overlap with employees)
CREATE TABLE contractors (
    contractor_id INT, name VARCHAR(50), department VARCHAR(50), rate DECIMAL(10,2), city VARCHAR(50)
);
INSERT INTO contractors VALUES
(2001, 'Anita', 'Sales', 75, 'Mumbai'),
(2002, 'Deepak', 'Engineering', 85, 'Delhi'),
(2003, 'Naveen', 'Engineering', 90, 'Toronto'),   -- Same name/dept/city as employee!
(2004, 'Kavya', 'Analytics', 70, 'Pune');

-- Monthly sales data
CREATE TABLE monthly_sales (
    year INT, month VARCHAR(20), region VARCHAR(50), revenue DECIMAL(12,2)
);
INSERT INTO monthly_sales VALUES
(2025, 'January', 'East', 150000), (2025, 'January', 'West', 120000),
(2025, 'February', 'East', 165000), (2025, 'February', 'West', 135000),
(2025, 'March', 'East', 180000), (2025, 'March', 'West', 140000),
(2026, 'January', 'East', 175000), (2026, 'January', 'West', 145000),
(2026, 'February', 'East', 190000), (2026, 'February', 'West', 155000);

UNION — Combine and Remove Duplicates

UNION stacks two result sets vertically and removes duplicate rows:

-- All people (employees + contractors), no duplicates
SELECT name, department, city FROM employees
UNION
SELECT name, department, city FROM contractors;

name	department	city
Anita	Sales	Mumbai
Deepak	Engineering	Delhi
Kavya	Analytics	Pune
Naveen	Engineering	Toronto
Priya	Analytics	Toronto
Ravi	Analytics	Calgary
Shrey	Engineering	Toronto
Vishnu	Analytics	Ottawa
Vrushab	Engineering	Vancouver

Notice: “Naveen, Engineering, Toronto” appears only ONCE even though it exists in both tables. UNION removes the duplicate.

Real-life analogy: UNION is like merging two guest lists for a party. If someone is on both lists, they get ONE invitation, not two. The final list has no duplicates.

UNION ALL — Combine and Keep Duplicates

UNION ALL stacks results WITHOUT removing duplicates — faster because no deduplication:

SELECT name, department, city FROM employees
UNION ALL
SELECT name, department, city FROM contractors;

Now “Naveen, Engineering, Toronto” appears TWICE — once from each table. UNION ALL keeps everything.

UNION vs UNION ALL Performance

UNION:     Combines → sorts → deduplicates → returns (SLOWER)
UNION ALL: Combines → returns (FASTER — no sort/dedup step)

Rule: Use UNION ALL unless you specifically need deduplication. It is significantly faster on large datasets.

INTERSECT — Only in Both

INTERSECT returns rows that appear in BOTH result sets:

-- People who exist in both employees AND contractors (by name, dept, city)
SELECT name, department, city FROM employees
INTERSECT
SELECT name, department, city FROM contractors;

name	department	city
Naveen	Engineering	Toronto

Only Naveen appears in both tables with the same name, department, and city.

Real-life analogy: INTERSECT is like comparing two party guest lists and keeping only people who are on BOTH lists. The overlap.

EXCEPT — In First But Not Second

EXCEPT returns rows from the first query that do NOT appear in the second:

-- Employees who are NOT also contractors
SELECT name, department, city FROM employees
EXCEPT
SELECT name, department, city FROM contractors;

name	department	city
Priya	Analytics	Toronto
Ravi	Analytics	Calgary
Shrey	Engineering	Toronto
Vishnu	Analytics	Ottawa
Vrushab	Engineering	Vancouver

Naveen is excluded because he appears in both tables. Everyone else is employee-only.

-- Contractors who are NOT also employees
SELECT name, department, city FROM contractors
EXCEPT
SELECT name, department, city FROM employees;

name	department	city
Anita	Sales	Mumbai
Deepak	Engineering	Delhi
Kavya	Analytics	Pune

Order matters! A EXCEPT B is different from B EXCEPT A.

Real-life analogy: EXCEPT is like comparing your contact list with a blocked list. “Show me everyone in my contacts who is NOT on the blocked list.” The direction matters — blocking works one way.

UNION vs JOIN: Different Operations

This confuses everyone. They are completely different:

UNION: Stacks rows VERTICALLY (combines two result sets into one taller result)
  Query A: 10 rows, 3 columns
  Query B: 8 rows, 3 columns
  UNION:   up to 18 rows, 3 columns (same columns, more rows)

JOIN: Combines columns HORIZONTALLY (adds columns from another table)
  Table A: 10 rows, 3 columns
  Table B: 8 rows, 4 columns
  JOIN:    up to 10 rows, 7 columns (more columns, same/fewer rows)

Real-life analogy: UNION is stacking two spreadsheets with the same columns on top of each other (more rows). JOIN is placing two spreadsheets side by side and matching rows (more columns).

SET Operations Rules

1. Same number of columns — both queries must return the same number of columns
2. Compatible data types — corresponding columns must have compatible types (INT with INT, VARCHAR with VARCHAR)
3. Column names from first query — the result uses column names from the FIRST SELECT
4. ORDER BY goes at the end — only ONE ORDER BY, after the last query

-- ❌ FAILS: Different number of columns
SELECT name, department, salary FROM employees
UNION
SELECT name, department FROM contractors;
-- ERROR: must have same number of columns

-- ❌ FAILS: Incompatible types
SELECT name, salary FROM employees
UNION
SELECT name, city FROM contractors;
-- ERROR: salary (DECIMAL) vs city (VARCHAR) — incompatible

-- ✅ CORRECT: Same columns, ORDER BY at end
SELECT name, department, city FROM employees
UNION ALL
SELECT name, department, city FROM contractors
ORDER BY department, name;

Real-World SET Operation Patterns

Pattern 1: Combine Data from Multiple Sources

-- All customer interactions (from 3 different source systems)
SELECT customer_id, interaction_date, 'Phone' AS channel FROM phone_calls
UNION ALL
SELECT customer_id, interaction_date, 'Email' AS channel FROM emails
UNION ALL
SELECT customer_id, interaction_date, 'Chat' AS channel FROM chat_messages
ORDER BY customer_id, interaction_date;

Pattern 2: Find Missing Records (Data Quality)

-- Products in orders but NOT in the product catalog (orphan references)
SELECT DISTINCT product_id FROM order_items
EXCEPT
SELECT product_id FROM products;
-- Returns product_ids that exist in orders but not in the product table — data quality issue!

Pattern 3: Compare Two Tables (Reconciliation)

-- Rows in source but NOT in target (missing from target)
SELECT customer_id, name, email FROM source_customers
EXCEPT
SELECT customer_id, name, email FROM target_customers;
-- Shows exactly what is missing or different between environments

Pattern 4: Combine with Constants for Categories

-- All people with their type
SELECT name, department, 'Employee' AS type, salary AS compensation FROM employees
UNION ALL
SELECT name, department, 'Contractor' AS type, rate * 2080 AS compensation FROM contractors
ORDER BY compensation DESC;

PIVOT — Turn Rows into Columns

PIVOT transforms row values into column headers — turning a long, narrow table into a wide report:

-- Before PIVOT (rows):
-- | region | month    | revenue |
-- | East   | January  | 150000  |
-- | East   | February | 165000  |
-- | West   | January  | 120000  |
-- | West   | February | 135000  |

-- After PIVOT (columns):
-- | region | January | February | March  |
-- | East   | 150000  | 165000   | 180000 |
-- | West   | 120000  | 135000   | 140000 |

-- SQL Server PIVOT syntax
SELECT region, [January], [February], [March]
FROM (
    SELECT region, month, revenue
    FROM monthly_sales
    WHERE year = 2025
) AS source_data
PIVOT (
    SUM(revenue)
    FOR month IN ([January], [February], [March])
) AS pivot_table;

Real-life analogy: PIVOT is like turning a vertical attendance list (Name, Date, Present) into a horizontal attendance sheet with dates as column headers and checkmarks in cells. Same data, different shape.

UNPIVOT — Turn Columns into Rows

UNPIVOT does the reverse — turns columns back into rows:

-- Before UNPIVOT (wide):
-- | region | q1_revenue | q2_revenue | q3_revenue |
-- | East   | 495000     | 520000     | 480000     |
-- | West   | 395000     | 410000     | 390000     |

-- After UNPIVOT (narrow):
-- | region | quarter    | revenue |
-- | East   | q1_revenue | 495000  |
-- | East   | q2_revenue | 520000  |
-- | East   | q3_revenue | 480000  |
-- | West   | q1_revenue | 395000  |

SELECT region, quarter, revenue
FROM quarterly_report
UNPIVOT (
    revenue FOR quarter IN (q1_revenue, q2_revenue, q3_revenue)
) AS unpivot_table;

PIVOT Without the PIVOT Keyword (CASE WHEN)

This works in ALL databases (not just SQL Server) and is more flexible:

-- CASE WHEN pivot (works everywhere)
SELECT region,
    SUM(CASE WHEN month = 'January' THEN revenue ELSE 0 END) AS January,
    SUM(CASE WHEN month = 'February' THEN revenue ELSE 0 END) AS February,
    SUM(CASE WHEN month = 'March' THEN revenue ELSE 0 END) AS March
FROM monthly_sales
WHERE year = 2025
GROUP BY region;

Same result as PIVOT but works in PostgreSQL, MySQL, and any SQL database. This is the pattern we used in our GROUP BY post — conditional aggregation.

Dynamic SQL — Building Queries at Runtime

Dynamic SQL builds a query as a string and executes it. Useful when table names, column names, or WHERE conditions are not known until runtime.

EXEC — Simple Execution

-- Basic dynamic SQL
DECLARE @table_name VARCHAR(100) = 'employees';
DECLARE @sql NVARCHAR(MAX);

SET @sql = 'SELECT * FROM ' + @table_name + ' WHERE salary > 90000';
EXEC(@sql);
-- Executes: SELECT * FROM employees WHERE salary > 90000

sp_executesql — Parameterized (Safer)

-- Parameterized dynamic SQL (prevents SQL injection)
DECLARE @department VARCHAR(50) = 'Engineering';
DECLARE @min_salary DECIMAL(10,2) = 90000;
DECLARE @sql NVARCHAR(MAX);

SET @sql = N'SELECT * FROM employees WHERE department = @dept AND salary > @sal';

EXEC sp_executesql @sql,
    N'@dept VARCHAR(50), @sal DECIMAL(10,2)',
    @dept = @department,
    @sal = @min_salary;

Always use sp_executesql over EXEC — it parameterizes inputs, preventing SQL injection and improving query plan reuse.

Dynamic SQL Use Cases

-- Dynamic PIVOT (unknown number of months)
DECLARE @columns NVARCHAR(MAX) = '';
DECLARE @sql NVARCHAR(MAX);

-- Build column list dynamically
SELECT @columns = @columns + QUOTENAME(month) + ','
FROM (SELECT DISTINCT month FROM monthly_sales) AS months;
SET @columns = LEFT(@columns, LEN(@columns) - 1);  -- Remove trailing comma

SET @sql = N'
    SELECT region, ' + @columns + '
    FROM (SELECT region, month, revenue FROM monthly_sales WHERE year = 2025) src
    PIVOT (SUM(revenue) FOR month IN (' + @columns + ')) pvt';

EXEC sp_executesql @sql;

SQL Injection Warning

-- ❌ DANGEROUS: User input directly concatenated
DECLARE @user_input VARCHAR(100) = 'Engineering; DROP TABLE employees; --';
EXEC('SELECT * FROM employees WHERE department = ''' + @user_input + '''');
-- Attacker just DROPPED your table!

-- ✅ SAFE: Parameterized query
EXEC sp_executesql
    N'SELECT * FROM employees WHERE department = @dept',
    N'@dept VARCHAR(100)',
    @dept = @user_input;
-- The input is treated as data, not code — no injection possible

Cursors — Row-by-Row Processing

A cursor lets you process query results ONE ROW AT A TIME:

-- Cursor example: process each employee individually
DECLARE @name VARCHAR(50), @salary DECIMAL(10,2);

DECLARE emp_cursor CURSOR FOR
    SELECT name, salary FROM employees WHERE department = 'Engineering';

OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @name, @salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @name + ': $' + CAST(@salary AS VARCHAR);
    -- Do something with each row...
    FETCH NEXT FROM emp_cursor INTO @name, @salary;
END;

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

Why Cursors Are Almost Always Wrong

SQL is designed for SET-BASED operations (process all rows at once). Cursors force ROW-BY-ROW processing — dramatically slower:

-- ❌ CURSOR: 10,000 individual UPDATE statements (slow)
DECLARE emp_cursor CURSOR FOR SELECT emp_id FROM employees;
OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE employees SET salary = salary * 1.10 WHERE emp_id = @id;
    FETCH NEXT FROM emp_cursor INTO @id;
END;
-- Time: 30 seconds for 10,000 rows

-- ✅ SET-BASED: 1 UPDATE statement (fast)
UPDATE employees SET salary = salary * 1.10;
-- Time: 0.1 seconds for 10,000 rows

The rule: If you can do it with a single SQL statement, NEVER use a cursor.

When Cursors Are Acceptable

• Sending emails for each row (cannot batch)
• Calling a stored procedure per row (no set-based alternative)
• Complex row-dependent logic where each row’s processing depends on the previous row’s result
• Administrative tasks (rebuilding indexes per table)

Even then, consider WHILE loops with temp tables as a faster alternative.

Common Mistakes

1. Using UNION when UNION ALL is sufficient — UNION sorts and deduplicates, which is slower. If you know there are no duplicates (or duplicates are acceptable), use UNION ALL.

2. Different column counts in UNION — both queries must return the same number of columns with compatible types.

3. Hardcoding PIVOT columns — if months or categories change, hardcoded PIVOT breaks. Use dynamic SQL for flexible PIVOTs.

4. Using EXEC instead of sp_executesql — EXEC is vulnerable to SQL injection. Always parameterize with sp_executesql.

5. Using cursors for batch updates — set-based UPDATE/DELETE is 100-1000x faster. Cursors are the last resort, not the first choice.

Interview Questions

Q: What is the difference between UNION and UNION ALL? A: UNION combines results and removes duplicates (slower — requires sort + dedup). UNION ALL combines results and keeps all rows including duplicates (faster — no dedup step). Use UNION ALL unless you specifically need deduplication.

Q: What is the difference between UNION and JOIN? A: UNION stacks rows vertically — same columns, more rows. JOIN combines columns horizontally — more columns, matched rows. UNION requires same column count and types. JOIN requires a matching key.

Q: What is EXCEPT and when would you use it? A: EXCEPT returns rows from the first query that do not appear in the second query. Common use: finding missing records (“products in orders but not in the catalog”), data reconciliation (“rows in source but not in target”), and data quality checks.

Q: What is PIVOT and how does the CASE WHEN alternative work? A: PIVOT transforms row values into column headers. The CASE WHEN alternative uses SUM(CASE WHEN column = 'value' THEN amount ELSE 0 END) AS value inside GROUP BY. CASE WHEN is portable (works in all databases) while PIVOT syntax is SQL Server specific.

Q: Why should you avoid cursors? A: Cursors process rows one at a time, while SQL is optimized for set-based operations (all rows at once). A cursor UPDATE on 10,000 rows runs 10,000 individual statements. A set-based UPDATE runs 1 statement. The performance difference is typically 100-1000x. Use cursors only when there is no set-based alternative.

Wrapping Up

SET operations complete your ability to combine data from multiple sources. PIVOT/UNPIVOT reshape data for reporting. Dynamic SQL adds flexibility for runtime-determined queries. And cursors exist for the rare cases where row-by-row processing is the only option.

The key rules: UNION ALL over UNION for performance. CASE WHEN over PIVOT for portability. sp_executesql over EXEC for safety. And set-based over cursors for everything.

Related posts: – SQL Joins – GROUP BY, HAVING, CASE WHEN – Stored Procedures & Functions – SQL Functions

---

Naveen Vuppula is a Senior Data Engineering Consultant and app developer based in Ontario, Canada. He writes about Python, SQL, AWS, Azure, and everything data engineering at DriveDataScience.com.