- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Articles
- 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?
- 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?
- Can we use “LIKE concat()” in a MySQL query?
- How can we use a MySQL stored function in a database query?
- How can we write PHP script to count the affected rows by MySQL query?
- Can we use SELECT NULL statement in a MySQL query?
- How can we write MySQL query for inner joins with the help of Comma operator?

Advertisements