
- 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 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)
- Related Articles
- 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?
- 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?
- Can we fetch multiple values with MySQL WHERE Clause?
- How can we upload data into multiple MySQL tables by using mysqlimport?
- What is MySQL LEFT JOIN and how can we write MySQL query for it?

Advertisements