
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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)
- Related Articles
- How can we simulate the MySQL INTERSECT query?
- How can we simulate the MySQL INTERSECT query returning multiple expressions?
- How can we simulate the MySQL MINUS query?
- How Can we use MySQL DISTINCT clause with WHERE and LIMIT clause?
- How can we use MySQL SUM() function with HAVING clause?
- How can we use ASCII() function with MySQL WHERE clause?
- How can we use CHAR_LENGTH() function with MySQL WHERE clause?
- How can we use BIN() function with MySQL WHERE clause?
- How can we use FIND_IN_SET() function with MySQL WHERE clause?
- How can we use MySQL INSTR() function with WHERE clause?
- How can we use two columns with MySQL WHERE clause?
- Can we use WHERE clause inside MySQL CASE statement?
- Can we fetch multiple values with MySQL WHERE Clause?
- How can we use WHERE clause with MySQL INSERT INTO command?
- How MySQL LEFT JOIN can be used to simulate the MySQL MINUS query?

Advertisements