

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Questions & Answers
- How can we convert subqueries to INNER JOIN?
- How can we convert subqueries to RIGHT JOIN?
- How can we create a MySQL view with LEFT JOIN?
- What is MySQL LEFT JOIN and how can we write MySQL query for it?
- How can we subtract values in MySQL table with the help of LEFT JOIN?
- How can we distinguish between MySQL CROSS JOIN and INNER JOIN?
- How MySQL LEFT JOIN can be used to simulate the MySQL MINUS query?
- INNER JOIN vs FULL OUTER JOIN vs LEFT JOIN vs RIGHT JOIN in PostgreSQL?
- How can we create a MySQL view with INNER JOIN?
- How can we create a MySQL view with RIGHT JOIN?
- How can you perform left join on two tables using MySQL in Python?
- How can we convert list to Set in Java?
- Perform MySQL LEFT JOIN on two tables?
- Can we convert a Java array to list?
- Can we convert a Java list to array?
Advertisements