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.
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.