Demystifying T-SQL Subqueries–Part I
When I teach SQL Server Development/T-SQL courses, subqueries is usually one of those topics that students find confusing. Usually it’s a head scratcher, although there are some who just get it.
So let’s try to demystify subqueries.
Very simply put, a subquery is just a query within a query. It’s a SELECT inside another SELECT.
Now let’s break it down. Let’s start with understanding what a query gives you first.
Understanding results of regular queries
Before we can understand subqueries, we need to understand first the different results we get from regular queries. A regular query always results in a dataset. It gives you the following variations of results:
or Column List
or Single List of Values
Where to embed subqueries
Now that we know the different kinds of results we get from our regular SELECT statements, we now need to identify how and where we can embed these queries.
Here’s a quick summary of where you can place these subqueries. You might find some weird corner cases, but for the most part, this is where subqueries are applicable:
The following T-SQL query samples are using the Chinook database.
Subqueries which return scalar values
Let’s start with Result A – queries that have a scalar value as a result.
Scalar value means there’s only a single result – not a column, not a table. It can be any of the primitive data types (ex int, float, decimal, money, char, varchar, xml etc.)
Where in a SELECT statement can we embed queries that result in a scalar value?
It turns out we can embed a subquery that returns a scalar value in a lot of clauses in our typical SELECT statement.
Scalar subquery in SELECT
Only scalar subqueries are allowed inside a SELECT clause.
SELECT AlbumId, Title, ArtistID, (SELECT COUNT(*) FROM Album a2 WHERE a2.ArtistId = Album.ArtistId) AS TotalAlbumsByArtist FROM Album
Scalar subquery in FROM
SELECT x.TotalNumberOfAlbums FROM (SELECT COUNT(*) TotalNumberOfAlbums FROM Album ) x
Scalar subquery in WHERE
-- get all tracks that have -- above average price SELECT TrackId, TrackName FROM Track WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Track)
Scalar subquery in HAVING
SELECT Composer, AVG(UnitPrice) AvgUnitPrice FROM Track GROUP BY Composer HAVING AVG(UnitPrice) > (SELECT AVG(UnitPrice) FROM Track)
Scalar subquery in ORDER
-- the following is composed for -- illustration purposes only; -- this order by can be simplified, and -- you won't really use this ORDER BY in production SELECT TOP 10 TrackId, Name, Composer FROM Track ORDER BY -- this simply returns 1, 2 or 3 -- done in a complicated way (SELECT (ABS(CAST(CAST(NEWID() AS VARBINARY) AS int)) % 3) + 1 )
Having fun yet? In the next post, we will look at additional samples of where we can place subqueries that return single column list and tabular results in your SELECT statement.