Subquery Returned More Than 1 Value!

22 Jul 2005 8:24 AM

Arpan

The following query, which retrieves the tenth costliest product from a
table, works fine:

---------------------------------------------
SELECT TOP 1 PID,PName,Price
FROM Products
WHERE PID NOT IN (SELECT TOP 9 PID FROM Products ORDER BY Price ASC)
---------------------------------------------

But if the above query is modified slightly by replacing the logical
operators "NOT IN" with the comparison operator "<>" (as shown below)

---------------------------------------------
SELECT TOP 1 PID,PName,Price
FROM Products
WHERE PID<>(SELECT TOP 9 PID FROM Products ORDER BY Price ASC)
---------------------------------------------

an error saying "Subquery cannot return more than 1 value..." gets
generated. That's perfectly OK since "SELECT TOP 9" will retrieve 9
records but the same 9 records are being retrieved by the subquery
within the first query as well! So why isn't the first query generating
the "....cannot return more than 1 value..." error?

Thanks,

Arpan

22 Jul 2005 8:33 AM

Daniel Crichton

Arpan wrote on 22 Jul 2005 01:24:30 -0700:

Show quote

> The following query, which retrieves the tenth costliest product from a
> table, works fine:
>
> ---------------------------------------------
> SELECT TOP 1 PID,PName,Price
> FROM Products
> WHERE PID NOT IN (SELECT TOP 9 PID FROM Products ORDER BY Price ASC)
> ---------------------------------------------
>
> But if the above query is modified slightly by replacing the logical
> operators "NOT IN" with the comparison operator "<>" (as shown below)
>
> ---------------------------------------------
> SELECT TOP 1 PID,PName,Price
> FROM Products
> WHERE PID<>(SELECT TOP 9 PID FROM Products ORDER BY Price ASC)
> ---------------------------------------------
>
> an error saying "Subquery cannot return more than 1 value..." gets
> generated. That's perfectly OK since "SELECT TOP 9" will retrieve 9
> records but the same 9 records are being retrieved by the subquery
> within the first query as well! So why isn't the first query generating
> the "....cannot return more than 1 value..." error?

The IN operator can take a list of values, either as 1 or more values comma
separated or a set of rows. <> only allows one value to be used in the
comparison. Think of IN as being "in the list ..." and it makes perfect
sense why it doesn't throw an error.

Dan

22 Jul 2005 2:16 PM

Madhivanan

If you use arithmetic operator then the subquery should return only one
value

Madhivanan