JOIN clause|V4.3.5| docs|Distributed Database

JOIN clause

Last Updated：2025-11-14 07:33:32 Updated

Description

The JOIN clause is used to join two or more tables based on the related columns in the tables.

Privilege requirements

To execute a table join (JOIN), the current user must have the SELECT privilege on at least one of the JOIN tables. For more information about privileges in OceanBase Database, see Privilege types in Oracle mode.

Syntax

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

join_condition:
    expression
    | column_name1 = column_name2

Arguments

table_references (table references)

A comma-separated list of one or more table references. In other words, in a SELECT statement, you can specify multiple tables in the FROM clause, separating them with commas.

It can also be a table factor or a joined table. In other words, a table reference can be a single table or the result of joining multiple tables.

table_name: the name of the table or view from which to retrieve data. OceanBase Database allows you to use a DBLink to query a table in another database (table_name@dblink_name). For more information about DBLinks, see Access data in a remote database by using a DBLink.

Note

This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support the DBLink feature.
PARTITION (partition_name_list): an optional clause to specify a query on specific partitions.
partition_name_list: a list of partition names. partition_name indicates a partition name.

table_factor (table factor)

The most basic table reference. It can be in one of the following forms:

table_name [PARTITION (partition_name_list)] [[AS] table_alias_name]: the name of a table, with the option to specify partitions (PARTITION clause) and a table alias (AS table_alias_name).
table_subquery [AS] table_alias_name]: a subquery, with the option to alias the result of the subquery. For more information about subqueries, see Subqueries.
(table_references): a set of table references enclosed in parentheses, with the option to nest table references.

joined_table (joined tables)

The result of joining multiple tables. It can be in one of the following forms:

table_reference: a single table reference (although called a joined table, it can be a single table).
table_reference1, table_reference2 [WHERE join_condition]: two table references separated by a comma, with a join condition specified in the WHERE clause (implicit join).
table_reference1 {INNER} JOIN table_reference2: two table references joined by using an INNER JOIN (inner join).
table_reference1 {LEFT | RIGHT | FULL} [OUTER] JOIN table_reference2 {ON join_condition | WHERE join_condition}: two table references joined by using a LEFT OUTER JOIN (left outer join), RIGHT OUTER JOIN (right outer join), or FULL OUTER JOIN (full outer join). A join condition can be specified by using the ON or WHERE clause.
table_reference1 {LEFT | RIGHT | FULL} [OUTER] JOIN table_reference2: two table references joined by using a LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN. A natural join will automatically join columns with the same name.

join_condition (join condition)

The condition for joining tables. It can be in one of the following two forms:

expression: an expression, such as table1.column1 = table2.column2 AND table1.column3 > 10.
column_name1 = column_name2: an equality comparison between two column names, such as table1.column1 = table2.column1.

Examples

-- Create a table named COUNTRIES.
CREATE TABLE countries (
    country_id VARCHAR2(2) PRIMARY KEY,
    country_name VARCHAR2(40),
    region_id NUMBER
);

-- Create a table named LOCATIONS.
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 a table named DEPARTMENTS.
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 a table named EMPLOYEES.
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 data into the COUNTRIES table.
INSERT INTO countries VALUES ('US', 'United States', 2);
INSERT INTO countries VALUES ('CA', 'Canada', 2);

-- Insert data into the LOCATIONS table.
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 data into the DEPARTMENTS table.
INSERT INTO departments VALUES (10, 'Administration', NULL, 1000);
INSERT INTO departments VALUES (20, 'Marketing', NULL, 1100);

-- Insert data into the EMPLOYEES table.
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 query examples

-- 1. Basic inner join
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. Multi-table join
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. Left outer join
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. Self join (query employees and their managers)
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 (right outer 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 (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 clause join
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
JOIN departments d USING (department_id);