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:

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.

Be Sociable, Share!

• 
•