TRAINING | DAY 9

Subqueries & an Introduction to CTEs

Holding intermediate results: subqueries in FROM, WHERE, HAVING, SELECT and JOIN, the ICC points table solved, and CTEs with WITH.

The Crux in 60 Seconds

Some answers need an intermediate result

Average order value cannot come from AVG(sales) when an order has several rows. Sum per order first, then average that: 458, not 230.

A subquery can go anywhere

SELECT, FROM, WHERE, HAVING or JOIN. In FROM it needs an alias; used as a condition it does not. IN / NOT IN accept a list; comparison operators need one value.

Derive a second table from the first

Group the employee table by department to get average salary, then join it back. The ICC points table works the same way: flag, wrap, aggregate.

A CTE is a subquery moved to the top

WITH name AS (query) then a final SELECT. Same result, more readable, and one CTE can be reused several times.

Foundational Concepts & Comparisons

AVG(sales)

Averages every row. Example: three rows, 1,800 total, average 600.

Average order value

Sum per order id first (1,200 and 600), then average: 900.

Subquery in FROM

Result acts as a virtual table; alias required, and you select columns from it in the outer query.

Subquery as a condition

Only its result is compared, so no alias is needed.

IN / NOT IN

Expect a list: one column, many rows allowed.

Greater than, less than

Expect a single value, so the subquery must return one row and one column.

Inner vs outer query

Inner: the query inside. Outer (main or final) query uses its result. Only the terms differ; it is still one idea.

Subquery vs CTE

Very similar: the subquery moves into a WITH block with a name. Everything else stays the same.

Architectural Mechanics & Workflows

Why subqueries: the average order value

Orders table: order id, product, sales. In the Excel example order 1 has two products (combined sales 1,200) and order 2 one product (600). Plain average of sales: 1,800 / 3 = 600. Real average order value: (1,200 + 600) / 2 = 900. In SQL Server AVG(sales) gave about 230, which is wrong for the same reason. You cannot reach the right value directly; you need to hold an intermediate result (sales per order) and run another query on top.

Order id	Rows	Sales per order
1	2 products	1,200
2	1 product	600
Average of row sales (1,800 / 3)		600 (wrong)
Average of order totals (1,800 / 2)		900 (right)

Inner queryorder id, SUM(sales) per order (GROUP BY order id)

→

Virtual tablealias a: order_id, order_sales

→

Outer queryAVG(order_sales) = 458

SELECT AVG(order_sales)
FROM (SELECT order_id, SUM(sales) AS order_sales
      FROM orders GROUP BY order_id) a

Put the query in brackets and give it an alias; the alias can be anything (A, OrdersAggregated). The result is a virtual table and an ordinary query runs on it.
Using sales in the outer query gives "invalid column name": the new table has order_id and order_sales. Refer to alias.order_sales as with any table alias.
458 is higher than 230, as it should be, because multi-product orders are now one row each.

Orders whose sales exceed the average order value

SELECT order_id FROM orders
GROUP BY order_id
HAVING SUM(sales) > (SELECT AVG(order_sales)
                     FROM (SELECT order_id, SUM(sales) AS order_sales
                           FROM orders GROUP BY order_id) a)

Result: 1,379 orders (the table has 9,994 rows). Checked one: sales near 700, above 458.
There is no limit to nesting queries inside queries.
HAVING, because we group by order id first and filter on an aggregate; one order id can have several rows.
Alias question: the second subquery has none, because it is used as a condition. An alias is needed when you select columns from the result in an outer query.
A student asked about reusing the alias OrdersAggregated instead of rewriting the subquery: the alias only names that query's result; without the query it means nothing. You can refer to OrdersAggregated.order_sales inside its own query's scope, like a table alias.

Subquery in WHERE: employees whose department is missing

SELECT * FROM employee
WHERE department_id NOT IN (SELECT department_id FROM department)

Earlier this was solved with a left join and a WHERE condition; this is another way. NOT IN normally takes a hardcoded list (100, 200, 300); here the inner query supplies the list (100 to 500), and the outer query returns the employee with department 700.

