Demystifying T-SQL Subqueries–Part II

In the last post (Demystifying T-SQL Subqueries – Part I) we looked at how to use scalar subqueries. Let’s continue our subquery adventure.

As with Part I, the following T-SQL query samples are using the Chinook database.

Subqueries which return a single list of values

Result Description Sample
B Single Column
or Column List
or Single List of Values
clip_image004_thumb

Continue reading

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:

Result Description Sample
A Scalar Value subquery that returns scalar value
B Single Column
or Column List
or Single List of Values
subquery that returns a list (single column) of values
C Table subquery that returns a table

Continue reading