Oracle SQL: Correct Way to Write Joins for 3 or More Tables and Non-Equi Joins

In business system development and data analysis, there are frequent scenarios where you need to retrieve information by combining not just one table, but “3 or more tables.” Additionally, being able to master “Non-equi joins”—which use range conditions instead of simple equals (=)—will significantly improve your SQL expressiveness.

This article explains the procedure for joining multiple tables in Oracle SQL and how to use non-equi joins useful in practice, accompanied by sample code that runs on an actual environment.

Conclusion: Key Points for Joining Multiple Tables
Basic Knowledge of JOIN
Practice 1: Joining 3 or More Tables
Practice 2: Mechanism of Non-Equi Join
1. Reference: Substitution with CASE Expression (Method without joining)
Application: Joining with a Database Link (DB Link) Destination
1. What is a Database Link (DB Link)?
2. Execution Example: Joining Local EMP Table and Remote DEPT Table
Troubleshooting
Operational and Performance Considerations
FAQ: Frequently Asked Questions
Summary

Conclusion: Key Points for Joining Multiple Tables

Joining 3 or more: Expandable simply by continuing to write JOIN after the FROM clause.
Non-equi join: A join that uses operators other than = (such as BETWEEN, <, >) in the join condition.
Tip: First decide on the “main table,” then JOIN the related master tables one by one.
Caution: If you forget the join condition, it results in a “Cartesian product (Cross Join),” causing the data to balloon, so care is required.

Basic Knowledge of JOIN

What is a Table Join?

In a relational database, this refers to the operation of horizontally connecting multiple normalized (divided) tables based on common columns or conditions. In Oracle Database, ANSI syntax (INNER JOIN, LEFT JOIN, etc.) is primarily recommended.

What is a Non-Equi Join?

Usually, join conditions use the equality operator (=) like ON A.ID = B.ID, but joining using something other than the equality operator is called a “Non-equi join.” A representative example is a range join (BETWEEN), such as “If the salary is within this range, assign this grade.”

Prerequisite Data (DDL/DML for Verification)

Execute the following SQL to create tables and data for verification.

Note: We will use 3 tables: EMP (Employees), DEPT (Departments), and SALGRADE (Salary Grades).

-- Drop tables if they exist
-- DROP TABLE EMP;
-- DROP TABLE DEPT;
-- DROP TABLE SALGRADE;

-- 1. Create DEPT table
CREATE TABLE DEPT (
    DEPTNO NUMBER(2) PRIMARY KEY,
    DNAME  VARCHAR2(14),
    LOC    VARCHAR2(13)
);

INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT VALUES (20, 'RESEARCH',   'DALLAS');
INSERT INTO DEPT VALUES (30, 'SALES',      'CHICAGO');

-- 2. Create EMP table
CREATE TABLE EMP (
    EMPNO    NUMBER(4) PRIMARY KEY,
    ENAME    VARCHAR2(10),
    JOB      VARCHAR2(9),
    MGR      NUMBER(4),
    HIREDATE DATE,
    SAL      NUMBER(7, 2),
    COMM     NUMBER(7, 2),
    DEPTNO   NUMBER(2)
);

INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK',    7902, TO_DATE('1980-12-17', 'YYYY-MM-DD'), 800,  NULL, 20);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('1981-02-20', 'YYYY-MM-DD'), 1600, 300,  30);

-- 3. Create SALGRADE table
CREATE TABLE SALGRADE (
    GRADE NUMBER(1),
    LOSAL NUMBER(7),
    HISAL NUMBER(7)
);

INSERT INTO SALGRADE VALUES (1, 700,  1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);

COMMIT;

Practice 1: Joining 3 or More Tables

We will link the Department Name (DEPT) and Salary Grade (SALGRADE) to the Employee Information (EMP) simultaneously to create a single report.

Image of the Join

EMP + DEPT: Join where Department Number (DEPTNO) matches (Equi-join)
EMP + SALGRADE: Join where Salary (SAL) is between the grade’s lower limit (LOSAL) and upper limit (HISAL) (Non-equi join)

We combine these into a single query.

SELECT 
    E.ENAME AS Employee_Name, 
    E.JOB   AS Job_Title, 
    D.DNAME AS Dept_Name, 
    E.SAL   AS Salary,
    S.GRADE AS Salary_Grade
FROM 
    EMP E
-- Join 1: DEPT table (Equi-join)
INNER JOIN 
    DEPT D ON E.DEPTNO = D.DEPTNO
-- Join 2: SALGRADE table (Non-equi-join)
INNER JOIN 
    SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

Execution Result:

SQL> SELECT
  2      E.ENAME AS Employee_Name,
  3      E.JOB   AS Job_Title,
  4      D.DNAME AS Dept_Name,
  5      E.SAL   AS Salary,
  6      S.GRADE AS Salary_Grade
  7  FROM
  8      EMP E
  9  INNER JOIN
 10      DEPT D ON E.DEPTNO = D.DEPTNO
 11  INNER JOIN
 12      SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

EMPLOYEE_N JOB_TITLE DEPT_NAME          SALARY SALARY_GRADE
---------- --------- -------------- ---------- ------------
SMITH      CLERK     RESEARCH              800            1
ALLEN      SALESMAN  SALES                1600            3

Explanation

You can join 3 or even 4 tables by writing JOIN clauses continuously. The Oracle Database optimizer automatically determines the optimal join order (driving table, etc.) and executes it.

Practice 2: Mechanism of Non-Equi Join

