MySQL - 右连接

理解外连接：MySQL 的 RIGHT JOIN

在 SQL 中，连接 (JOIN) 用于根据相关列合并来自两个或更多表的行。INNER JOIN (内连接) 只返回两个表中连接条件都满足的行，而 OUTER JOIN (外连接，包括 LEFT、RIGHT 或 FULL) 还会返回在另一个表中没有匹配的行。

什么是 RIGHT JOIN？

RIGHT JOIN (也称为 RIGHT OUTER JOIN，右外连接) 返回右表中的所有行以及左表中匹配的行。如果右表中的某一行在左表中没有匹配项，结果仍将包含该行，但左表的所有列将显示 NULL (空值)。

可以这样理解：“给我右表中的所有内容，如果能在左表中找到任何匹配信息，也一并带过来。”

语法

SELECT table1.column_name, table2.column_name
FROM table1
RIGHT JOIN table2 ON table1.related_column = table2.related_column;

最佳实践：许多开发人员觉得 LEFT JOIN (左连接) 更直观。任何 RIGHT JOIN 都可以通过简单地交换表的顺序改写为 LEFT JOIN。例如，A RIGHT JOIN B 等同于 B LEFT JOIN A。

实用的 RIGHT JOIN 示例

让我们考虑一个包含两个表的情景：employees (员工) 和 departments (部门)。我们想列出所有部门，如果一个部门有员工，我们就显示该员工的姓名。

示例表

首先，是 employees 表：

CREATE TABLE employees (
    id INT PRIMARY KEY, name VARCHAR(50), dept_id INT
);
INSERT INTO employees VALUES
(1, 'Alice', 101),
(2, 'Bob', 102),
(3, 'Charlie', 101);

接下来是 departments 表。请注意，‘Marketing’ (市场部，ID 103) 尚未分配任何员工。

CREATE TABLE departments (
    id INT PRIMARY KEY, name VARCHAR(50)
);
INSERT INTO departments VALUES
(101, 'Sales'),
(102, 'Engineering'),
(103, 'Marketing');

RIGHT JOIN 查询

我们将使用 RIGHT JOIN 来确保列出所有部门，无论它们是否有员工。

SELECT
    e.name AS employee_name,
    d.name AS department_name
FROM
    employees AS e
RIGHT JOIN
    departments AS d ON e.dept_id = d.id;

输出

结果正确地显示了所有部门。对于没有员工的“Marketing”部门，employee_name 为 NULL。

employee_name	department_name
Alice	Sales
Charlie	Sales
Bob	Engineering
NULL	Marketing

带 WHERE 子句的 RIGHT JOIN

你可以使用 WHERE 子句来过滤连接的结果。外连接的一个常见用例是查找在一个表中存在但另一个表中不存在的行。

示例：查找未配备员工的部门

通过过滤左侧为 NULL 的行，我们可以找到所有没有员工的部门。

SELECT d.name AS department_name
FROM employees AS e
RIGHT JOIN departments AS d ON e.dept_id = d.id
WHERE e.id IS NULL;

输出

department_name
Marketing

客户端程序中的 RIGHT JOIN

在客户端程序中执行连接是很常见的任务。一如既往，请使用预处理语句 (prepared statements) 来防止 SQL 注入，即使是对于 SELECT (选择) 查询。

示例 (Python 与 mysql-connector-python)

Python
NodeJS
Java
PHP



import mysql.connector

def get_department_roster():
    db_config = {'user': 'root', 'password': 'password', 'database': 'your_db'}
    query = """
        SELECT e.name, d.name
        FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id
        ORDER BY d.name, e.name
    """
    with mysql.connector.connect(**db_config) as conn:
        with conn.cursor(dictionary=True) as cursor: # dictionary=True 非常有用
            cursor.execute(query)
            results = cursor.fetchall()
            for row in results:
                employee = row['name'] if row['name'] else 'N/A'
                print(f"部门: {row['d.name']}, 员工: {employee}")

get_department_roster()



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

async function getDepartmentRoster() {
    const conn = await mysql.createConnection({ /* 配置 */ });
    const query = `
        SELECT e.name AS employeeName, d.name AS departmentName
        FROM employees AS e
        RIGHT JOIN departments AS d ON e.dept_id = d.id
    `;
    try {
        const [rows] = await conn.query(query);
        console.log(rows);
    } finally {
        await conn.end();
    }
}
getDepartmentRoster();



public void printDepartmentRoster() {
    String sql = "SELECT e.name, d.name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id";
    try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(sql)) {

        while (rs.next()) {
            String employeeName = rs.getString("e.name");
            String departmentName = rs.getString("d.name");
            System.out.printf("员工: %s, 部门: %s%n",
                employeeName == null ? "(无员工)" : employeeName, departmentName);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}



$pdo = new PDO(/* DSN */);
$sql = 'SELECT e.name AS employeeName, d.name AS departmentName
        FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id';

$stmt = $pdo->query($sql);

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    $employee = $row['employeeName'] ?? '(无员工)';
    echo "部门: {$row['departmentName']}, 员工: {$employee}\n";
}