How can we filter data with the help of MySQL subquery?

With the help of IN keyword, we can use a subquery to filter the data. It is because we can use the result of a query like we use a list of values with the IN operator to filter a query based on the result of another query. The subquery appears in the parentheses after the IN keyword.

Example

We are using the data from the following tables for illustrating this example −

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)

The query below uses ‘IN’ operator with a subquery and returns the result after comparing all the values returned by the subquery.

mysql> SELECT * from customers WHERE customer_id IN (Select customer_id from reservations);
+-------------+----------+
| Customer_Id | Name     |
+-------------+----------+
|           1 | Rahul    |
|           2 | Yashpal  |
|           3 | Gaurav   |
+-------------+----------+
3 rows in set (0.00 sec)

Sharon Christine

Updated on 22-Jun-2020 11:07:10

• Related Questions & Answers
• How can we use a MySQL subquery with INSERT statement?
• How can we use a MySQL subquery with FROM clause?
• How can we create a MySQL view with a subquery?
• How can we nest a subquery within another subquery?
• How can we know about the starting range of TIMESTAMP data type with the help of MySQL FROM_UNIXTIME() function?
• How can we test for the existence of any record in MySQL subquery?
• How can we subtract values in MySQL table with the help of LEFT JOIN?
• How can we get the list of MySQL server-side help categories?
• Which MySQL query can be used with the help of which we can see the list of MySQL databases?
• How can we write MySQL query for inner joins with the help of Comma operator?
• How can we write MySQL query for inner joins with the help of keyword JOIN?
• How can we write MySQL query for cross joins with the help of Comma operator?
• How can we write MySQL query for cross joins with the help of keyword JOIN?
• How can we display all the records from MySQL table with the help of PHP script?
• How can we insert values into a table with the help of MySQL self-computed output?