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

Published on 22-Feb-2018 05:38:04

Previous Page Print Page