How to filter data with the help of MySQL subquery?

By using the IN keyword, we can use subqueries to filter data. This is because we can use query results like a list of values, using the IN operator to filter a query based on the results of another query. Subqueries appear in parentheses after the IN keyword.

Example

We will use the data in the following table to illustrate 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 the 'IN' operator with a subquery, and Returns the result after comparing all 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)

The above is the detailed content of How to filter data with the help of MySQL subquery?. For more information, please follow other related articles on the PHP Chinese website!