Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What kind of output is returned by MySQL scalar subquery? What are the restrictions on using it with MySQL query?
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.
239 Views
