Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- 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 MySQL LEFT JOIN can be used to simulate the MySQL MINUS query?
Since we cannot use MINUS query in MySQL, we will use LEFT JOIN to simulate the MINUS query. It can be understood with the help of the following example:
Example
In this example, we are two tables namely Student_detail and Student_info having the following data −
mysql> Select * from Student_detail; +-----------+---------+------------+------------+ | studentid | Name | Address | Subject | +-----------+---------+------------+------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | Literature | | 130 | Ram | Jhansi | Computers | | 132 | Shyam | Chandigarh | Economics | | 133 | Mohan | Delhi | Computers | | 150 | Rajesh | Jaipur | Yoga | | 160 | Pradeep | Kochi | Hindi | +-----------+---------+------------+------------+ 7 rows in set (0.00 sec) mysql> Select * from Student_info; +-----------+-----------+------------+-------------+ | studentid | Name | Address | Subject | +-----------+-----------+------------+-------------+ | 101 | YashPal | Amritsar | History | | 105 | Gaurav | Chandigarh | Literature | | 130 | Ram | Jhansi | Computers | | 132 | Shyam | Chandigarh | Economics | | 133 | Mohan | Delhi | Computers | | 165 | Abhimanyu | Calcutta | Electronics | +-----------+-----------+------------+-------------+ 6 rows in set (0.00 sec)
Now, the following query using LEFT JOIN will simulate MINUS to return the ‘studentid’ values in student_info but not in Student_detail table.
mysql> SELECT studentid from student_info LEFT JOIN Student_detail USING(studentid) WHERE student_detail.studentid IS NULL; +-----------+ | studentid | +-----------+ | 165 | +-----------+ 1 row in set (0.07 sec)
Now, the following query will give us the opposite result of above query i.e. it will return the ‘studentid’ values in student_detail but not in Student_info table.
mysql> SELECT studentid from student_detail LEFT JOIN Student_info USING(studentid) WHERE student_info.studentid IS NULL; +-----------+ | studentid | +-----------+ | 150 | | 160 | +-----------+ 2 rows in set (0.00 sec)
Updated on: 20-Jun-2020
736 Views
Advertisements