Tony Rogerson's ramblings on SQL Server

I've had so many referrals from google on this error because of my previous blog entry on the T-SQL Value assignment SET vs SELECT I've decided to expand into this specific error and in what situations you get it, what causes it and how to get round it.

Some test data...

The error message is pretty self-explanatory once you remember what a subquery is. A subquery is a query within your main query, for example the query below reproduces the behaviour and gives the message...

The sub-query is the bit between the braces, the sub-query in itself is fine and valid, its because we are using an operator that expects comparison against a constant rather than a set. Our sub-query is returning 2 rows and because we are using '=' equals expects a single value (1 row, 1 column).

The correct thing to do depends on your problem, we can do a number of things we can change the single value operator to an operator that can deal with sets, so we change from '=' to 'IN' and now everything works fine.

select *
from #test
where mypk IN ( select mypk
                from #test
                where fullname = 'Tony Rogerson' )

This might not be what you want, the above query has changed in behaviour because it can now return multiple mypk, so if you where expecting a look up on a single mypk then you are stuffed. We can get round that by using the TOP keyword, however, think about what you are doing - it may not be valid, what determines which mypk gets selected? As the query below demonstrates we can get a random mypk, its going to be either 1 or 2.

select *
from #test
where mypk = ( select TOP 1 mypk
               from #test
               where fullname = 'Tony Rogerson' )

When using TOP in a sub-query you can also use ORDER BY, this is the only time you can use ORDER BY in a sub-query, to digress, people started to use an undocumented behaviour where you can order a view but SQL 2005 now breaks that behaviour so my message is clear never use ORDER BY and TOP in a view and expect the view to be ordered - it won't be!!

select *
from #test
where mypk = ( select TOP 1 mypk
               from #test
               where fullname = 'Tony Rogerson'
               order by mypk DESC )

Again using this solution depends on the business requirement you are trying to resolve.

Sub-queries can exist in many places, on the SELECT clause, in a CASE statement in many of the builtin functions like DATEDIFF.

I think my only advice if you get this is to check that your using keys correctly, I'm a very pro on using surrogate keys so make sure you never get into this situation in the first place.

Remember =, !=, <, <= , >, >= all expect single values (0 or 1 row, 1 column), EXISTS and IN are set operators and as such expect a set of data (0 or more rows).

Tony.