mysql intersect – How to intersect multiple queries using MYSQL?

mysql intersect operator returns the distinct (common) elements in two sets or common records from two or more tables.

MySQL INTERSECT Operator

The INTERSECT operator compares the result sets of two queries and returns the distinct rows that are output by both queries.

Syntax
MySQL INTERSECT OPERATOR Syntax

SELECT column_lists FROM table_name WHERE condition  
INTERSECT  
SELECT column_lists FROM table_name WHERE condition;   

Example – With Single Expression

If the main MysqL database supported the INTERSECT operator (which MySQL does not), it is how you would have use the INTERSECT operator to return the common movie_type_id values between the movies and cinema tables.

SELECT movie_type_id
FROM movies
INTERSECT
SELECT movie_type_id
FROM cinema;

you will use the IN operator

SELECT movies.movie_type_id
FROM movies
WHERE movies.movie_type_id IN (SELECT cinema.movie_type_id FROM cinema);

MySQL INTERSECT with WHERE conditions:

SELECT movie_type_id
FROM movies
WHERE movie_type_id < 500
INTERSECT
SELECT movie_type_id
FROM cinema
WHERE amount > 0;

MySQL INTERSECT query IN operator with WHERE conditions:

SELECT movies.movie_type_id
FROM movies
WHERE movies.movie_type_id < 500
AND movies.movie_type_id IN
   (SELECT cinema.movie_type_id
    FROM cinema
    WHERE cinema.amount > 0);

Example – With Multiple Expressions

SELECT connection_id, sir_nm, profile_nm
FROM connections
WHERE connection_id < 500
INTERSECT
SELECT member_id, sir_nm, profile_nm
FROM members
WHERE sir_nm <> 'Virat';

use the EXISTS clause

SELECT connections.connection_id, connections.sir_nm, connections.profile_nm
FROM connections
WHERE connections.connection_id < 500
AND EXISTS (SELECT *
            FROM members
            WHERE members.sir_nm <> 'Virat'
            AND members.member_id = connections.connection_id
            AND members.sir_nm = connections.sir_nm
            AND members.profile_nm = connections.profile_nm);

use an inner join to filter for rows

SELECT DISTINCT profiles.id 
FROM profiles
INNER JOIN members m1 on m1.id = profiles.profile_nm AND members.value = "bhagvati"
INNER JOIN members m2 on m2.id = profiles.sir_nm AND members.value = "rekha"

SQL – INTERSECT Clause

SQL> SELECT  ID, NAME, total, DATE
   FROM memberS
   LEFT JOIN charges
   ON memberS.ID = charges.member_ID
INTERSECT
   SELECT  ID, NAME, total, DATE
   FROM memberS
   RIGHT JOIN charges
   ON memberS.ID = charges.member_ID;

INTERSECT Operator using DISTINCT and INNER JOIN Clause

mysql> SELECT DISTINCT Id FROM first_db_table  
INNER JOIN tab2 USING (Id);  

INTERSECT Operator using IN and Subquery

mysql> SELECT DISTINCT Id FROM second_db_table  
WHERE Id IN (SELECT Id FROM tab2);  

Don’t Miss : set operators in sql

MySQL INTERSECT OPERATOR example using JOIN

SELECT A.profile_nm, A.sir_nm
FROM tblmember A 
JOIN tblmemberloan B
ON A.profile_nm = B.profile_nm AND A.sir_nm = B.sir_nm

I hope you get an idea about mysql intersect.
I would like to have feedback on my infinityknow.com.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.