The following part, which appeared casually in the previous example, is the implementation of a non-equi join.

JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL

Why is Non-Equi Join necessary?

The SALGRADE table does not have a common ID column that matches the EMP table. Instead, it holds range information such as “Grade 1 if between 700 and 1200.” In such cases, since you cannot join with =, you use BETWEEN or inequality signs (>=, <=) to find and join “rows where the value falls within the range.”

Reference: Substitution with CASE Expression (Method without joining)

The original article also introduced classification using the CASE statement. If you perform fixed judgment within SQL without joining tables, you write it as follows.

-- Use CASE expression instead of JOIN
SELECT 
    E.ENAME, 
    E.SAL,
    CASE 
        WHEN E.SAL > 2000 THEN 'HIGH'
        WHEN E.SAL BETWEEN 1000 AND 2000 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS SALARY_CATEGORY
FROM 
    EMP E;

Execution Result:

SQL> SELECT
  2      E.ENAME,
  3      E.SAL,
  4      CASE
  5          WHEN E.SAL > 2000 THEN 'HIGH'
  6          WHEN E.SAL BETWEEN 1000 AND 2000 THEN 'MEDIUM'
  7          ELSE 'LOW'
  8      END AS SALARY_CATEGORY
  9  FROM
 10      EMP E;

ENAME             SAL SALARY
---------- ---------- ------
SMITH             800 LOW
ALLEN            1600 MEDIUM

Differentiation:

Non-equi join: When thresholds or rank definitions are managed in a master table (like SALGRADE) and there is a possibility they will change frequently.
CASE expression: When you want to temporarily classify data for analysis queries, or when a master table does not exist.

Application: Joining with a Database Link (DB Link) Destination

In Oracle Database, it is possible to join with tables in another database across a network. The “Database Link (DB Link)” function realizes this.

What is a Database Link (DB Link)?

It is a defined object for accessing a remote database from the currently connected database. By appending @link_name after the table name, you can treat it as if it were a local table.

Execution Example: Joining Local EMP Table and Remote DEPT Table

For example, when joining the Employee table in the headquarters DB (local) and the Department table in the branch DB (remote: TOKYO_HQ), you write it as follows.

SELECT 
    E.ENAME, 
    D.DNAME
FROM 
    EMP E
-- Join with remote DEPT table via DB Link
INNER JOIN 
    DEPT@TOKYO_HQ D ON E.DEPTNO = D.DEPTNO;

Cautions

Performance: Since it goes via the network, processing may become extremely slow if a large amount of data is joined.
Driving Site: Usually, data is collected and join processing is performed on the DB side that executed the query. If the data volume on the remote side is huge, tuning with hints like DRIVING_SITE may be necessary.

Troubleshooting

Here are common errors that occur in join operations and their remedies.

Error Code	Error Message Example	Cause and Remedy
ORA-00918	column ambiguously defined	The joined tables have the same column name (e.g., `DEPTNO`), and it is unclear which one is referred to. Specify with a table alias like `E.DEPTNO`.
ORA-00904	invalid identifier	A typo in the column name, or specifying the original table name for a column even though a table alias was assigned (e.g., writing `EMP.ENAME` after defining `FROM EMP E`).
Data Overload	(Returns massive rows without error)	Possibility of a “Cartesian product (Cross Join)” due to missing join conditions. Check if the `ON` clause is written correctly.

Operational and Performance Considerations

Utilizing Indexes

If there is no index on the join key (such as DEPTNO), a “Full Table Scan” reading the entire table occurs, potentially slowing down the join processing (Hash Join, etc.). It is standard practice to create indexes on foreign key columns.

Cost of Non-Equi Joins

Joins using BETWEEN or inequality signs tend to have higher processing costs compared to equi-joins. Pay attention to processing time when performing non-equi joins between large-scale tables with millions of rows or more.

Recommendation of ANSI Syntax

We recommend using the JOIN ... ON ... format rather than the old Oracle-proprietary join syntax (WHERE E.DEPTNO = D.DEPTNO). The join conditions and filter conditions (WHERE clause) are clearly separated, improving readability and reducing mistakes.

FAQ: Frequently Asked Questions

Q1. Can I join 4 or more tables?

A. Yes, it is possible. You just keep adding JOIN clauses. However, as the number of joins increases, the SQL becomes more complex, and performance tuning tends to become more difficult.

Q2. Some rows do not appear even though I joined them. Why?

A. This is because you are using INNER JOIN. An inner join displays only “cases where matching data exists in both tables.” If you want to display employees who do not belong to a department or employees with salaries outside the grade range, use LEFT JOIN (Outer Join).

Q3. Does the order of joins affect performance?

A. Because the Oracle Optimizer (CBO) is excellent, basically the order in which you write (FROM A JOIN B or FROM B JOIN A) does not affect the execution plan. Write in an order that is easy for humans to understand (Main Table → Related Master).

Summary

For joins of 3 or more, take your time and add JOIN one by one.
Non-equi join is a powerful weapon when you want to link with a master table by something other than ID (such as a range).
If you use DB Link join, you can transparently join with tables in another DB (but be careful of network load).
Always specify table aliases when column names overlap to prevent the error “ORA-00918”.

In practice, the ability to correctly join “transactions like the EMP table” and “masters like the DEPT table” is fundamental. Please try running the sample code from this time in your local environment to confirm the behavior of the joins.

This article explains the content targeting Oracle Database 19c (screens and default values may differ in other versions).

[reference]
Oracle Database SQL Language Reference, 19c