MySQL - INTERSECT Operator

MySQL: The INTERSECT Operator

In set theory, the intersection of two sets contains only the elements that are present in both sets. The INTERSECT operator in SQL performs this exact function on the result sets of two or more SELECT statements, returning only the rows that are common to all of them.

Note: The INTERSECT operator was introduced in MySQL version 8.0.31. For earlier versions, you must use alternative methods like INNER JOIN or IN with a subquery to achieve the same result.

Understanding the INTERSECT Operator

The INTERSECT operator compares the results of two queries and returns the distinct rows that appear in both result sets. For INTERSECT to work correctly, the SELECT statements must satisfy two conditions:

• They must have the same number of columns.
• The corresponding columns must have compatible data types.

Syntax

The basic syntax for the INTERSECT operator is as follows:

SELECT column_list FROM table1
INTERSECT
SELECT column_list FROM table2;

Example

Imagine a company has two sets of employees: FullTimeEmployees and ProjectContractors. We want to find individuals who are listed in both tables, perhaps because they transitioned from a contractor to a full-time role.

First, let’s create and populate the FullTimeEmployees table:

CREATE TABLE FullTimeEmployees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

INSERT INTO FullTimeEmployees (id, name, email) VALUES
(1, 'Alice', 'alice@example.com'),
(2, 'Bob', 'bob@example.com'),
(3, 'Charlie', 'charlie@example.com');

Next, the ProjectContractors table:

CREATE TABLE ProjectContractors (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

INSERT INTO ProjectContractors (id, name, email) VALUES
(101, 'David', 'david@example.com'),
(102, 'Charlie', 'charlie@example.com'),
(103, 'Eve', 'eve@example.com');

Now, use INTERSECT to find the person who exists in both tables, based on their name and email.

SELECT name, email FROM FullTimeEmployees
INTERSECT
SELECT name, email FROM ProjectContractors;

Output

The query returns only ‘Charlie’, as he is the only person present in both result sets.

name	email
Charlie	charlie@example.com

Using INTERSECT with Other Clauses

You can combine INTERSECT with other clauses like WHERE, ORDER BY, and LIMIT to build more complex queries.

INTERSECT with a WHERE Clause

Suppose we have two tables of products: OnlineStore and RetailStore. We want to find products that are on sale in both stores.

-- Assuming tables OnlineStore(product_name, price, on_sale) and RetailStore(product_name, price, on_sale)
SELECT product_name FROM OnlineStore
WHERE on_sale = TRUE
INTERSECT
SELECT product_name FROM RetailStore
WHERE on_sale = TRUE;

Alternative to INTERSECT (for older MySQL versions)

If you are using a version of MySQL older than 8.0.31, you can achieve the same result using an INNER JOIN.

SELECT DISTINCT fte.name, fte.email
FROM FullTimeEmployees fte
INNER JOIN ProjectContractors pc ON fte.name = pc.name AND fte.email = pc.email;

This query joins the two tables on the common columns and DISTINCT ensures that each unique row is returned only once, mimicking the behavior of INTERSECT.

Using INTERSECT in a Client Program

Executing an INTERSECT query from an application is the same as any other SELECT query. Below are examples in modern programming languages, using best practices like prepared statements to prevent SQL injection.

Python
Node.js
Java
PHP



This Python example uses `mysql-connector-python` to execute the `INTERSECT` query.

import mysql.connector

def find_common_personnel():
    try:
        with mysql.connector.connect(
            host="localhost",
            user="root",
            password="your_password",
            database="your_db"
        ) as cnx:
            with cnx.cursor(dictionary=True) as cursor:
                query = ("SELECT name, email FROM FullTimeEmployees "
                         "INTERSECT "
                         "SELECT name, email FROM ProjectContractors")
                cursor.execute(query)
                results = cursor.fetchall()
                print("Common Personnel:")
                for row in results:
                    print(f"- Name: {row['name']}, Email: {row['email']}")
    except mysql.connector.Error as err:
        print(f"Error: {err}")

find_common_personnel()

Output

Common Personnel:
- Name: Charlie, Email: charlie@example.com



This Node.js example uses `mysql2/promise` with async/await for clean, modern asynchronous code.

const mysql = require('mysql2/promise');

async function findCommonPersonnel() {
  let connection;
  try {
    connection = await mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: 'your_password',
        database: 'your_db'
    });
    const query = `
        SELECT name, email FROM FullTimeEmployees
        INTERSECT
        SELECT name, email FROM ProjectContractors`;

    const [rows] = await connection.execute(query);
    console.log('Common Personnel:');
    rows.forEach(row => {
        console.log(`- Name: ${row.name}, Email: ${row.email}`);
    });
  } catch (error) {
    console.error(`Database operation failed: ${error.message}`);
  } finally {
    if (connection) await connection.end();
  }
}

findCommonPersonnel();

Output

Common Personnel:
- Name: Charlie, Email: charlie@example.com



This Java example uses a `try-with-resources` statement to ensure database resources are closed automatically.

import java.sql.*;

public class IntersectExample {
    static final String DB_URL = "jdbc:mysql://localhost:3306/your_db";
    static final String USER = "root";
    static final String PASS = "your_password";

    public static void main(String[] args) {
        String query = "SELECT name, email FROM FullTimeEmployees " +
                       "INTERSECT " +
                       "SELECT name, email FROM ProjectContractors";

        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {

            System.out.println("Common Personnel:");
            while (rs.next()) {
                String name = rs.getString("name");
                String email = rs.getString("email");
                System.out.println("- Name: " + name + ", Email: " + email);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Output

Common Personnel:
- Name: Charlie, Email: charlie@example.com



This PHP example uses the modern `mysqli` object-oriented interface.

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'your_password';
$dbname = 'your_db';

$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$sql = "SELECT name, email FROM FullTimeEmployees INTERSECT SELECT name, email FROM ProjectContractors;";
$result = $mysqli->query($sql);

echo "Common Personnel:<br>";
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "- Name: " . htmlspecialchars($row["name"]) . ", Email: " . htmlspecialchars($row["email"]) . "<br>";
    }
} else {
    echo "No common personnel found.";
}

$mysqli->close();
?>

Output

Common Personnel:
- Name: Charlie, Email: charlie@example.com