How can we simulate the MySQL INTERSECT query?

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 will simulate INTERSECT to return all ‘studentid’ values that exist in both the tables −

mysql> Select Student_detail.studentid FROM Student_detail WHERE student_detail.studentid IN(SELECT Student_info.studentid FROM Student_info);
+-----------+
| studentid |
+-----------+
| 101       |
| 105       |
| 130       |
| 132       |
| 133       |
+-----------+
5 rows in set (0.06 sec)

Jennifer Nicholas

Published on 22-Feb-2018 08:14:05

• Related Questions & Answers
• How can we simulate the MySQL INTERSECT query having WHERE clause?
• How can we simulate the MySQL INTERSECT query returning multiple expressions?
• How can we simulate the MySQL MINUS query?
• How MySQL LEFT JOIN can be used to simulate the MySQL MINUS query?
• 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?