JOIN 子句

JOIN 子句

更新时间：2026-08-30 22:11:55

描述

JOIN 子句用于将两个或多个表的行联接在一起，基于这些表之间的相关列进行匹配。

权限要求

执行表联接（JOIN）时，需要当前用户至少拥有参与 JOIN 表的 SELECT 权限。有关 OceanBase 数据库权限的详细介绍，参见 Oracle 模式下的权限分类。

语法

table_references:
    table_reference [, table_reference ...]

table_reference:
    table_factor
    | joined_table

table_factor:
    table_name [PARTITION (partition_name_list)] [[AS] table_alias_name]
    | table_subquery [AS] table_alias_name
    | (table_references)

partition_name_list:
    partition_name [, partition_name ...]

joined_table:
    table_reference
    | table_reference1, table_reference2 [WHERE join_condition]
    | table_reference1
      {INNER} JOIN table_reference2
    | table_reference1
      {LEFT | RIGHT | FULL} [OUTER] JOIN table_reference2
      {ON join_condition | WHERE join_condition}
    | table_reference1 {LEFT | RIGHT | FULL} [OUTER] JOIN table_reference2
    | table_reference1 NATURAL [INNER] JOIN table_reference2
    | table_reference1 NATURAL {LEFT | RIGHT | FULL} [OUTER] JOIN table_reference2

join_condition:
    expression
    | column_name1 = column_name2

参数解释

table_references（表引用）

表示一个或多个 table_reference 的列表，用逗号分隔。即 SELECT 语句中 FROM 后面可以跟多个表，这些表之间用逗号隔开。

也可以是 table_factor 或 joined_table。即一个表引用可以是一个简单的表，也可以是多个表连接的结果。

table_name：指定要从中检索数据的表或视图的名称。OceanBase 数据库支持使用 DBLink 来查询其他数据库中的表（table_name@dblink_name），更多有关使用 DBLink 的介绍信息，参见通过 DBLink 访问远端数据库中的数据。

说明

该内容仅适用于 OceanBase 数据库企业版。OceanBase 数据库社区版暂不支持 DBLink 功能。
PARTITION (partition_name_list)：可选项，用来指定特定分区的查询。
partition_name_list：表示分区名称的列表。partition_name 表示分区名称。

table_factor（表因子）

最基本的表引用单元。可以是以下几种形式：

table_name [PARTITION (partition_name_list)] [[AS] table_alias_name]：一个实际的表名，可以选择性地指定分区（PARTITION 子句）以及表别名（AS table_alias_name）。
table_subquery [AS] table_alias_name]：一个子查询，可以给子查询的结果指定别名。更多有关子查询的介绍，参见子查询。
(table_references)：用括号括起来的 table_references，允许嵌套使用表引用。

joined_table（连接表）

表示多个表连接的结果。可以是以下几种形式：

table_reference：单个表引用（虽然叫连接表，但也可以是单个表）。
table_reference1, table_reference2 [WHERE join_condition]：两个表引用，使用逗号分隔，并通过 WHERE 子句指定连接条件（隐式连接）。
table_reference1 {INNER} JOIN table_reference2：两个表引用，使用 INNER JOIN（内连接）。
table_reference1 {LEFT | RIGHT | FULL} [OUTER] JOIN table_reference2 {ON join_condition | WHERE join_condition}：两个表引用，使用 LEFT OUTER JOIN（左外连接）、RIGHT OUTER JOIN（右外连接）或 FULL OUTER JOIN（全外连接），并通过 ON 子句或 WHERE 子句指定连接条件。
table_reference1 {LEFT | RIGHT | FULL} [OUTER] JOIN table_reference2：两个表引用，使用 LEFT OUTER JOIN、RIGHT OUTER JOIN 或 FULL OUTER JOIN，自然连接会自动基于同名列进行连接。
table_reference1 NATURAL [INNER] JOIN table_reference2：自然内连接，自动基于两表同名列进行等值连接。
table_reference1 NATURAL {LEFT | RIGHT | FULL} [OUTER] JOIN table_reference2：自然外连接（左/右/全），自动基于两表同名列进行等值连接，无需指定 ON 或 USING 子句。

join_condition（连接条件）

