What kind of output is returned by MySQL scalar subquery? What are the restrictions on using it with MySQL query?

MySQLMySQLi Database

MySQL scalar subquery returns exactly one column value from one row and we can use it where a single column is permissible. Followings are the cases when scalar subqueries return value other than one row −

Case1 − When it returns 0 rows

In case if the subquery returns 0 rows then the value of scalar subquery expression would be NULL.

Case2 − When it returns more than one rows

In case if the subquery returns more than one row then, due to the property of scalar subquery, MySQL returns an error.

It can be understood with the help of an example which uses the data from the following table −

mysql> Select * from Customers;
+-------------+----------+
| Customer_Id | Name     |
+-------------+----------+
| 1           | Rahul    |
| 2           | Yashpal  |
| 3           | Gaurav   |
| 4           | Virender |
+-------------+----------+
4 rows in set (0.00 sec)

mysql> Select * from Reservations;
+------+-------------+------------+
| ID   | Customer_id | Day        |
+------+-------------+------------+
| 1    | 1           | 2017-12-30 |
| 2    | 2           | 2017-12-28 |
| 3    | 2           | 2017-12-29 |
| 4    | 1           | 2017-12-25 |
| 5    | 3           | 2017-12-26 |
+------+-------------+------------+
5 rows in set (0.00 sec)

Now, the following query would return exactly one column value for one row −

mysql> Select Name from Customers WHERE customer_id = (Select Customer_id FROM reservations where id = 3);
+---------+
| Name    |
+---------+
| Yashpal |
+---------+
1 row in set (0.00 sec)

Now, suppose if the subquery returns 0 rows then it means that the value of scalar subquery expression is NULL. It is shown in the following query −

mysql> Select Name from Customers WHERE customer_id = (Select Customer_id FROM reservations where id = 10);
Empty set (0.00 sec)

We can see from the above result set that MySQL returns empty set i.e. 0 rows because the value of scalar subquery expression if NULL (no id that is equal to 10).

The restriction in using the scalar subquery with MySQL query is that we can use a scalar subquery with statements that permits only literal value. For example, as we know that LIMIT requires literal integer arguments hence we cannot use scalar subqueries to supply these values.

raja
Published on 21-Feb-2018 17:11:30
Advertisements