How can we simulate the MySQL INTERSECT query having WHERE clause?
Since we cannot use INTERSECT query in MySQL, we will use IN operator to simulate the INTERSECT 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 IN operator with WHERE clause will simulate INTERSECT to return all ‘studentid’ values greater than 130 that exist in both the tables −
mysql> Select Student_detail.studentid FROM Student_detail WHERE student_detail.studentid >130 AND student_detail.studentid IN(SELECT Student_info.studentid FROM Student_info WHERE Student_detail.studentid > 0);
+-----------+
| studentid |
+-----------+
| 132 |
| 133 |
+-----------+
2 rows in set (0.00 sec)
Published on 22-Feb-2018 12:44:50
