A subquery is a SELECT statement nested inside another SQL statement. Use subqueries to filter, aggregate, or transform data in ways that a single-level query cannot express cleanly.
Example
The following query finds the top 10 sellers by maximum tickets sold, excluding sellers whose city appears in the venue list. The WHERE clause uses a table subquery with NOT IN — the subquery returns a single column (venuecity) with multiple rows.
A table subquery can return multiple rows and columns.
select firstname, lastname, cityname, max(qtysold) as maxsold
from users join sales on users.userid=sales.sellerid
where users.cityname not in(select venuecity from venue)
group by firstname, lastname, cityname
order by maxsold desc, cityname desc
limit 10;Result:
firstname | lastname | cityname | maxsold
-----------+----------+----------------+---------
Noah | Guerrero | Worcester | 8
Isadora | Moss | Winooski | 8
Kieran | Harrison | Westminster | 8
Heidi | Davis | Warwick | 8
Sara | Anthony | Waco | 8
Bree | Buck | Valdez | 8
Evangeline | Sampson | Trenton | 8
Kendall | Keith | Stillwater | 8
Bertha | Bishop | Stevens Point | 8
Patricia | Anderson | South Portland | 8How the query works:
JOIN sales ON users.userid = sales.sellerid— links each user to their sales records as a seller.WHERE users.cityname NOT IN (SELECT venuecity FROM venue)— the table subquery returns all venue cities; the outer query keeps only sellers whose city is not in that list.MAX(qtysold) AS maxsold— aggregates ticket quantity per seller.ORDER BY maxsold DESC, cityname DESC— ranks results by sales volume, then by city name in descending order.LIMIT 10— returns only the top 10 rows.