Real-World Nested Queries in SQL
Introduction
Real-world nested queries are SQL statements in which one query is placed inside another to solve a business requirement that contains multiple logical questions. The inner query may calculate a benchmark, produce a set of identifiers, test whether a relationship exists, identify a missing relationship, or derive an intermediate result. The outer query uses that result to return the final business records.
Nested queries are common because real applications rarely ask only for raw rows. Teams need employees earning above their department average, customers who never ordered, products that were never purchased, the latest event for every device, test cases that never passed, and API endpoints whose latency exceeds their own history. These questions combine row-level data, group-level facts, and relationship checks.
This tutorial teaches reusable patterns through practical scenarios. It also explains nulls, duplicates, ties, correlation, aggregation scope, temporal filters, alternatives such as joins and window functions, performance, testing, and interview reasoning. The objective is to select a query shape that states the business rule clearly and remains correct as data grows more complex.
What Are Real-World Nested Queries?
A nested query, or subquery, is a SELECT embedded inside another SQL statement. In production work, the nested portion is not used simply to demonstrate syntax. It answers a supporting question required by the outer query. For example, "Which employees earn above average?" first requires the average salary, while "Which customers have no orders?" requires checking the absence of a related order.
The correct nested-query type follows the shape of the supporting answer. A scalar benchmark needs one value. Membership needs a set. Presence needs EXISTS. Absence needs NOT EXISTS. Per-row comparisons often need correlation. Multi-stage reporting may need a derived table or CTE.
Why Nested Queries Matter in Projects
Nested queries keep a supporting rule near the statement that consumes it. They can express dynamic thresholds, avoid unnecessary row multiplication, and make existence or absence logic explicit. They are particularly useful in validation, security, monitoring, reconciliation, and analytical screens.
They are not automatically better than joins. The same requirement may have several correct forms. Good design starts with meaning and cardinality, then considers readability, reuse, optimizer behavior, and measured performance.
Reusable Nested-Query Patterns
| Business question | Typical pattern | Inner result |
|---|---|---|
| Above a benchmark | Scalar aggregate subquery | One value |
| Above group benchmark | Correlated aggregate | One value per outer key |
| Belongs to qualifying group | IN subquery | Set of keys |
| Has a related record | EXISTS | Presence |
| Has no related record | NOT EXISTS | Absence |
| Has every required record | Double NOT EXISTS | No missing requirement |
| Latest or top record per group | Ranked derived table | Rows with sequence |
Sample Business Data
The examples use familiar tables: employees, departments, customers, orders, order items, products, categories, training courses, test cases, test executions, API requests, and transaction records. Keys connect each child row with its parent. Status, amount, date, and tenant columns define the business scope.
Every example must be adapted to the real schema. Include tenant ID when identifiers are unique only within a tenant, version when records are versioned, and effective dates when relationships change over time.
Employees Earning Above Company Average
A non-correlated scalar subquery calculates one company-wide benchmark.
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);
The inner query returns one value, while the outer query may return many employees. If salaries can be null, AVG ignores them. Confirm that this matches the compensation rule.
Employees Earning Above Department Average
The benchmark now depends on the current employee's department, so the scalar subquery is correlated.
SELECT e.employee_id, e.employee_name,
e.department_id, e.salary
FROM employees AS e
WHERE e.salary > (
SELECT AVG(x.salary)
FROM employees AS x
WHERE x.department_id = e.department_id
);
A grouped department summary joined to employees or a window function can express the same rule. Compare readability and plans for the real workload.
Highest and Second-Highest Salaries
The maximum salary is a scalar benchmark. Equality returns every employee tied at the maximum.
SELECT employee_name, salary
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
);
For the second-highest distinct salary, exclude the maximum before calculating another maximum.
SELECT employee_name, salary
FROM employees
WHERE salary = (
SELECT MAX(salary)
FROM employees
WHERE salary < (
SELECT MAX(salary)
FROM employees
)
);
This returns all ties. DENSE_RANK is often clearer when several salary levels are required.
Employees in Qualifying Departments
When several departments may qualify, the inner result is a set and IN expresses membership.
SELECT employee_id, employee_name, department_id
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location = 'Chicago'
);
Using equality would fail if Chicago has more than one department. A join is equally natural when department columns must appear in the result.
Departments Without Employees
Missing relationships are best stated with NOT EXISTS.
SELECT d.department_id, d.department_name
FROM departments AS d
WHERE NOT EXISTS (
SELECT 1
FROM employees AS e
WHERE e.department_id = d.department_id
);
This normally avoids the null problem associated with NOT IN. An index on the employee department key helps the database prove absence efficiently.
Employees Without Managers
If top-level employees store a null manager ID, use IS NULL. If invalid manager IDs are also possible, a nested relationship check exposes orphan references.
SELECT e.employee_id, e.employee_name, e.manager_id
FROM employees AS e
WHERE e.manager_id IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM employees AS m
WHERE m.employee_id = e.manager_id
);
Distinguish intentional root managers from broken references instead of treating both as the same condition.
Employees Earning More Than Their Managers
A correlated scalar subquery retrieves one manager salary when manager ID is unique.
SELECT e.employee_name, e.salary
FROM employees AS e
WHERE e.salary > (
SELECT m.salary
FROM employees AS m
WHERE m.employee_id = e.manager_id
);
A self join is usually preferable when manager name and other manager attributes are also required.
Customers Who Have Ordered
EXISTS qualifies each customer after finding one related order without multiplying the customer by every order.
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
);
This preserves customer grain naturally. A join would require deliberate duplicate handling if customers have many orders.
Customers Who Never Ordered
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
);
Test customers with no child rows, one child row, and many child rows. If orders are tenant-scoped, tenant ID must participate in the correlation.
Completed Orders and Missing Completed Orders
Business filters belong inside the existence query because they define the qualifying relationship.
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.status = 'COMPLETED'
);
Replace EXISTS with NOT EXISTS to find customers with no completed orders. That result includes customers with only pending or cancelled orders and customers with no orders at all. Confirm whether the business wants both groups.
Orders Above Average Value
SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount > (
SELECT AVG(total_amount)
FROM orders
WHERE status = 'COMPLETED'
)
AND status = 'COMPLETED';
Apply the same population definition to both the benchmark and candidate rows unless the business intentionally compares different populations.
Customers Spending Above Average
This requirement compares customer-level totals, so first create one total per customer and then compare it with the average of those totals.
SELECT customer_id, customer_total
FROM (
SELECT customer_id,
SUM(total_amount) AS customer_total
FROM orders
WHERE status = 'COMPLETED'
GROUP BY customer_id
) AS totals
WHERE customer_total > (
SELECT AVG(customer_total)
FROM (
SELECT customer_id,
SUM(total_amount) AS customer_total
FROM orders
WHERE status = 'COMPLETED'
GROUP BY customer_id
) AS benchmark
);
A CTE can name the repeated summary once and improve maintainability.
Customers With More Than One Order
A correlated scalar count is concise:
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE (
SELECT COUNT(*)
FROM orders AS o
WHERE o.customer_id = c.customer_id
) > 1;
A grouped order summary with HAVING COUNT(*) > 1 may be more efficient for broad reporting. Use EXISTS only for presence, not when the actual count threshold matters.
High-Value Completed Orders
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.status = 'COMPLETED'
AND o.total_amount > 1000
);
This is a selective relationship check. An index aligned with customer, status, and amount may help, but column order must reflect the full workload.
Products Never Ordered
SELECT p.product_id, p.product_name
FROM products AS p
WHERE NOT EXISTS (
SELECT 1
FROM order_items AS oi
WHERE oi.product_id = p.product_id
);
This pattern supports cleanup, catalog review, and inventory analysis. Decide whether cancelled orders or historical archives count as purchases.
Products Priced Above Category Average
SELECT p.product_id, p.product_name,
p.category_id, p.price
FROM products AS p
WHERE p.price > (
SELECT AVG(x.price)
FROM products AS x
WHERE x.category_id = p.category_id
);
This is the product equivalent of salary above department average. Category nulls and inactive products need explicit policy.
Customers Who Purchased a Specific Product
A nested existence check follows the relationship from customer to order to item to product.
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
JOIN order_items AS oi
ON oi.order_id = o.order_id
JOIN products AS p
ON p.product_id = oi.product_id
WHERE o.customer_id = c.customer_id
AND p.product_name = 'Laptop'
);
A stable product ID is safer than a display name. Include completed-order status when only completed purchases count.
Customers Who Purchased Expensive Products
The same relationship can filter product price rather than identity. Keep the price source clear: current catalog price may differ from the historical sale price stored in order items.
WHERE EXISTS (
SELECT 1
FROM orders AS o
JOIN order_items AS oi
ON oi.order_id = o.order_id
WHERE o.customer_id = c.customer_id
AND oi.unit_price > 500
);
Historical analysis should normally use the transaction price.
Customers Who Bought Multiple Categories
This requirement needs a count of distinct categories per customer.
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE (
SELECT COUNT(DISTINCT p.category_id)
FROM orders AS o
JOIN order_items AS oi
ON oi.order_id = o.order_id
JOIN products AS p
ON p.product_id = oi.product_id
WHERE o.customer_id = c.customer_id
AND o.status = 'COMPLETED'
) > 1;
Distinctness is required because repeated products from one category should count once.
Customers Who Purchased Every Required Product
Relational division is often expressed as double NOT EXISTS: there must be no required product for which the customer lacks a qualifying purchase.
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM products AS p
WHERE p.category_id = 10
AND NOT EXISTS (
SELECT 1
FROM orders AS o
JOIN order_items AS oi
ON oi.order_id = o.order_id
WHERE o.customer_id = c.customer_id
AND oi.product_id = p.product_id
AND o.status = 'COMPLETED'
)
);
Define what happens when the required category contains no products. Under pure logic, every customer satisfies an empty requirement set; business rules may differ.
Customers Who Bought Both Products
Two existence predicates clearly express "bought both Mouse and Keyboard."
WHERE EXISTS (
SELECT 1
FROM purchases AS p
WHERE p.customer_id = c.customer_id
AND p.product_code = 'MOUSE'
)
AND EXISTS (
SELECT 1
FROM purchases AS p
WHERE p.customer_id = c.customer_id
AND p.product_code = 'KEYBOARD'
);
An alternative groups purchases and uses conditional counts. The grouped form scales better when the required list contains many products.
Bought One Product but Never Another
Combine positive and negative existence to find customers who bought a laptop but never a desk.
WHERE EXISTS (
SELECT 1 FROM purchases AS p
WHERE p.customer_id = c.customer_id
AND p.product_code = 'LAPTOP'
)
AND NOT EXISTS (
SELECT 1 FROM purchases AS p
WHERE p.customer_id = c.customer_id
AND p.product_code = 'DESK'
);
Keep date range and completion-status definitions consistent across both predicates.
Departments with Above-Average Payroll
This compares group totals rather than individual salaries.
WITH department_totals AS (
SELECT department_id,
SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
)
SELECT department_id, total_salary
FROM department_totals
WHERE total_salary > (
SELECT AVG(total_salary)
FROM department_totals
);
The CTE avoids repeating the same grouped subquery and makes the two levels of aggregation explicit.
Highest Department Average
First calculate each department average, then compare it with the maximum of those averages. A ranked window result is another clear option when ties and top-N departments matter.
Do not compare a department's average directly with the maximum individual salary; that would mix grains and answer a different question.
Orders Above Each Customer's Average
SELECT o.order_id, o.customer_id, o.total_amount
FROM orders AS o
WHERE o.total_amount > (
SELECT AVG(x.total_amount)
FROM orders AS x
WHERE x.customer_id = o.customer_id
);
The correlation creates a customer-specific benchmark. A window average can calculate the same value while preserving order rows.
Latest Order for Every Customer
A scalar maximum date can identify the latest date, but it may return several orders tied on that date. A ranked derived table gives deterministic control.
SELECT order_id, customer_id, order_date, total_amount
FROM (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC, order_id DESC
) AS row_num
FROM orders AS o
) AS ranked_orders
WHERE row_num = 1;
This is a nested query even though the main work uses a window function.
Customers with No Recent Orders
SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.order_date >= :cutoff_date
);
Pass an explicit cutoff value so tests are repeatable. Clarify timezone, timestamp precision, and whether the boundary is inclusive.
Employees Missing Mandatory Training
To find employees who have not completed at least one mandatory course, look for a required course with no corresponding completion.
SELECT e.employee_id, e.employee_name
FROM employees AS e
WHERE EXISTS (
SELECT 1
FROM training_courses AS tc
WHERE tc.is_mandatory = 1
AND NOT EXISTS (
SELECT 1
FROM training_completions AS x
WHERE x.employee_id = e.employee_id
AND x.course_id = tc.course_id
AND x.status = 'COMPLETED'
)
);
Replace outer EXISTS with NOT EXISTS to find employees who completed every mandatory course.
Banking and Inventory Scenarios
Banking queries compare transactions with account-specific averages, identify accounts with no activity, or detect payments outside customer patterns. Inventory queries find products below category-average stock, items without suppliers, and warehouses with missing replenishment activity.
These domains require complete account, currency, tenant, effective-date, and status filters. A mathematically correct average can still be operationally wrong if it combines currencies or inactive records.
Banking Transactions Above Account Average
A correlated scalar subquery can flag transactions larger than the average for the same account. The comparison population should use the same currency and a defined historical window. Otherwise, old behavior or currency conversion can distort the benchmark.
SELECT t.transaction_id,
t.account_id,
t.amount,
t.transaction_time
FROM transactions AS t
WHERE t.amount > (
SELECT AVG(x.amount)
FROM transactions AS x
WHERE x.account_id = t.account_id
AND x.currency_code = t.currency_code
AND x.transaction_time >= :history_start
);
This query identifies unusual size, not fraud. Production detection usually combines amount with location, device, merchant, velocity, account history, and risk rules. An account with no earlier transactions produces a null average, so its current row is not returned unless first-transaction handling is added explicitly.
Inventory Below Category Average and Missing Suppliers
Inventory teams can compare each product's available stock with the average for its category. Available stock should be defined consistently, for example on-hand quantity minus reserved quantity, rather than mixing raw and available measures.
SELECT p.product_id,
p.product_name,
p.stock_quantity
FROM products AS p
WHERE p.stock_quantity < (
SELECT AVG(x.stock_quantity)
FROM products AS x
WHERE x.category_id = p.category_id
AND x.is_active = 1
)
AND p.is_active = 1;
A separate NOT EXISTS check can identify active products with no active supplier assignment. Include effective dates and supplier status inside the relationship check. A supplier link that expired yesterday should not satisfy a requirement for a currently available supplier.
Students Who Passed Every Required Subject
The all-required-items pattern applies to education just as it applies to product purchases and employee training. A student qualifies when no required subject lacks a passing result.
SELECT s.student_id, s.student_name
FROM students AS s
WHERE NOT EXISTS (
SELECT 1
FROM subjects AS sub
WHERE sub.is_required = 1
AND NOT EXISTS (
SELECT 1
FROM results AS r
WHERE r.student_id = s.student_id
AND r.subject_id = sub.subject_id
AND r.score >= sub.pass_score
)
);
If students may retake a subject, any passing attempt satisfies this version. If only the latest attempt counts, rank attempts first. Also decide whether a curriculum with no required subjects should qualify every student or produce no result.
Patients Without Appointments
Healthcare scheduling can find active patients with no future non-cancelled appointment. The status and time conditions define the relationship that must be absent.
SELECT p.patient_id, p.patient_name
FROM patients AS p
WHERE p.is_active = 1
AND NOT EXISTS (
SELECT 1
FROM appointments AS a
WHERE a.patient_id = p.patient_id
AND a.appointment_time >= :current_time
AND a.status NOT IN ('CANCELLED', 'NO_SHOW')
);
This query does not mean the patient has never had an appointment; it means no qualifying future appointment exists. Keep those meanings separate in reports, alerts, and interview explanations.
Student and Healthcare Scenarios
Education systems use correlated averages to find students above class average and double NOT EXISTS to verify that a student passed every required subject. Healthcare systems identify patients without appointments, doctors above average appointment load, and records lacking required follow-up events.
Privacy, soft deletion, cancellation status, and effective dates affect which rows qualify. Nested logic must reflect those domain rules explicitly.
Testing Scenarios
A test case that never passed can be found with NOT EXISTS over successful executions. Frequently failing tests require counts, while above-average failure counts require a second aggregation level.
SELECT t.test_case_id, t.test_name
FROM test_cases AS t
WHERE NOT EXISTS (
SELECT 1
FROM test_executions AS e
WHERE e.test_case_id = t.test_case_id
AND e.status = 'PASSED'
);
Decide whether obsolete, quarantined, or never-executed tests belong in the result. "Never passed" includes never executed unless another existence condition excludes them.
API Monitoring Scenarios
Monitoring queries identify endpoints above a global latency benchmark, endpoints with no successful requests, or endpoints slower than their own historical average. Time windows and percentile metrics matter because averages can hide long-tail latency.
SELECT endpoint_id, request_time, duration_ms
FROM api_requests AS r
WHERE duration_ms > (
SELECT AVG(x.duration_ms)
FROM api_requests AS x
WHERE x.endpoint_id = r.endpoint_id
AND x.request_time >= :history_start
);
Exclude the current request from the benchmark if the requirement demands comparison only with prior history.
Data Validation and Orphan Detection
Orphan rows are child records whose parent is missing. NOT EXISTS states this directly.
SELECT o.order_id, o.customer_id
FROM orders AS o
WHERE NOT EXISTS (
SELECT 1
FROM customers AS c
WHERE c.customer_id = o.customer_id
);
The same pattern finds order items without orders, payments without invoices, and executions without test cases. Foreign keys should prevent many such issues, but validation remains useful for imported, staged, or legacy data.
Duplicate Customer Detection
A grouped nested query can identify duplicate normalized email values and join back to full customer rows.
SELECT c.*
FROM customers AS c
JOIN (
SELECT LOWER(TRIM(email)) AS email_key
FROM customers
WHERE email IS NOT NULL
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1
) AS duplicates
ON duplicates.email_key = LOWER(TRIM(c.email));
Normalization rules should be governed and indexed appropriately. Email equivalence can be more complex than lowercasing and trimming.
Choosing the Right Pattern
| Requirement wording | Likely pattern |
|---|---|
| Greater than the overall average | Non-correlated scalar aggregate |
| Greater than its group's average | Correlated aggregate or window function |
| Belongs to one of these qualifying groups | IN or join |
| Has at least one related record | EXISTS |
| Has no related records | NOT EXISTS |
| Has all required records | Double NOT EXISTS or count comparison |
| Latest or top N in each group | Window function in a derived table or CTE |
Nested Queries vs JOINs, CTEs, and Windows
Use joins when related columns belong in the output or several values come from the same related row. Use CTEs to name repeated or multi-stage logic. Use window functions when group calculations must preserve detail rows. Use nested existence predicates when only relationship presence or absence matters.
These techniques are complementary. A single solution may contain a CTE, joins, a window function, and an outer nested filter. Choose boundaries that make row grain and business intent reviewable.
Performance Considerations
Correlated subqueries can lead to repeated inner access, but optimizers may decorrelate them. EXISTS may become a semijoin, NOT EXISTS an antijoin, and derived tables may be merged or materialized. Index correlation and filter keys according to real access patterns.
Capture actual plans and compare estimated with actual rows. Test realistic data volume, skew, parameters, and concurrency. Do not declare joins universally faster or subqueries universally slower.
Common Mistakes
- Using equality when the inner query can return multiple rows.
- Using
NOT INwithout controlling inner nulls. - Omitting a correlation key such as tenant, version, or department.
- Comparing values calculated at different grains.
- Assuming physical execution always proceeds inside-out.
- Adding excessive nesting instead of naming stages.
- Ignoring duplicate records and tie behavior.
- Counting every match when only existence is needed.
- Ignoring status, history, soft deletion, and time windows.
- Using arbitrary row limiting to hide invalid multiple matches.
Testing Checklist
Test inner results containing zero, one, and multiple rows. Include null keys, duplicate children, ties, empty requirement sets, no-history cases, soft-deleted rows, and boundary timestamps. Verify final row count as well as values.
For correlation, create identical local IDs in different tenants to expose missing tenant keys. For "every" requirements, test zero requirements and partial completion. For negative conditions, test unrelated and disallowed child rows.
Best Practices
- Translate the business wording into scalar, set, existence, absence, or all-record semantics.
- Write and test the inner query independently.
- State the grain of every query block.
- Use complete correlation and business keys.
- Handle null and empty-set behavior deliberately.
- Preserve ties when the requirement does not select one winner.
- Use stable identifiers rather than display names.
- Name complex stages with CTEs or derived-table aliases.
- Index important relationship and filter columns.
- Validate execution plans and production-like data.
Interview Questions and Answers
What is a real-world nested query?
It is a query that uses another query to calculate a benchmark, produce a set, or test a relationship needed by a practical business requirement.
When should EXISTS be used?
Use it when the requirement asks whether at least one qualifying related row exists.
When should NOT EXISTS be used?
Use it for missing relationships or to prove that no required record is missing.
How do you find rows above a group average?
Use a correlated aggregate, a grouped summary join, or a window average, then compare each detail row with the group benchmark.
How do you find customers who purchased every product?
Use double NOT EXISTS to prove there is no required product lacking a qualifying purchase, or compare distinct required and purchased counts carefully.
Are nested queries slower than joins?
Not inherently. Optimizers can transform subqueries into join-like plans. Performance depends on the plan, data, indexes, and workload.
Practical Exercises
Practice finding employees above location average, customers with exactly one completed order, products never purchased in the last year, test cases that failed in every recent run, endpoints whose current latency exceeds their prior seven-day average, and accounts missing a monthly reconciliation record.
For each exercise, write the business grain, choose the inner-result shape, test null and duplicate behavior, create an equivalent alternative where practical, and compare actual plans.
Decision Framework
1. What does the outer row represent?
2. What supporting question must be answered?
3. Is the inner answer one value, many values,
presence, absence, or every required value?
4. Can NULL, duplicates, or ties occur?
5. Which keys define the complete relationship?
6. Would a join, CTE, or window function be clearer?
7. Does the query preserve the required row grain?
8. What does the actual execution plan show?
Key Takeaway
Real-world nested queries solve layered business questions by making one query supply a benchmark, set, or relationship test to another. The most reusable patterns are scalar aggregates for benchmarks, correlated aggregates for per-group comparisons, IN for membership, EXISTS for presence, NOT EXISTS for absence, double NOT EXISTS for every-required-item logic, and ranked derived tables for latest or top-N rows.
Correctness depends on result shape, grain, complete keys, nulls, duplicates, ties, statuses, and time windows. Choose the clearest pattern, test edge cases, and use execution plans and realistic data to evaluate performance. Nested queries are not a shortcut around relational reasoning; they are a structured way to express it.