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 |
+--------+------------------------------+

Answer 1

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:

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

Answer 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

1. This will loop through every record from "enrolled" table that has cid = '15-455'.

    FROM enrolled as E
   WHERE cid='15-455';
   

2. For every record from step 1 it will perform the following query

   SELECT S.name 
     FROM student AS S
    WHERE S.sid = E.sid;
   

Answer 3

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.