T-SQL 101: 117 Creating Scalar Subqueries in SQL Server T-SQL

T-SQL 101: 117 Creating Scalar Subqueries in SQL Server T-SQL

The first example of subqueries types in this series is what’s called a scalar subquery. In this case, I’m trying to work out which orders were on the last day that we delivered any orders. We deliver orders on various days, but on the last day that we did deliver orders, tell me which orders they were.

The problem with this is that we don’t know what the delivery date was. If I did, I could just use that date value in a WHERE clause. So what we can do instead is to use a subquery, where we work out the maximum delivery date from the orders table, and then that will return us back a date. Finally, we ask for all the orders for that particular day.

Note that this type of subquery is easy to test, because I can just highlight the code in the subquery and execute it by itself.

What about NULLs ?

With this type of query, you do need to be careful of values that are NULL. I’ve previously mentioned that you can’t check if something equals NULL. We have things that are null. We need to check IS NULL or IS NOT NULL.

So when you execute a subquery, you need to keep in mind that the response might be NULL rather than returning a value. So for example, it would happen if there were no orders at all.

The query predicate would then be the same as if I had said:

WHERE o.DeliveryDate = NULL

That wouldn’t return anything, but in this case that wouldn’t matter because there’d be no orders anyway. But whenever you have a subquery, you need to think about what would happen if the response was NULL.

Would the query still make sense?

Result Restriction

It’s important that this type of  subquery only returns one column in one row (or no rows). A common error is to have a scalar subquery that returns more than one row. That causes the following error:

Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is 
not permitted when the subquery follows =, !=, <, <= , >, >= 
or when the subquery is used as an expression.

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-18