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

Updated on 22-Jun-2020 12:35:18

• Related Questions & Answers
• 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 MINUS query?
• How MySQL LEFT JOIN can be used to simulate the MySQL MINUS query?
• How can we sort multiple columns in a single query?
• In MySQL, how we can write Multiple-line statement?
• How can I simulate a print statement in MySQL?
• How can I simulate an array variable in MySQL?
• How can we delete multiple rows from a MySQL table?
• How can we check for NULL in a MySQL query?
• Can we fetch multiple values with MySQL WHERE Clause?
• Returning Multiple Values in Python?
• How can I get the output of multiple MySQL tables from a single query?
• How can we get the total number of rows affected by MySQL query?
• How we can bundle multiple python modules?