How can we simulate the MySQL INTERSECT query returning multiple expressions?

Since we cannot use the INTERSECT query in MySQL, we will use the EXIST 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 EXIST operator with WHERE clause will simulate INTERSECT to return ‘studentid’, Name, Address where the name is not ‘Yashpal’ that exist in both the tables −

mysql>Select Student_detail.studentid,Student_detail.name, student_detail.address FROM student_detail WHERE Student_detail.studentid >100 AND EXISTS (SELECT * FROM Student_info WHERE Student_info.Name <> 'Yashpal' AND Student_info.studentid = Student_detail.studentid AND Student_info.name = Student_detail.name);
+-----------+--------+------------+
| studentid | name   | address    |
+-----------+--------+------------+
|       105 | Gaurav | Chandigarh |
|       130 | Ram    | Jhansi     |
|       132 | Shyam  | Chandigarh |
|       133 | Mohan  | Delhi      |
+-----------+--------+------------+
4 rows in set (0.00 sec)

Giri Raju

Published on 22-Feb-2018 08:16:19

Previous Page Print Page