CODEWITHSUNDEEP
sql
Wait What
Wait What

Reputation: 61

SQL: How is this query getting executed when the inner subquery is returning multiple rows while I haven't used IN()?

I have a table emptable and EMPNO is the primary key.

I run this query:

SELECT ename 
FROM emp 
WHERE ename = (SELECT MIN(ename) FROM emp);

and it returns this:

enter image description here

Since the inner subquery is returning two rows, and since I am not using IN(), shouldn't I get an error of subquery returning multiple rows, so how am I getting this output?

P.S.: Sorry for my horrible English

Upvotes: 1

Views: 73

Answers (2)

Doug Coats
Doug Coats

Reputation: 7117

So subqueries are fun. Aside from using a sub query as something to select from, the general types are scalar or correlated.

Scalar subqueries can only return one value.

Correlated subqueries refer to something outside of the subquery (think EXISTS clause, as that is a kind of correlated subquery).

These two definitions are crude and not text book but explain them simply enough for now.

So your subquery is scalar and used for comparison. What makes it fit the rules for returning only one value is the MIN() function as MIN() only returns one value. If you did not use MIN(), then you would have to figure out another way to make it fit the rules of a scalar subquery. For example:

SELECT ename 
FROM emp e
WHERE ename = 
    (
        SELECT TOP 1 ename 
        FROM emp 
        WHERE emp.ename=e.ename 
        ORDER BY emp.ename ASC
    );

This is a correlated scalar subquery, as the subquery refers to the e object from the outer query. You’ll notice I had to had TOP 1 because the sample data provided doesn’t give me any other unique columns for comparison, not doing so would caused the “you can only have one value returned from scalar subquery” error.

So to answer your question – the reason it does not error is because the sub query only returns one value. For each row of your result set, the ename equals to the MIN(ename). This is intended behavior.

Upvotes: 0

forpas
forpas

Reputation: 164164

The subquery:

SELECT MIN(ename) FROM emp

returns only 1 row with 1 column (it is called a scalar subquery) which has the value of the minimum ename of the table.

If you had used also a GROUP BY clause like this:

SELECT MIN(ename) FROM emp GROUP BY empno

then the subquery would return 2 rows, 1 for each empno.

Upvotes: 2

Related Questions