1. Introduction
In this article, we will look at the Intersect operation. SQL Set Theory contains the Intersect. Most of the RDBMS like Oracle, SQL Server, and PostgreSQL support the Intersect operator. However, MySQL does not. In this article, we will understand what the Intersect operator is and how to emulate it in MySQL.
2. What does Set Theory mean?
In SQL, an entity that can hold a collection of objects is called a “set”. Hence in SQL all “Relations” i.e. tables are sets. Set Theory is the set of operations in which objects from multiple sets merge to form a single result. In other words, the set operators combine multiple select queries to give a single result.
The SQL Set operators are UNION, INTERSECT, and EXCEPT. Out of these operators, MySQL supports only UNION. It does not support the Intersect and Except keywords. However, MySQL emulates these operations using Joins and/or subqueries.
3. What is Intersect Operator?
The Intersect operator is the intersection of 2 or more datasets. In Relational Algebra, the intersection is shown with ꓵ symbol. The Venn diagram representation of the intersect operator is as follows
In SQL, the basic syntax of the Intersect operator is as follows:
SELECT [column_names or *] FROM tables [WHERE conditions] INTERSECT SELECT [column_names or *] FROM tables [WHERE conditions];
As per MySQL Theory
The intersect, except and Minus statements are not elementary since they can be composed with other commands. Thus, they are not implemented.
Unknown Source
4. How to Emulate Intersect in MySQL
To get the effect of the Intersect operator, we can use the following methods in MySQL RDBMS.
- INNER JOIN: MySQL supports the INNER JOIN which gets the exact same result as the Intersect operator. Inner join can be implemented on two or more tables. The Basic syntax of the Inner join query is as follows
SELECT [column_names] FROM [table1] INNER JOIN table2 USING([column_name_common_to_both]);
OR
SELECT [column_names] FROM [table1] INNER JOIN table2 ON [table1.column_name] = [table2.column_name];
- IN and Sub Query: We can also use the In Operator and subqueries to get the same result as Intersect. The basic syntax of a IN operator is as follows
SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name IN (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE] conditions);
- EXISTS: In Some cases we can also use the EXISTS operator to get the effect of an Intersect operator. The basic syntax of a EXISTS query is as follows:
SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE EXISTS (SELECT column_name FROM table1 [WHERE condition])
5. Setup
Forgoing through the examples related to the Intersect operator, we will consider the database called “Sakila”. This is an example database given by MySQL. The schema structure is available on the documentation page.
For running the queries, we will use the MySQL Workbench. The documentation for MySQL Workbench is available here.
6. Examples
We will look at all examples of all the 3 ways in which we can emulate the Intersect operator in MySQL.
6.1 INNER JOIN
INNER JOIN is the simplest join supported by MySQL. It simply matches each record of each of the tables to each other and returns only the records that are present in all tables. It performs the exact same operation that the INTERSECT operator does. This is the JOIN that is used by Default and hence the INNER Keyword is optional.
6.1.1 INNER Join 2 tables using the ON Keyword
When we join two tables using a column name, we are using the inner join. Inner joins can be done using the ON keyword. Below given is an example of Inner Join
SELECT C.CATEGORY_ID,C.NAME,FC.FILM_ID,FC.LAST_UPDATE FROM CATEGORY C INNER JOIN FILM_CATEGORY FC ON C.CATEGORY_ID = FC.CATEGORY_ID;
6.1.2 INNER JOIN 2 tables with the USING Keyword
If the column names from the tables in the Join are the same, then we can use the USING keyword instead. For example
SELECT C.CATEGORY_ID,C.NAME,FC.FILM_ID,FC.LAST_UPDATE FROM CATEGORY C INNER JOIN FILM_CATEGORY FC USING (CATEGORY_ID);
6.1.3 INNER JOIN 3 tables using the ON Keyword
The inner join can intersect 3 tables also. The column names in each of the intersects can be different. We can do it using both the ON and the USING keyword. For example:
SELECT C.CATEGORY_ID,C.NAME,FC.FILM_ID,F.TITLE, F.DESCRIPTION,F.RATING,F.SPECIAL_FEATURES FROM CATEGORY C INNER JOIN FILM_CATEGORY FC ON C.CATEGORY_ID = FC.CATEGORY_ID INNER JOIN FILM F ON F.FILM_ID = FC.FILM_ID;
6.1.4 INNER JOIN 3 tables with the USING Keyword.
We can join 3 tables with the USING keyword if the columns to be matched have the same column name in the joined tables.
SELECT C.CATEGORY_ID,C.NAME,FC.FILM_ID,F.TITLE, F.DESCRIPTION,F.RATING,F.SPECIAL_FEATURES FROM CATEGORY C INNER JOIN FILM_CATEGORY FC USING (CATEGORY_ID) INNER JOIN FILM F USING (FILM_ID);
6.1.5 INNER JOIN using Multiple tables
We can also use Inner joins to join more than 3 tables. Since Mysql by default does Inner Join, the following queries are equivalent to each other.
SELECT F.FILM_ID,F.TITLE,F.DESCRIPTION,C.NAME,F.RENTAL_RATE,F.LENGTH,F.RATING, GROUP_CONCAT(CONCAT(A.FIRST_NAME," ",A.LAST_NAME) SEPARATOR ', ') AS "ACTORS" FROM CATEGORY C INNER JOIN FILM_CATEGORY FC ON C.CATEGORY_ID = FC.CATEGORY_ID INNER JOIN FILM F ON FC.FILM_ID = F.FILM_ID INNER JOIN FILM_ACTOR FA ON F.FILM_ID = FA.FILM_ID INNER JOIN ACTOR A ON FA.ACTOR_ID = A.ACTOR_ID GROUP BY F.TITLE ORDER BY C.NAME,F.LENGTH DESC,F.TITLE ASC;
#Equivalent to above Query
SELECT F.FILM_ID,F.TITLE,F.DESCRIPTION,C.NAME,F.RENTAL_RATE,F.LENGTH,F.RATING, GROUP_CONCAT(CONCAT(A.FIRST_NAME," ",A.LAST_NAME) SEPARATOR ', ') AS "ACTORS" FROM CATEGORY C, FILM_CATEGORY FC,FILM F, FILM_ACTOR FA,ACTOR A WHERE C.CATEGORY_ID = FC.CATEGORY_ID AND FC.FILM_ID = F.FILM_ID AND F.FILM_ID = FA.FILM_ID AND FA.ACTOR_ID = A.ACTOR_ID GROUP BY F.TITLE ORDER BY C.NAME,F.LENGTH DESC,F.TITLE ASC;
6.2 In with a Subquery
The In operator also does the job of the intersect operator. The Subquery used with the IN, returns one or more records. The Query using the IN then matches the column with every record returned and the result contains only the records present in both.
6.2.1 Simple IN with a subquery
The In query can be used with a simple subquery as in the below example
SELECT * FROM FILM_CATEGORY WHERE CATEGORY_ID IN ( SELECT CATEGORY_ID FROM CATEGORY);
6.2.2 Using IN with INNER JOIN
Inner joins and IN can be combined with each other and it gives the same effect as multiple Intersect operator queries.
SELECT FILM_ID,TITLE,DESCRIPTION,RATING FROM FILM INNER JOIN FILM_CATEGORY USING (FILM_ID) WHERE CATEGORY_ID IN ( SELECT CATEGORY_ID FROM CATEGORY);
6.2.3 Nested IN
Subqueries with the IN operator can be nested as well. Writing queries using this method is discouraged since there are much easier and more cost-effective ways of achieving the same effect. However, the below given query is valid. This is equivalent to writing multiple Intersect operators.
SELECT SUM(AMOUNT) AS "TOTAL SALES"
FROM PAYMENT
WHERE RENTAL_ID IN (SELECT RENTAL_ID FROM RENTAL
WHERE INVENTORY_ID IN (SELECT INVENTORY_ID FROM INVENTORY
WHERE FILM_ID IN (SELECT FILM_ID FROM FILM
WHERE FILM_ID IN (SELECT FILM_ID FROM FILM_CATEGORY
WHERE CATEGORY_ID IN (SELECT CATEGORY_ID FROM CATEGORY)))));
6.3 Exists
In cases where a table needs to be joined across multiple tables, we can use the Exists operator to achieve the effect of an intersect operator.
SELECT C.FIRST_NAME,C.LAST_NAME,A.ADDRESS,A.POSTAL_CODE
FROM CUSTOMER C,ADDRESS A
WHERE C.ACTIVE =1
AND C.ADDRESS_ID = A.ADDRESS_ID
AND EXISTS (SELECT *
FROM CITY CT
WHERE CT.COUNTRY_ID IN (44,91,103,8,20)
AND CT.CITY_ID = A.CITY_ID);
7. Summary
Even though My SQL does not directly support the Intersect operator, we can still emulate the Intersect operator in multiple ways. There have been several requests made already to My SQL to introduce support for all the set operators to provide better dynamic SQL query building.
8. Download the Source Code
This was an example of the Intersect operation using MySQL RDBMS.
You can download the full source code of this example here: SQL Intersect Example
