I am testing two examples in which a WHERE clause is compared to the results of a subquery.
SELECT BusinessEntityID, FirstName, LastName
FROM Person.Person p
WHERE p.BusinessEntityID = (SELECT BusinessEntityID FROM HumanResources.Employee h WHERE p.BusinessEntityID = h.BusinessEntityID);
This first example works. It returns 290 rows. This means that p.BusinessEntity in the outer WHERE clause can handle multiple values.
Then there is this example:
SELECT ord_no, purch_amt
FROM orders o
WHERE salesman_id = (SELECT Salesman_id FROM salesman);
When I run this 2nd example, I get an error stating:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
This 2nd example is also returning multiple value (I tested that subquery by itself), but this time I get an error.
Why is it that the WHERE clause in the first example can handle multiple values, but the 2nd example can't?