MySQL INTERSECT Operator

In relational databases, MySQL stands as a cornerstone for managing and manipulating data. Among its arsenal of SQL operators lies a potent tool: the INTERSECT operator. In this article, we’ll delve into the syntax, usage, and applications of MySQL’s INTERSECT operator, accompanied by practical examples to demonstrate its capabilities.

MySQL INTERSECT Operator

INTERSECT operator is used in MySQL to retrieve records from two or more SELECT statements only where these records are in common. It executes table union and presents only the row that appears in all SELECT statements of the queries. It summarizes information to help find the common points that are accurate and not altered in several data sets.

Syntax:

The syntax for using INTERSECT in MySQL is as follows:

SELECT column1, column2, ...
FROM table1
INTERSECT
SELECT column1, column2, ...
FROM table2;

Here, table1 and table2 denote the tables where data is selected and column1, column2, … refer to the columns which are used. While INTERSECT ensures that only the rows that are in both SELECTs are added to the result set, OUTER JOIN contains all the rows from the first SELECT and ONLY the rows corresponding to the rows in the second SELECT.

Example of MySQL INTERSECT

-- Create the table name, customers.
CREATE TABLE customers (
customer_id INT,
name VARCHAR(50)
);

-- Putting in an example data in the 'customers' column
INSERT INTO customers (customer_id, name) VALUES

(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'David');

-- Make the 'premium_customers' table now
CREATE TABLE premium_customers (
customer_id INT,
name VARCHAR(50)
);

-- Inserting sample data into the '"premium_customers" table
INSERT INTO premium_customers (customer_id, name) VALUES
(2, 'Bob'),
(3, 'Charlie'),
(5, 'Emma'),
(6, 'Frank');

customers table:

+-------------+---------+
| customer_id | name    |
+-------------+---------+
|           1 | Alice   |
|           2 | Bob     |
|           3 | Charlie |
|           4 | David   |
+-------------+---------+

premium_customers table:

+-------------+---------+
| customer_id | name    |
+-------------+---------+
|           2 | Bob     |
|           3 | Charlie |
|           5 | Emma    |
|           6 | Frank   |
+-------------+---------+

Examples of MySQL INTERSECT

Example 1: Using DISTINCT and INNER JOIN Clause

• INNER JOIN Clause: The INNER JOIN is used to combine rows from either two or more tables as they are related by one column between them. This involves customer_id between customers and premium_customers tables.
• DISTINCT Keyword: The DISTINCT keyword plays a critical role in eliminating duplicate rows in the outcome set. It allows only such pairs of customer_id and name to exist which are unique and different from the others.

SELECT DISTINCT customers.customer_id, customers.name
FROM customers
INNER JOIN premium_customers ON customers.customer_id= premium_customers.customer_id;

Output Explanation:

This query determines the customer IDs and names from the customers table which are in the premium_customers table by comparing them with each other on the customer_id column. With the DISTINCT keyword comes the assurance of only having unique rows selected in the result set.

Output:

+-------------+---------+
| customer_id | name    |
+-------------+---------+
|           2 | Bob     |
|           3 | Charlie |
+-------------+---------+

Example 2: Using IN and Subquery

• Subquery: A subquery, or the query nestled within another query, is known as an inner or nested query. Here, the subquery gets out the customer_id values from the table premium customers.
• IN Operator: The IN operator is applied to check the suitability of a value against any value of the subquery; It removes customers rows with data that matches those found by the subquery.

SELECT customer_id, name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM premium_customers
);

Output Explanation:

This query finds the customers’ IDs and names from the customers table where the customers’ IDs are within the result set of the subquery, which assumes the customer IDs from the premium_customers table.

Output:

+-------------+---------+
| customer_id | name    |
+-------------+---------+
|           2 | Bob     |
|           3 | Charlie |
+-------------+---------+

Conclusion

MySQL INTERSECT operator is an effective means of identifying similar data features throughout multiple datasets thus facilitating comparative operations and data analysis. It does not matter if a developer decodes the data with the INNER JOIN clauses, subqueries, or other options. The INTERSECT is a tool to optimize and put together different approaches offered by the developers.