How can we convert subqueries to LEFT JOIN?

To make it understand we are using the data from the following tables −

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 which will find the name of all the customers who have not to reserve any car.

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 help of followings steps, we can convert the above subquery into RIGHT join −

Move the ‘Reserve’ table named in the subquery to the FROM clause and join it to ‘Customers’ using LEFT JOIN.
The WHERE clause compares the customer_id column to the ids returned from the subquery. Hence convert the IN expression to an explicit direct comparison between id columns of two tables in the FROM clause.
In the WHERE clause, restrict the output to those rows having NULL 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)

Sai Nath

Updated on: 22-Jun-2020

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started