用于指定表连接的条件。可以是以下两种形式：

expression：一个表达式，例如 table1.column1 = table2.column2 AND table1.column3 > 10。
column_name1 = column_name2：两个列名之间的等值比较，例如 table1.column1 = table2.column1。

示例

-- 创建国家表
CREATE TABLE countries (
    country_id VARCHAR2(2) PRIMARY KEY,
    country_name VARCHAR2(40),
    region_id NUMBER
);

-- 创建地点表
CREATE TABLE locations (
    location_id NUMBER PRIMARY KEY,
    street_address VARCHAR2(40),
    postal_code VARCHAR2(12),
    city VARCHAR2(30),
    state_province VARCHAR2(25),
    country_id VARCHAR2(2),
    CONSTRAINT loc_country_fk FOREIGN KEY (country_id)
    REFERENCES countries(country_id)
);

-- 创建部门表
CREATE TABLE departments (
    department_id NUMBER PRIMARY KEY,
    department_name VARCHAR2(30),
    manager_id NUMBER,
    location_id NUMBER,
    CONSTRAINT dept_loc_fk FOREIGN KEY (location_id)
    REFERENCES locations(location_id)
);

-- 创建员工表
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(20),
    last_name VARCHAR2(25),
    email VARCHAR2(25),
    phone_number VARCHAR2(20),
    hire_date DATE,
    job_id VARCHAR2(10),
    salary NUMBER(8,2),
    commission_pct NUMBER(2,2),
    manager_id NUMBER,
    department_id NUMBER,
    CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
    REFERENCES departments(department_id),
    CONSTRAINT emp_manager_fk FOREIGN KEY (manager_id)
    REFERENCES employees(employee_id)
);

-- 插入国家数据
INSERT INTO countries VALUES ('US', 'United States', 2);
INSERT INTO countries VALUES ('CA', 'Canada', 2);

-- 插入地点数据
INSERT INTO locations VALUES (
    1000,
    '1297 Via Cola di Rie',
    '00989',
    'Roma',
    'Lazio',
    'US'
);
INSERT INTO locations VALUES (
    1100,
    '93091 Sulfur Springs',
    '10925',
    'Toronto',
    'Ontario',
    'CA'
);

-- 插入部门数据
INSERT INTO departments VALUES (10, 'Administration', NULL, 1000);
INSERT INTO departments VALUES (20, 'Marketing', NULL, 1100);

-- 插入员工数据
INSERT INTO employees VALUES (
    100,
    'Steven',
    'King',
    'SKING',
    '515.123.4567',
    TO_DATE('17-06-2003', 'DD-MM-YYYY'),
    'AD_PRES',
    24000,
    NULL,
    NULL,
    10
);
INSERT INTO employees VALUES (
    101,
    'Neena',
    'Kochhar',
    'NKOCHHAR',
    '515.123.4568',
    TO_DATE('21-09-2005', 'DD-MM-YYYY'),
    'AD_VP',
    17000,
    NULL,
    100,
    10
);

-- SQL查询示例

-- 1. 基本内连接
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- 2. 多表连接
SELECT e.employee_id,
       e.first_name,
       d.department_name,
       l.city,
       c.country_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN locations l ON d.location_id = l.location_id
INNER JOIN countries c ON l.country_id = c.country_id;

-- 3. 左外连接
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON e.department_id = d.department_id;

-- 4. 自连接（查询员工及其经理）
SELECT
    e1.employee_id,
    e1.first_name || ' ' || e1.last_name AS employee_name,
    e2.first_name || ' ' || e2.last_name AS manager_name
FROM employees e1
LEFT OUTER JOIN employees e2 ON e1.manager_id = e2.employee_id;

-- RIGHT JOIN（右外连接）
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

-- FULL OUTER JOIN（全外连接）
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

-- USING子句连接
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d USING (department_id);

-- NATURAL LEFT JOIN（自然左外连接）
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
NATURAL LEFT JOIN departments d;

-- NATURAL RIGHT JOIN（自然右外连接）
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
NATURAL RIGHT JOIN departments d;

-- NATURAL FULL JOIN（自然全外连接）
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
NATURAL FULL JOIN departments d;

描述