Question

How can we simulate the MySQL MINUS query?

Answer 1

Since we cannot use 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 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)