Subqueries, CTEs, views and UNION: queries inside queries and reusable results
Some questions need two steps. "Which products cost more than the average?" means first calculating the average, then comparing each product with it. "Who are our top customers, and what did they buy?" needs a list first, then details. Subqueries (queries inside queries) and CTEs (named temporary results) let you solve multi-step questions in one statement. Views save a query under a name so everyone can reuse it, and UNION stacks results on top of each other.
Scalar subqueries: one value
A subquery in brackets that returns a single value can be used like a number:
SELECT Name, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);The database runs the inner query first (average ≈ 2,567), then the outer query compares each price to it.
Use one in SELECT to show the comparison:
SELECT Name,
Price,
ROUND((SELECT AVG(Price) FROM Products)) AS AvgPrice,
Price - ROUND((SELECT AVG(Price) FROM Products)) AS Difference
FROM Products
ORDER BY Difference DESC;The most expensive product, without LIMIT:
SELECT Name, Price FROM Products WHERE Price = (SELECT MAX(Price) FROM Products);Subqueries returning a list: IN and NOT IN
Customers who have placed at least one order:
SELECT Name FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);Customers who have never ordered:
SELECT Name FROM Customers
WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders);Products bought by Nairobi customers (a subquery inside a subquery):
SELECT Name FROM Products
WHERE ProductID IN (
SELECT ProductID FROM Orders
WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE City = 'Nairobi')
);Correlated subqueries
A correlated subquery refers to the outer query's current row, so it runs once per row.
Products that cost more than the average of their own category:
SELECT p.Name, p.Category, p.Price
FROM Products p
WHERE p.Price > (
SELECT AVG(p2.Price) FROM Products p2 WHERE p2.Category = p.Category
);Each customer's most recent order date:
SELECT c.Name,
(SELECT MAX(o.OrderDate) FROM Orders o WHERE o.CustomerID = c.CustomerID) AS LastOrder
FROM Customers c;EXISTS and NOT EXISTS
EXISTS is true if the subquery returns at least one row. It's often clearer and safer than IN/NOT IN:
SELECT c.Name
FROM Customers c
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID);Subqueries in FROM (derived tables)
A subquery in FROM acts as a temporary table. It must have an alias:
SELECT ROUND(AVG(Total), 2) AS AvgUnitsPerCustomer
FROM (
SELECT CustomerID, SUM(Quantity) AS Total
FROM Orders
GROUP BY CustomerID
) AS t;CTEs: WITH
A Common Table Expression names a subquery at the top so the main query reads like steps:
WITH CustomerSpend AS (
SELECT o.CustomerID, SUM(o.Quantity * p.Price) AS Spent
FROM Orders o
JOIN Products p ON p.ProductID = o.ProductID
GROUP BY o.CustomerID
)
SELECT c.Name, cs.Spent
FROM CustomerSpend cs
JOIN Customers c ON c.CustomerID = cs.CustomerID
ORDER BY cs.Spent DESC;Several CTEs, each using the ones before:
WITH Spend AS (
SELECT o.CustomerID, SUM(o.Quantity * p.Price) AS Spent
FROM Orders o JOIN Products p ON p.ProductID = o.ProductID
GROUP BY o.CustomerID
),
Average AS (
SELECT AVG(Spent) AS AvgSpent FROM Spend
)
SELECT c.Name, s.Spent, ROUND(a.AvgSpent) AS AvgSpent,
CASE WHEN s.Spent > a.AvgSpent THEN 'Above average' ELSE 'Below average' END AS Segment
FROM Spend s
JOIN Customers c ON c.CustomerID = s.CustomerID
CROSS JOIN Average a
ORDER BY s.Spent DESC;CTEs make complex reports readable, and they're used heavily in data analysis jobs. A recursive CTE can even generate sequences or walk hierarchies:
WITH RECURSIVE Days(d) AS (
SELECT '2026-09-01'
UNION ALL
SELECT DATE(d, '+1 day') FROM Days WHERE d < '2026-09-07'
)
SELECT d AS Day FROM Days;Views: saved queries
A view is a named, saved query that behaves like a read-only table:
CREATE VIEW OrderDetails AS
SELECT o.OrderID, o.OrderDate, c.Name AS Customer, c.City,
p.Name AS Product, o.Quantity, p.Price,
o.Quantity * p.Price AS LineTotal
FROM Orders o
JOIN Customers c ON c.CustomerID = o.CustomerID
JOIN Products p ON p.ProductID = o.ProductID;
SELECT Customer, Product, LineTotal FROM OrderDetails WHERE City = 'Kisumu';CREATE VIEW OrderDetails AS
SELECT o.OrderID, c.City, o.Quantity * p.Price AS LineTotal
FROM Orders o
JOIN Customers c ON c.CustomerID = o.CustomerID
JOIN Products p ON p.ProductID = o.ProductID;
SELECT City, SUM(LineTotal) AS Revenue FROM OrderDetails GROUP BY City ORDER BY Revenue DESC;Why use views:
- Simplicity: analysts query
OrderDetailswithout writing joins. - Consistency: everyone uses the same definition of "revenue".
- Security: give a user access to a view that hides sensitive columns (like phone numbers) instead of the full table.
A view stores the query, not the data, so it always shows current data. Remove one with DROP VIEW OrderDetails;.
UNION and friends: stacking results
UNION combines the rows of two queries with the same number of columns and compatible types:
SELECT Name, 'Customer' AS Type FROM Customers WHERE City = 'Kisumu'
UNION
SELECT Name, 'Product' AS Type FROM Products WHERE Category = 'Storage';| Operator | Result |
|---|---|
UNION | Rows from both, duplicates removed |
UNION ALL | Rows from both, duplicates kept (faster) |
INTERSECT | Rows in both results |
EXCEPT | Rows in the first but not the second (Oracle: MINUS) |
-- Cities that have customers but no orders
SELECT City FROM Customers
EXCEPT
SELECT c.City FROM Orders o JOIN Customers c ON c.CustomerID = o.CustomerID;Join or subquery?
| Use a JOIN when... | Use a subquery/CTE when... |
|---|---|
| You need columns from both tables in the result | You only need to filter by another table |
| Simple relationships | Multi-step logic (aggregate, then compare) |
| Readability: naming steps with CTEs |
Modern databases often optimise both into the same plan, so choose the clearest one.
Think about it: Write the steps (in words) for: "Show customers who spent more than KSh 5,000 in total, with their city."Show answer
Step 1: a CTE that totals each customer's spending (Orders JOIN Products, GROUP BY CustomerID). Step 2: join it to Customers for name and city. Step 3: filter WHERE Spent > 5000 (or use HAVING in step 1).
Summary
- Scalar subqueries return one value; IN/NOT IN subqueries return a list (beware NULL with NOT IN).
- Correlated subqueries run per row; EXISTS/NOT EXISTS test for matching rows.
- Subqueries in FROM need an alias; CTEs (WITH) name steps and make complex queries readable; recursive CTEs generate sequences.
- Views save queries for simplicity, consistency and security.
- UNION, UNION ALL, INTERSECT and EXCEPT combine results with matching columns.
Check yourself
Which keyword starts a CTE?
Show answer
WITH
Which operator combines two results and removes duplicates?
Show answer
UNION
Which keeps duplicates: UNION or UNION ALL?
Show answer
UNION ALL
A saved query that behaves like a table is called a what?
Show answer
view
Which keyword tests whether a subquery returns any rows?
Show answer
EXISTS
Which operator returns rows in the first result but not the second (SQLite)?
Show answer
EXCEPT
Exercise
Select the Name of products that cost more than the average price, using a subquery.