Rule	Detail
Inner query returns one column for IN / NOT IN	Adding department name as a second column fails with "only one expression can be specified in the select list when the subquery is not introduced with EXISTS". EXISTS is different and comes later.
IN / NOT IN: many rows allowed	It is effectively a list of values.
Comparison operators: one value	`WHERE department_id > (SELECT MIN(department_id) FROM department)` is fine: one row, giving everything except 100. Two columns or several rows would break it.
Match the subquery to the operator	List for IN / NOT IN, single value for greater than and similar.

Student question: MINUS instead of NOT IN? The tables have different column counts. Subtracting department ids would return only the missing ids, not the employee details that this query returns.
Joins and subqueries can be combined (shown below).

Subquery in SELECT

SELECT e.*, (SELECT AVG(salary) FROM employee) AS avg_salary FROM employee e

The inner query runs first (it returned 9,000) and that value is shown on every row. If you filter an employee out of the outer query, the average stays 9,000: the subquery executes first and the filter applies to the main table afterwards. So far: SELECT, FROM, WHERE and HAVING; JOIN is left.

Subquery in JOIN: the same question again

SELECT a.order_id, a.order_sales, b.avg_order_sales
FROM (SELECT order_id, SUM(sales) AS order_sales FROM orders GROUP BY order_id) a
INNER JOIN (SELECT AVG(order_sales) AS avg_order_sales
            FROM (SELECT order_id, SUM(sales) AS order_sales
                  FROM orders GROUP BY order_id) x) b ON 1 = 1
WHERE a.order_sales > b.avg_order_sales

Wherever a table can be used, a subquery can be used: a and b are query results, each visualised as a table.
No common column, so ON 1 = 1. The same 1,379 rows as with HAVING come back.

Where	Example from class
FROM	Average of sums per order id (alias a)
HAVING	SUM(sales) greater than the average order value
WHERE	Department id NOT IN the department ids
SELECT	Average salary shown on every row
JOIN	Order-wise sales joined to the one-row average

Why `1 = 1`, and performance (trainer Q&A)

Question	Answer
Why `1 = 1`?	Always true (`100 = 100` would do). A normal join this way would be a cross join, but B is an average with no GROUP BY, so it has one row. Each order gets that one value and the row count stays equal to A's. If B had two rows, every row of A would match both and rows would double.
Can I leave the ON out?	Yes: write A, B with no ON. By default each row tries every row; same result, since B has one row.
HAVING or join: which is faster?	HAVING here: fewer subqueries (three versus two). Subqueries are not as performant because the database must hold the result in memory. Avoid them when you can; often you cannot.
Order of execution?	FROM first, then the join, then WHERE, then the result passes to the outer query.
Category-wise sales of orders not returned (day 5 question)?	Can use a left join or a subquery.

Employees with their department's average salary

Expected: department 100 shows 10,000 on every employee row, 200 shows 9,500, 300 (two employees) shows 7,000, 700 (one employee) shows 7,000. Only the employee table is given, so derive the second table from it and join back on department id.

Eemployee table

→

D (derived)department id, AVG(salary) GROUP BY department id

→

JOINE.department_id = D.department_id

SELECT E.*, D.avg_dept_salary
FROM employee E
INNER JOIN (SELECT department_id, AVG(salary) AS avg_dept_salary
            FROM employee GROUP BY department_id) D
  ON E.department_id = D.department_id

INNER JOIN, not left: every department id exists in both because both come from the same table and there is no filter.
E.* means all columns of E; D.* would repeat department id.
How to approach: write down the result you need (department column, average salary), then the join, then the full query. First write one query, then the outer query using it, step by step; after a few questions you can write directly. Do not start by writing a subquery.
A subquery result could be held in a view, but views are database objects with a different purpose. A view just to simplify a big join is possible, not usual.

ICC World Cup points table (the parked question, solved)

Output: team name, matches played, won, lost. India played 2, won 2, lost 0; South Africa played 1, lost 1. A team can sit in either column, so combine both.

SELECT team_name, COUNT(1) AS matches_played, SUM(win_flag) AS matches_won,
       COUNT(1) - SUM(win_flag) AS matches_lost
FROM (SELECT team_1 AS team_name, CASE WHEN team_1 = winner THEN 1 ELSE 0 END AS win_flag
      FROM icc_world_cup
      UNION ALL
      SELECT team_2, CASE WHEN team_2 = winner THEN 1 ELSE 0 END FROM icc_world_cup) a
GROUP BY team_name

