- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
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 can we simulate the MySQL MINUS query?
Since we cannot use the MINUS query in MySQL, we will use 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 JOINS will simulate MINUS to return the ‘studentid’ values in student_info but not in the 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 the 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)
- Related Articles
- How MySQL LEFT JOIN can be used to simulate the MySQL MINUS query?
- How can we simulate the MySQL INTERSECT query?
- How can we simulate the MySQL INTERSECT query having WHERE clause?
- How can we simulate the MySQL INTERSECT query returning multiple expressions?
- How can I simulate a print statement in MySQL?
- How can I simulate an array variable in MySQL?
- How can we check for NULL in a MySQL query?
- How can we get the total number of rows affected by MySQL query?
- What is MySQL LEFT JOIN and how can we write MySQL query for it?
- What is MySQL RIGHT JOIN and how can we write MySQL query for it?
- Can we use “LIKE concat()” in a MySQL query?
- How can we use a MySQL stored function in a database query?
- How can we write PHP script to count the affected rows by MySQL query?
- Can we use SELECT NULL statement in a MySQL query?
- How can we write MySQL query for inner joins with the help of Comma operator?

Advertisements