Subqueries and correlated queries

Use one query’s result in another.

UnderstandStudy an examplePractise

Understand the concept

A subquery can appear as a scalar expression, a derived table in FROM or a predicate in WHERE. A scalar subquery must return at most one value; multiple rows raise an error. IN tests membership in a result set, and a derived table needs an alias.

A correlated subquery references the outer row. That logical dependence does not guarantee a separate physical execution for every row: the optimizer can transform the plan. Evaluate both readability and the actual execution plan.

WHEN TO USE IT

Use it for above-average values and within-group comparisons.

COMMON PITFALL

Forgetting that a scalar subquery may return multiple rows.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT ProductName, Price FROM lab.Product WHERE Price > (SELECT AVG(Price) FROM lab.Product);

Compares each price with the overall average.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Find every product tied at the maximum price using a subquery.

Show the solution and explanation

Worked solution

SELECT ProductName, Price FROM lab.Product WHERE Price = (SELECT MAX(Price) FROM lab.Product);

Returns Laptop and would retain ties at the maximum.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.