The inner result has ten rows (five from each query) and no winner column is needed. There were five wins and five losses in total, as there were five matches.
UNION ALL, not UNION: UNION removes duplicates and could lose matches. Count with COUNT(1) or COUNT(*). Losses = count minus the sum of the win flag.

CTE: common table expression

Subqueries and CTEs are very similar; only the writing differs. Use WITH, a name, AS and the query in brackets, then a final SELECT that uses the name. The name acts like a virtual or temporary table.

WITH a AS (SELECT team_1 AS team_name, ... UNION ALL SELECT team_2, ... )
SELECT team_name, COUNT(1) AS matches_played, SUM(win_flag) AS matches_won
FROM a GROUP BY team_name

The WITH part cannot run alone: it expects a final SELECT, so run them together. "A" is not a good name in real work; it was used for comparison with the subquery.
Department average salary as a CTE: WITH D AS (SELECT department_id, AVG(salary) ...) then select from employee and inner join D (called DEP for clarity). Same result, same order of execution: FROM, then the join, and when DEP is needed its result is fetched.

Multiple CTEsSeparate them with commas after one WITH: WITH a AS (...), b AS (...). A later CTE can use an earlier one. There is no limit, but do not make queries needlessly complex.

Execution with several CTEsIt starts from the main query, not from each CTE. A CTE that is never used is not executed. FROM needs OrderWiseSales, so it runs; a later join to B (which may use OrderWiseSales) uses that result; then WHERE, then SELECT.

	Subquery	CTE
Written	Inside FROM, WHERE, HAVING, SELECT or JOIN	In a WITH block before the main SELECT
Name	Alias (required in FROM)	Name given after WITH
Reuse	Rewrite the query each time	Refer to it many times

Advantages of CTEs

Advantage	Explanation
Structure and readability	Read the query top to bottom, step by step, instead of nested SELECTs. The instructor usually prefers CTEs for this reason.
Reuse in one query	Order-wise sales appeared twice in the average-order-value query. Write `WITH OrderWiseSales AS (...)` once and use it as A and inside B. It can also be referenced from another CTE (hence "common").
Possible performance gain	A repeated subquery may run twice; a CTE can hold the result once. For identical, single-use queries there is generally no difference.

Why subqueries at all? A CTE works in every scenario, and writing CTEs can be a habit, but practise subqueries too. For a simple WHERE condition a CTE (WITH Departments AS ...) is unnecessary: use the subquery.
CTE details and advantages continue tomorrow. Students asked to revisit CTEs in the doubt class.

Best Practices & Golden Rules

Aggregate first, then averageWhen one entity spans several rows (an order with several products), plain AVG is wrong.

Alias every subquery in FROMIt is required; skip it only for condition subqueries.

Match the operatorIN / NOT IN: one column. Greater than: one value.

Plan the result firstWrite the columns you need, join step by step, then build the full query.

Avoid subqueries when you canHAVING beat the join of subqueries here. Keep simple conditions as plain subqueries.

UNION ALL for countingUNION could drop matches in the points table.

Numbers & Metrics to Remember

900

true average order value in the Excel example (not 600)

230 / 458

wrong vs right average order value in the data

1,379

orders above the average order value

700

department id missing from the department table

Mini Glossary

Subquery: a query inside another query, used wherever a value, list or table fits.

Inner / outer query: the nested query, and the main query that uses its result.

Virtual table: the result of a subquery in FROM, queried like a table through its alias.

Intermediate result: data derived first (such as sales per order) so a second step can use it.

CTE: common table expression, a named query defined with WITH.

ON 1 = 1: always-true join condition, safe when one side has one row.

E.*: all columns of table or alias E.

Single-value subquery: returns one row and one column, as comparison operators need.

Quick Self-Test

1Why is AVG(sales) not the average order value, and what steps give the right answer?
2When does a subquery need an alias and when not?
3Scenario: find employees whose department is not in the department table using a subquery. What goes in WHERE, and why must the inner query return one column?
4What must a subquery return when used with greater than, versus with IN?
5Why does ON 1 = 1 not multiply rows when joining orders to the average?
6Scenario: show each employee with their department's average salary. Which two tables do you join and on what?
7In the ICC query, why UNION ALL, and how are matches lost calculated?
8Troubleshooting: your CTE query fails when you run only the WITH part. Why? Name two advantages of CTEs over subqueries.