Why the output of a SELECT can be another SELECT?

Question

I am rather confused about the following SQL query:

SELECT (SELECT S.name FROM student AS S
    WHERE S.sid = E.sid) AS sname
FROM enrolled as E
WHERE cid='15-455';

SELECT should be followed by an output, but why here there is another SELECT? How to understand the step-by-step meaning of this query?

The following is the query that has the same result of the above query, but its meaning is rather explicit: the output of the second SELECT is passed into the IN() function.

SELECT name FROM student
WHERE sid IN ( 
    SELECT sid FROM enrolled
    WHERE cid = '15-445'
);

Here are the original tables of this question:

mysql> select * from student;
+-------+--------+------------+------+---------+
| sid   | name   | login      | age  | gpa     |
+-------+--------+------------+------+---------+
| 53666 | Kanye  | kayne@cs   |   39 | 4.00000 |
| 53688 | Bieber | jbieber@cs |   22 | 3.90000 |
| 53655 | Tupac  | shakur@cs  |   26 | 3.50000 |
+-------+--------+------------+------+---------+

mysql> select * from enrolled;
+-------+--------+-------+
| sid   | cid    | grade |
+-------+--------+-------+
| 53666 | 15-445 | C     |
| 53688 | 15-721 | A     |
| 53688 | 15-826 | B     |
| 53655 | 15-445 | B     |
| 53666 | 15-721 | C     |
+-------+--------+-------+

mysql> select * from course;
+--------+------------------------------+
| cid    | name                         |
+--------+------------------------------+
| 15-445 | Database Systems             |
| 15-721 | Advanced Database Systems    |
| 15-826 | Data Mining                  |
| 15-823 | Advanced Topics in Databases |
+--------+------------------------------+

This is known as a scalar subquery, a subquery that returns a single value. — Serg, Jul 12 '21 at 13:39

ekochergin · Answer 1 · 2021-07-12T14:21:55.960

2

In real life I'd say both queries are just two creepy ways to avoid joins.

But in this particular case they were included in the slides you've found in order to show in how many place nested loops can be used.

They all do the same thing as the following

SELECT name 
  FROM student s
  JOIN enrolled e
    ON s.sid = e.sid
 WHERE cid = '15-445';

As for your question about step-by-step meaning of the first query. It is the following

This will loop through every record from "enrolled" table that has cid = '15-455'.
```
 FROM enrolled as E
WHERE cid='15-455';
```
For every record from step 1 it will perform the following query
```
SELECT S.name 
  FROM student AS S
 WHERE S.sid = E.sid;
```

edited Jul 12 '21 at 14:21

answered Jul 12 '21 at 13:39

ekochergin

4,109
2
12
19

This is an excerpt from the slides of CMU 15-445 Database Systems course. Maybe it is just for illustration of some query statement usages and not for practical uses. – Name Null Jul 12 '21 at 14:01
@NameNull I think you're right. So, in conclusion, as per my experience using scalar subqueries (query 1) is very rare. I don't even have any reasons on top of my head why would I use it. – ekochergin Jul 12 '21 at 14:13
@NameNull I've found those slides and updated my answer. In short: these subqueries are there just to display that you may use subqueries in many parts of a query – ekochergin Jul 12 '21 at 14:23

score 1 · Accepted Answer · answered Jul 12 '21 at 15:27

This construct:

SELECT (SELECT S.name FROM student S WHERE S.sid = E.sid) AS sname
-------^

is called a scalar subquery. This is a special type of subquery that has two important properties:

It returns one column.
It returns at most one row.

In this case, the scalar subquery is also a correlated subquery meaning that it references columns in the outer query, via the where clause.

A scalar subquery can be using almost anywhere that a scalar (i.e. constant value) can be used in a query. They can be handy. They are not exactly equivalent to a join, because:

An inner join can filter values. A scalar subquery returns NULL if there are no rows returned.
A join can multiply the number of rows. A scalar subquery returns an error if it returns more than one row.

score 0 · Answer 3 · answered Jul 12 '21 at 13:43

If you want to get informations like :

Name of student | CID | Grade |

You can do something like :

select t.name, e.cid, e.grade 
  from enrolled e
  inner join student t on (e.sid = t.sid)

Or without join (for optimization) :

select (name from student t where t.sid = e.sid) as name, e.cid, e.grade
  from enrolled e

so results are the same but in the second one you're avoiding joins.

Why the output of a SELECT can be another SELECT?

3 Answers3