MySQL - INTERSECT 运算符

MySQL：INTERSECT 运算符

在集合论中，两个集合的交集仅包含同时存在于这两个集合中的元素。SQL 中的 INTERSECT 运算符对两个或更多 SELECT 语句的结果集执行此精确功能，仅返回所有结果集中共有的行。

注意：INTERSECT 运算符是在 MySQL 8.0.31 版本中引入的。对于早期版本，您必须使用 INNER JOIN 或带有子查询的 IN 等替代方法来实现相同的结果。

理解 INTERSECT 运算符

INTERSECT 运算符比较两个查询的结果，并返回同时出现在两个结果集中的不同行。为了使 INTERSECT 正常工作，SELECT 语句必须满足两个条件：

• 它们必须具有相同数量的列。
• 对应的列必须具有兼容的数据类型。

语法

INTERSECT 运算符的基本语法如下：

SELECT column_list FROM table1
INTERSECT
SELECT column_list FROM table2;

示例

假设一家公司有两组员工：FullTimeEmployees（全职员工）和 ProjectContractors（项目承包商）。我们想找出同时在这两张表中列出的人员，可能是因为他们从承包商转为全职员工。

首先，让我们创建并填充 FullTimeEmployees 表：

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');

接下来是 ProjectContractors 表：

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');

现在，使用 INTERSECT 根据其 name（姓名）和 email（电子邮件）来查找同时存在于这两张表中的人员。

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

输出

该查询只返回“Charlie”，因为他是唯一同时存在于两个结果集中的人员。

姓名	电子邮件
Charlie	charlie@example.com

将 INTERSECT 与其他子句结合使用

您可以将 INTERSECT 与 WHERE、ORDER BY 和 LIMIT 等其他子句结合使用，以构建更复杂的查询。

INTERSECT 与 WHERE 子句结合使用

假设我们有两个产品表：OnlineStore（在线商店）和 RetailStore（零售商店）。我们想找出两家商店都在促销的产品。

-- 假设表 OnlineStore(product_name, price, on_sale) 和 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;

INTERSECT 的替代方法（适用于旧版 MySQL）

如果您使用的是 MySQL 8.0.31 之前的版本，可以使用 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;

此查询通过公共列连接两个表，DISTINCT 确保每个唯一的行只返回一次，从而模拟 INTERSECT 的行为。

在客户端程序中使用 INTERSECT

从应用程序执行 INTERSECT 查询与其他任何 SELECT 查询相同。以下是使用现代编程语言的示例，采用预处理语句（prepared statements）等最佳实践来防止 SQL 注入。

Python
Node.js
Java
PHP



此 Python 示例使用 `mysql-connector-python` 执行 `INTERSECT` 查询。

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("共同人员:")
                for row in results:
                    print(f"- 姓名: {row['name']}, 电子邮件: {row['email']}")
    except mysql.connector.Error as err:
        print(f"错误: {err}")

find_common_personnel()

输出

共同人员:
- 姓名: Charlie, 电子邮件: charlie@example.com



此 Node.js 示例使用 `mysql2/promise` 和 async/await 来编写简洁、现代的异步代码。

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('共同人员:');
    rows.forEach(row => {
        console.log(`- 姓名: ${row.name}, 电子邮件: ${row.email}`);
    });
  } catch (error) {
    console.error(`数据库操作失败: ${error.message}`);
  } finally {
    if (connection) await connection.end();
  }
}

findCommonPersonnel();

输出

共同人员:
- 姓名: Charlie, 电子邮件: charlie@example.com



此 Java 示例使用 `try-with-resources` 语句来确保数据库资源自动关闭。

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("共同人员:");
            while (rs.next()) {
                String name = rs.getString("name");
                String email = rs.getString("email");
                System.out.println("- 姓名: " + name + ", 电子邮件: " + email);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

输出

共同人员:
- 姓名: Charlie, 电子邮件: charlie@example.com



此 PHP 示例使用现代的 `mysqli` 面向对象接口。

<?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 "共同人员:<br>";
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        echo "- 姓名: " . htmlspecialchars($row["name"]) . ", 电子邮件: " . htmlspecialchars($row["email"]) . "<br>";
    }
} else {
    echo "未找到共同人员。";
}

$mysqli->close();
?>

输出

共同人员:
- 姓名: Charlie, 电子邮件: charlie@example.com