SQL Queries and Set Operations

A SQL query is a statement written in SQL that instructs the database to perform an operation — most commonly retrieving data, but also inserting, updating, deleting, or modifying database structure.

Anatomy of a SELECT query (the most common type):

SELECT   department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM     employees
JOIN     departments ON employees.department_id = departments.id
WHERE    hire_date >= '2020-01-01'
GROUP BY department
HAVING   COUNT(*) > 5
ORDER BY avg_salary DESC
LIMIT    10;

How a query is used:

1. Data retrieval: The primary use — pulling specific rows and columns based on conditions
2. Reporting and aggregation: Using GROUP BY, COUNT, SUM, AVG, MIN, MAX to summarize data
3. Data modification: INSERT, UPDATE, DELETE statements also qualify as queries
4. Subqueries and CTEs: Queries nested inside other queries or defined with WITH for readability
5. Joins: Combining data from multiple related tables in a single query

Query types beyond SELECT:

-- DML queries
INSERT INTO orders (customer_id, total) VALUES (1, 150.00);
UPDATE products SET price = price * 1.05 WHERE category = 'Electronics';
DELETE FROM sessions WHERE expired_at < NOW();

-- DDL queries
CREATE TABLE new_table (...);
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

Interview framing: When asked this question, demonstrate that you know queries go beyond SELECT and that you understand the logical execution order of a SELECT statement (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT).

Set operations in SQL combine the result sets of two or more SELECT queries into a single result. All set operations require that both queries return the same number of columns with compatible data types.

The four set operations:

UNION — all rows from both queries, duplicates removed

SELECT city FROM customers
UNION
SELECT city FROM suppliers;

UNION ALL — all rows from both queries, duplicates kept (faster than UNION)

SELECT product_id FROM orders_2024
UNION ALL
SELECT product_id FROM orders_2025;

INTERSECT — only rows that appear in BOTH result sets

SELECT customer_id FROM orders_2024
INTERSECT
SELECT customer_id FROM orders_2025;
-- customers who ordered in both years

EXCEPT (or MINUS in Oracle) — rows from the first query that do NOT appear in the second

SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;
-- customers who have never placed an order

Rules:

• Column count must match between both SELECT statements
• Data types must be compatible (or implicitly convertible)
• Column names in the result come from the first SELECT
• ORDER BY can only appear once, at the very end, and applies to the final result

Performance note: UNION performs a sort/deduplicate step. UNION ALL skips this — use it when you know there are no duplicates or duplicates are acceptable, as it is significantly faster.

MySQL note: MySQL does not support INTERSECT or EXCEPT natively prior to MySQL 8.0.31. Both are available in MySQL 8.0.31+ (released 2022). PostgreSQL, SQL Server, and Oracle all support all four operations.

UNION combines the results of two or more SELECT queries and removes duplicate rows from the final output. UNION ALL keeps duplicates (and is faster).

Basic syntax:

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

Requirements:

• Both SELECT statements must return the same number of columns
• Corresponding columns must have compatible data types
• Column names in the result are taken from the first SELECT

Example — combining customer and supplier cities:

SELECT city, 'Customer' AS source FROM customers
UNION
SELECT city, 'Supplier' AS source FROM suppliers
ORDER BY city;

UNION vs UNION ALL:

-- UNION: removes duplicates (sort + dedup = slower)
SELECT email FROM newsletter_list_a
UNION
SELECT email FROM newsletter_list_b;

-- UNION ALL: keeps duplicates (no dedup = faster)
SELECT email FROM newsletter_list_a
UNION ALL
SELECT email FROM newsletter_list_b;

Ordering the combined result: ORDER BY applies to the final combined result, not to individual queries. Use column position or a name from the first query:

SELECT product_id, 'Q1' AS quarter, revenue FROM q1_sales
UNION ALL
SELECT product_id, 'Q2', revenue FROM q2_sales
ORDER BY product_id, quarter;

Interview tip: Always prefer UNION ALL over UNION when you know there are no duplicates or when duplicates are acceptable — the deduplication step in UNION requires a sort pass over the entire combined result set, which is expensive at scale.

INTERSECT returns only the rows that are present in both result sets. It is the set theory intersection operator — the overlap between two queries.

Syntax:

SELECT column1, column2 FROM table1
INTERSECT
SELECT column1, column2 FROM table2;

Example — customers who placed orders in both 2024 and 2025:

SELECT customer_id FROM orders WHERE YEAR(order_date) = 2024
INTERSECT
SELECT customer_id FROM orders WHERE YEAR(order_date) = 2025;

Key behaviors:

• Duplicates are removed (like UNION, not UNION ALL)
• Column count and compatible types required
• INTERSECT ALL (preserves duplicates) is supported in PostgreSQL and SQL Server 2022+

Database support (important for interviews):

• PostgreSQL: Fully supported, including INTERSECT ALL
• SQL Server: Fully supported since 2005
• Oracle: Supported (uses INTERSECT)
• MySQL: Supported since MySQL 8.0.31 (2022) — older versions must use a JOIN workaround

JOIN equivalent (for databases lacking INTERSECT or for performance):

-- Equivalent to INTERSECT using EXISTS:
SELECT DISTINCT a.customer_id
FROM orders_2024 a
WHERE EXISTS (
    SELECT 1 FROM orders_2025 b WHERE b.customer_id = a.customer_id
);

-- Or using INNER JOIN:
SELECT DISTINCT a.customer_id
FROM orders_2024 a
INNER JOIN orders_2025 b ON a.customer_id = b.customer_id;

Interview note: INTERSECT operates on full rows (all selected columns must match). If you only need matching IDs, EXISTS or INNER JOIN gives more control.

EXCEPT (called MINUS in Oracle) returns rows from the first query that do not appear in the second query — the set difference operation.

Syntax:

SELECT column1, column2 FROM table1
EXCEPT
SELECT column1, column2 FROM table2;

Example — customers who have never placed an order:

SELECT customer_id FROM customers
EXCEPT
SELECT DISTINCT customer_id FROM orders;

Example — products in the catalog but not sold in Q1:

SELECT product_id FROM products
EXCEPT
SELECT DISTINCT product_id FROM sales WHERE quarter = 'Q1';

Key behaviors:

• Returns rows from the first query not matched in the second (order matters)
• Duplicates are removed (like UNION)
• EXCEPT ALL preserves duplicates (PostgreSQL, SQL Server 2022+)

Database support:

• PostgreSQL, SQL Server: EXCEPT and EXCEPT ALL
• Oracle: MINUS (same behavior)
• MySQL: Supported since MySQL 8.0.31

NOT EXISTS / LEFT JOIN equivalent:

-- Equivalent to EXCEPT using NOT EXISTS:
SELECT DISTINCT c.customer_id
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

-- Using LEFT JOIN anti-pattern:
SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

Interview gotcha: The LEFT JOIN ... WHERE IS NULL approach is a common alternative that works in all databases including older MySQL. It is often used in interview questions about finding "orphan" records.

A CTE (Common Table Expression) is a named, temporary result set defined using the WITH keyword at the start of a query. It can be referenced multiple times within the same query.

WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

CTE vs subquery:

Recursive CTE (important for hierarchy traversal):

WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id, 0 AS level
    FROM employees WHERE manager_id IS NULL   -- root (CEO)
    UNION ALL
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

CTEs are supported in all major databases: PostgreSQL 8.4+, MySQL 8.0+, SQL Server 2005+, SQLite 3.35+.