MySQL - UNION 运算符

MySQL - UNION 运算符

MySQL 中的 UNION 运算符是一个强大的工具，用于将两个或多个 SELECT 语句的结果集合并为一个结果集。当您需要从不同表或同一表在不同条件下检索类似数据时，这尤其有用。

UNION 与 UNION ALL

UNION 运算符有两种变体：

• UNION： 合并结果集并移除重复行。为此，MySQL 会隐式执行 DISTINCT 操作，这可能会带来性能开销。
• UNION ALL： 合并结果集并包含所有行，包括重复项。这比 UNION 更快，因为它避免了检查和移除重复项的开销。除非您明确需要消除重复项，否则请使用 UNION ALL。

UNION 兼容性规则

要使 UNION 操作成功，所涉及的 SELECT 语句必须“UNION 兼容”。这意味着它们必须满足以下条件：

• 它们的结果集中的列数必须相同。
• 对应列的数据类型必须兼容（例如，您可以将 VARCHAR 与 CHAR UNION，但不能在不进行类型转换的情况下将其与 DATETIME UNION）。
• 在每个 SELECT 语句中，列的出现顺序必须相同。

示例：合并客户和潜在客户

假设我们有两个表：active_customers（活跃客户）和 prospects（潜在客户）。我们想要一个统一的邮件列表。

CREATE TABLE active_customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

CREATE TABLE prospects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

INSERT INTO active_customers (first_name, last_name, email) VALUES
('Walter', 'Brown', 'walter.b@example.com'),
('Bernice', 'Smith', 'bernice.s@example.com');

INSERT INTO prospects (first_name, last_name, email) VALUES
('Peter', 'Jones', 'peter.j@example.com'),
('Bernice', 'Smith', 'bernice.s@example.com'); -- 重复的电子邮件

要获取所有联系人的唯一列表，我们使用 UNION：

SELECT first_name, last_name, email FROM active_customers
UNION
SELECT first_name, last_name, email FROM prospects;

这将返回三行，因为重复的 ‘Bernice Smith’ 记录被移除了。

要获取所有记录（包括重复项），我们使用 UNION ALL：

SELECT first_name, last_name, email FROM active_customers
UNION ALL
SELECT first_name, last_name, email FROM prospects;

这将返回所有四行。

使用 WHERE、ORDER BY 和别名 (Aliases)

• WHERE 子句： 您可以对每个独立的 SELECT 语句应用 WHERE 子句，以便在 UNION 操作之前过滤其结果。
• ORDER BY 子句： ORDER BY 子句只能在整个 UNION 语句的末尾应用一次。它对最终合并的结果集进行排序。
• 别名： 列的别名由第一个 SELECT 语句决定。您可以在最终的 ORDER BY 子句中使用它们。

SELECT
    first_name AS f_name,
    last_name AS l_name,
    email
FROM
    active_customers
WHERE
    last_name <> 'Smith'

UNION

SELECT
    first_name,
    last_name,
    email
FROM
    prospects

ORDER BY l_name ASC, f_name ASC;

现代集合操作 (MySQL 8.0.31+)

MySQL 的最新版本引入了 INTERSECT 和 EXCEPT，它们是标准的 SQL 集合运算符，提供了更多比较结果集的方式：

• INTERSECT： 仅返回两个结果集中都出现的行。
• EXCEPT： 返回第一个结果集中出现但不在第二个结果集中出现的行。

-- 查找既是活跃客户又是潜在客户的人
SELECT first_name, last_name, email FROM active_customers
INTERSECT
SELECT first_name, last_name, email FROM prospects;
-- 结果：Bernice Smith

客户端程序中的 UNION (现代示例)

以下是如何在各种语言中使用现代代码执行 UNION 查询。

NodeJS
Python
Java
PHP



// 使用 mysql2 结合 async/await
const mysql = require('mysql2/promise');

async function getMailingList(pool) {
    const sql = `
        SELECT first_name, last_name, email FROM active_customers
        UNION
        SELECT first_name, last_name, email FROM prospects
        ORDER BY last_name;`
    try {
        const [rows] = await pool.query(sql);
        console.log('Combined Mailing List:', rows);
        return rows;
    } catch (error) {
        console.error('Failed to retrieve mailing list:', error);
    }
}



# 使用 mysql-connector-python 结合上下文管理器
import mysql.connector
from decimal import Decimal

def get_mailing_list(config):
    sql = """
        SELECT first_name, last_name, email FROM active_customers
        UNION
        SELECT first_name, last_name, email FROM prospects
        ORDER BY last_name;"""
    try:
        with mysql.connector.connect(**config) as conn:
            with conn.cursor(dictionary=True) as cursor:
                cursor.execute(sql)
                results = cursor.fetchall()
                print('Combined Mailing List:', results)
                return results
    except mysql.connector.Error as err:
        print(f"Database Error: {err}")



// 使用 JDBC 结合 try-with-resources
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MailingListDAO {
    public List<String> getMailingList(String url, String user, String password) {
        String sql = "SELECT first_name, last_name, email FROM active_customers " +
                     "UNION " +
                     "SELECT first_name, last_name, email FROM prospects " +
                     "ORDER BY last_name;";
        List<String> mailingList = new ArrayList<>();

        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                mailingList.add(rs.getString("email"));
            }
            System.out.println("Combined Mailing List fetched successfully.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return mailingList;
    }
}



// 使用 mysqli 结合面向对象风格和预处理语句
<?php
function getMailingList($dbHost, $dbUser, $dbPass, $dbName) {
    $mysqli = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
    if ($mysqli->connect_error) {
        die("Connection failed: " . $mysqli->connect_error);
    }

    $sql = "SELECT first_name, last_name, email FROM active_customers "
         . "UNION "
         . "SELECT first_name, last_name, email FROM prospects "
         . "ORDER BY last_name;";

    $result = $mysqli->query($sql);
    $mailingList = [];

    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $mailingList[] = $row;
        }
        $result->free();
    } else {
        echo "Error: " . $mysqli->error;
    }

    $mysqli->close();
    print_r($mailingList);
    return $mailingList;
}