How do we convert a subquery into a left join?

To make it understandable, we are using 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 reserve;
+------+------------+
| ID   | Day        |
+------+------------+
| 1    | 2017-12-30 |
| 2    | 2017-12-28 |
| 2    | 2017-12-25 |
| 1    | 2017-12-24 |
| 3    | 2017-12-26 |
+------+------------+
5 rows in set (0.00 sec)

Now, the following is a subquery that will find all the data that are not subscribed to any The name of the car's customer.

mysql> Select Name from customers where customer_id NOT IN (Select id From reserve);
+----------+
| Name     |
+----------+
| Virender |
+----------+
1 row in set (0.00 sec)

Now, with the following steps, we can convert the above subquery into a RIGHT JOIN −

• Move the table named 'Reserve' in the subquery to the FROM clause, And join it with 'Customers' table using LEFT JOIN.
• The WHERE clause compares the customer_id column to the ids returned by the subquery. Therefore, the IN expression is converted into an explicit direct comparison between the id columns of the two tables in the FROM clause.
• In the WHERE clause, limit the output to rows with NULL values ​​in the 'Reserve' table.

mysql> SELECT Name from customers LEFT JOIN reserve ON customer_id = Id WHERE Id IS NULL;
+----------+
| Name     |
+----------+
| Virender |
+----------+
1 row in set (0.00 sec)

The above is the detailed content of How do we convert a subquery into a left join?. For more information, please follow other related articles on the PHP Chinese website!