Why do we get 'Subquery returned more than 1 value' error in SQL Server?

Hi pals,
When we all start off with T-SQL querying, we often forget how inner queries provide values for comparison in the outer query.

For example, if there is an 'employee' table with IDs from 1 to 10 and another table 'title' with IDs from 1 to 9 and we need to find that one employee with an entry in 'employee' table without a corresponding entry in 'title' table, the query would look somewhat like this.

select * from employee where id not in (select id from title)

A common mistake that we make is by writing something like this

select * from employee where id >
(select id from title)

On executing this query, we get an error like this

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

 The mistake here is that the arithmetic operator '>' expects a single value from the inner query whereas we end up getting all the 'id' values from the 'title' table in the inner query.

How to solve this?

This is where we can use the operators ANY or ALL.

'ANY' returns true if at least one row in the inner query result satisfies the comparison.

In contrast, 'ALL' returns true only when all rows in the inner query result satisfies the comparison.

Usage

select * from employee where id > ALL
(select id from title) 

Hope this post helps beginners.
Praseo.