T-SQL 101: 118 Creating Lists from Subqueries in SQL Server T-SQL

Another way that we can use a subquery is in place of a list of values. I’ve previously shown how the IN operator allows us to supply a set of values, and we check if something or some value is contained in that list.

In this example query, I’m saying that I want a list of all the cinemas that have a credit rating that isn’t OK. Perhaps it’s something to do with not paying their bills, or defaulted in some way.

Regardless, there are many ways to do this (including with a simple join). But in this case, I’m obtaining a list of CreditRatingIDs where the CreditRatingName isn’t OK. Then I’m checking for any cinemas that are in any of those credit ratings.

This is another example of a subquery that is easy to test, because I can just highlight the code in the subquery and execute it by itself.

Result Restriction

It’s important that this type of  subquery only returns one column, but it can return any number of rows. With the scalar subquery we had before, it would have to return one column and one row.

You might hope that if you returned multiple columns, you could check more than one at once, with code like this:

WHERE (o.OrderDate, o.DeliveryDate) = (SELECT a, b FROM ...)

Some variants of SQL do allow that. SQL Server isn’t one of them. I wish it was. There is a lot of code that would be easier to read if we could do this.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-02-20