【oracle】什么是内连接和外连接？

本文将使用示例描述清楚数据库oracle的内连接和外连接。

1. 造数（假设数据）

假设我们有两个表：employees（员工表）和 departments（部门表）。

employees 表

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO employees (employee_id, employee_name, department_id) VALUES
(1, 'Alice', 10),
(2, 'Bob', 10),
(3, 'Charlie', 20),
(4, 'David', NULL), -- 没有分配部门
(5, 'Eva', 30);

departments 表

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

INSERT INTO departments (department_id, department_name) VALUES
(10, 'Finance'),
(20, 'HR'),
(30, 'IT'),
(40, 'Marketing'); -- 没有员工

2. 列举每个表的数据

employees 表数据

SELECT * FROM employees;

结果：

employee_id | employee_name | department_id
-----------|---------------|--------------
1         | Alice         | 10
2         | Bob           | 10
3         | Charlie       | 20
4         | David         | NULL
5         | Eva           | 30

departments 表数据

SELECT * FROM departments;

结果：

department_id | department_name
-------------|----------------
10           | Finance
20           | HR
30           | IT
40           | Marketing

3. 内连接（Inner Join）

内连接（Inner Join/Join）：返回两个表中满足连接条件的行。

查询

SELECT 
    e.employee_name AS 员工姓名,
    d.department_name AS 部门名称
FROM 
    employees e
INNER JOIN 
    departments d ON e.department_id = d.department_id;
----等同于下面
SELECT 
    e.employee_name AS 员工姓名,
    d.department_name AS 部门名称
FROM 
    employees e
JOIN 
    departments d ON e.department_id = d.department_id;

在SQL中，如果省略了INNER、LEFT、RIGHT或FULL等关键字，只使用JOIN关键字，则默认为INNER JOIN。

结果

员工姓名   | 部门名称
---------|---------
Alice    | Finance
Bob      | Finance
Charlie  | HR
Eva      | IT

4. 外连接

4.1 左外连接（Left Outer Join/Left Join）

左外连接（Left Outer Join/Left Join）：返回左表中的所有行，以及右表中满足连接条件的行。如果右表中没有匹配的行，则结果集中对应的列将为NULL。

查询

SELECT 
    e.employee_name AS 员工姓名,
    d.department_name AS 部门名称
FROM 
    employees e
LEFT OUTER JOIN 
    departments d ON e.department_id = d.department_id;

结果

员工姓名   | 部门名称
---------|---------
Alice    | Finance
Bob      | Finance
Charlie  | HR
Eva      | IT
David    | (NULL)  -- David没有部门

结果说明：

• 左外连接会返回左表（employees）中的所有行。
• 如果左表的某行在右表（departments）中没有匹配的行，则结果集中右表对应的列（如department_name）将显示为NULL。
• 在本例中，员工David由于没有分配到部门，所以其对应的department_name为NULL。

4.2 右外连接（Right Outer Join/Right Join）

右外连接（Right Outer Join/Right Join）：返回右表中的所有行，以及左表中满足连接条件的行。如果左表中没有匹配的行，则结果集中对应的列将为NULL。

查询

SELECT 
    e.employee_name AS 员工姓名,
    d.department_name AS 部门名称
FROM 
    employees e
RIGHT OUTER JOIN 
    departments d ON e.department_id = d.department_id;

结果

员工姓名   | 部门名称
---------|---------
Alice    | Finance
Bob      | Finance
Charlie  | HR
Eva      | IT
(NULL)   | Marketing  -- Marketing部门没有员工

结果说明：

• 右外连接会返回右表（departments）中的所有行。
• 如果右表的某行在左表（employees）中没有匹配的行，则结果集中左表对应的列（如employee_name）将显示为NULL。
• 在本例中，部门Marketing由于没有分配员工，所以其对应的employee_name为NULL。

4.3 全外连接（Full Outer Join/Full Join）（注意：不是所有的数据库系统都支持全外连接）

全外连接（Full Outer Join/Full Join）：返回两个表中的所有行。如果某个表中没有匹配的行，则结果集中对应的列将为NULL

查询

SELECT 
    e.employee_name AS 员工姓名,
    d.department_name AS 部门名称
FROM 
    employees e
FULL OUTER JOIN 
    departments d ON e.department_id = d.department_id;

结果

员工姓名   | 部门名称
---------|---------
Alice    | Finance
Bob      | Finance
Charlie  | HR
Eva      | IT
David    | (NULL)
(NULL)   | Marketing

结果说明：

• 全外连接会返回左表（employees）和右表（departments）中的所有行。
• 如果某行在另一表中没有匹配的行，则结果集中对应的列将显示为NULL。
• 在本例中，员工David由于没有分配到部门，其对应的department_name为NULL；同时，Marketing部门没有员工，则该部门的employee_name也为NULL。