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)

Mkotla

Published on 22-Feb-2018 08:23:15

• Related Questions & Answers
• How can we simulate the MySQL INTERSECT query?
• How MySQL LEFT JOIN can be used to simulate the MySQL MINUS 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?
• How can we write PHP script to count the affected rows 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?
• How can we use a MySQL stored function in a database query?
• Can we use SELECT NULL statement in a MySQL query?
• Which MySQL query can be used with the help of which we can see the list of MySQL databases?
• How can we write MySQL query for inner joins with the help of Comma operator?