“Table Joins (JOIN)” are an unavoidable part of working with databases.
“Should I use an inner join or an outer join, and what will the result be?” “What is the logical reason for data disappearing or increasing?” These questions can be clarified by verifying them with appropriate sample data.
In this article, we will create a unique verification table (all data in English) that makes it easy to understand the behavior of joins, and thoroughly explain the mechanism of joins (INNER, LEFT/RIGHT, FULL, SELF) in Oracle Database. Let’s solidify the basic knowledge that works in English environments and global projects.
Conclusion: List of Join Patterns
First, grasp the logical operation image of each join.
| Join Type | Keyword | Operation Overview | Handling NULL |
| Inner Join | INNER JOIN | Intersection | Retrieves only rows where the join condition matches. Mismatched rows are excluded. |
| Left Outer Join | LEFT JOIN | Left Outer (Left table priority) | Retrieves all rows from the left table and joins only matching rows from the right table. Mismatches become NULL. |
| Right Outer Join | RIGHT JOIN | Right Outer (Right table priority) | Retrieves all rows from the right table and joins only matching rows from the left table. Mismatches become NULL. |
| Full Outer Join | FULL JOIN | Union | Retrieves rows if they exist in either the left or right table. Mismatches become NULL. |
| Self Join | (Same Table) | Recursive Join | Joins within the same table (e.g., representing hierarchical structures like Boss/Subordinate). |
| Cross Join | CROSS JOIN | Cartesian Product | Generates every combination of all rows × all rows. |
[What you will learn in this article]
- Structural explanation of basic join syntax (
FROM,JOIN,ON) - The difference in the number of retrieved rows between
INNERandOUTER - Actual machine verification using custom sample tables (English data)
1. For Beginners: Dissecting the Basic Join Syntax
We will explain the ANSI join syntax (writing style using the JOIN keyword), which has been adopted as standard since Oracle 9i.
Basic Syntax Template
SELECT
A.Column_Name,
B.Column_Name
FROM
Table_A Alias_A
【JOIN TYPE】 JOIN Table_B Alias_B
ON A.Key_Column = B.Key_Column;
3 Points of Syntax
- FROM clause and JOIN clause:
- Describe the “Main” table (Left side) in
FROM. - Describe the table you want to “Join” (Right side) in
JOIN.
- Describe the “Main” table (Left side) in
- ON clause (Join Condition):
- Specifies the condition columns that associate the two tables.
- Example:
ON EMP.DEPT_ID = DEPT.DEPT_ID
- Table Aliases:
- It is common to define short aliases (like
E) such asFROM EMP_SAMPLE E. - By writing
E.NAMEwhen specifying columns, you clarify which table the column belongs to.
- It is common to define short aliases (like
2. Creating Verification Data
Since it is difficult to visualize the behavior of join mismatches (NULL) with the standard SCOTT schema, we will create our own tables with the following characteristics. Considering deployment to English articles, the data content consists entirely of single-byte characters (English).
- DEPT_SAMPLE (Departments): 4 departments. One of them is a department with no employees (HR).
- EMP_SAMPLE (Employees): 5 employees. One of them is an employee with no assigned department (Martin).
Please execute the following SQL in your environment.
-- 1. Create Department Table (DEPT_SAMPLE)
CREATE TABLE DEPT_SAMPLE (
DEPT_ID NUMBER PRIMARY KEY,
DEPT_NAME VARCHAR2(20)
);
INSERT INTO DEPT_SAMPLE VALUES (10, 'Sales'); -- Has employees
INSERT INTO DEPT_SAMPLE VALUES (20, 'Research'); -- Has employees
INSERT INTO DEPT_SAMPLE VALUES (30, 'Accounting'); -- Has employees
INSERT INTO DEPT_SAMPLE VALUES (40, 'HR'); -- No employees (No match in EMP)
COMMIT;
-- 2. Create Employee Table (EMP_SAMPLE)
CREATE TABLE EMP_SAMPLE (
EMP_ID NUMBER PRIMARY KEY,
EMP_NAME VARCHAR2(20),
DEPT_ID NUMBER,
BOSS_ID NUMBER
);
INSERT INTO EMP_SAMPLE VALUES (1, 'Smith', 10, NULL); -- Sales, No Boss
INSERT INTO EMP_SAMPLE VALUES (2, 'Allen', 10, 1); -- Sales, Boss is Smith
INSERT INTO EMP_SAMPLE VALUES (3, 'Ward', 20, 1); -- Research, Boss is Smith
INSERT INTO EMP_SAMPLE VALUES (4, 'Jones', 30, 2); -- Accounting, Boss is Allen
INSERT INTO EMP_SAMPLE VALUES (5, 'Martin', NULL, 1); -- No Dept (NULL), Boss is Smith
COMMIT;
Data Confirmation:
SQL> SELECT * FROM DEPT_SAMPLE;
DEPT_ID DEPT_NAME
---------- --------------------
10 Sales
20 Research
30 Accounting
40 HR
SQL> SELECT * FROM EMP_SAMPLE;
EMP_ID EMP_NAME DEPT_ID BOSS_ID
---------- -------------------- ---------- ----------
1 Smith 10 (null)
2 Allen 10 1
3 Ward 20 1
4 Jones 30 2
5 Martin (null) 1
Note: In this article, set null (null) is executed in SQLPlus to output NULL values explicitly.*
3. Inner Join (INNER JOIN)
Retrieves only data where the join key (DEPT_ID) matches in both tables.
Rows with no join partner are excluded from the search results.
SELECT
E.EMP_NAME,
D.DEPT_NAME
FROM EMP_SAMPLE E
INNER JOIN DEPT_SAMPLE D
ON E.DEPT_ID = D.DEPT_ID;
Execution Result:
SQL> SELECT
2 E.EMP_NAME,
3 D.DEPT_NAME
4 FROM EMP_SAMPLE E
5 INNER JOIN DEPT_SAMPLE D
6 ON E.DEPT_ID = D.DEPT_ID;
EMP_NAME DEPT_NAME
-------------------- --------------------
Smith Sales
Allen Sales
Ward Research
Jones Accounting
Explanation:
- Martin (DEPT_ID is NULL), whose department is undecided, was excluded.
- HR (DEPT_ID = 40), which has no employees, was also excluded.
- This is the characteristic of
INNER JOIN(strict matching).
4. Outer Join (LEFT / RIGHT JOIN)
Used when “you want to keep the rows of the base table even if they don’t match.”
4-1. Left Outer Join (LEFT JOIN)
Displays all rows from the table in the FROM clause (Left side).
-- Main Table: EMP_SAMPLE (Left)
SELECT
E.EMP_NAME,
D.DEPT_NAME
FROM EMP_SAMPLE E -- Left Side (Preserve all rows)
LEFT JOIN DEPT_SAMPLE D -- Right Side
ON E.DEPT_ID = D.DEPT_ID;
Execution Result:
SQL> SELECT
2 E.EMP_NAME,
3 D.DEPT_NAME
4 FROM EMP_SAMPLE E
5 LEFT JOIN DEPT_SAMPLE D
6 ON E.DEPT_ID = D.DEPT_ID;
EMP_NAME DEPT_NAME
-------------------- --------------------
Smith Sales
Allen Sales
Ward Research
Jones Accounting
Martin (null)
Explanation:
- Martin, who was excluded in the INNER JOIN, is displayed.
- Since there is no corresponding
DEPT_NAME, the value becomes(null).
4-2. Right Outer Join (RIGHT JOIN)
Displays all rows from the table in the JOIN clause (Right side).
-- Main Table: DEPT_SAMPLE (Right)
SELECT
E.EMP_NAME,
D.DEPT_NAME
FROM EMP_SAMPLE E -- Left Side
RIGHT JOIN DEPT_SAMPLE D -- Right Side (Preserve all rows)
ON E.DEPT_ID = D.DEPT_ID;
Execution Result:
SQL> SELECT
2 E.EMP_NAME,
3 D.DEPT_NAME
4 FROM EMP_SAMPLE E
5 RIGHT JOIN DEPT_SAMPLE D
6 ON E.DEPT_ID = D.DEPT_ID;
EMP_NAME DEPT_NAME
-------------------- --------------------
Smith Sales
Allen Sales
Ward Research
Jones Accounting
(null) HR
Explanation:
- This time, the HR department is displayed.
- Since there is no belonging
EMP_NAME, the value becomes(null).
5. Full Outer Join (FULL JOIN)
If rows exist in either the left or right table, all of them are displayed in the result.
SELECT E.EMP_NAME, D.DEPT_NAME
FROM EMP_SAMPLE E
FULL JOIN DEPT_SAMPLE D
ON E.DEPT_ID = D.DEPT_ID;
Execution Result:
SQL> SELECT E.EMP_NAME, D.DEPT_NAME
2 FROM EMP_SAMPLE E
3 FULL JOIN DEPT_SAMPLE D
4 ON E.DEPT_ID = D.DEPT_ID;
EMP_NAME DEPT_NAME
-------------------- --------------------
Smith Sales
Allen Sales
Ward Research
Jones Accounting
Martin (null)
(null) HR
Both Martin (no department) and HR (no employees) are displayed. This is often used for checking data inconsistencies or extracting differences (Diff) between master tables.
6. Self Join (SELF JOIN)
Joins a table to itself. The EMP_SAMPLE table has a BOSS_ID (Boss’s ID), which references the EMP_ID in the same table. We use this to create a list of “Subordinates and Bosses.”
SELECT
Worker.EMP_NAME AS WORKER_NAME,
Boss.EMP_NAME AS BOSS_NAME
FROM EMP_SAMPLE Worker -- 1st instance (As Worker)
LEFT JOIN EMP_SAMPLE Boss -- 2nd instance (As Boss)
ON Worker.BOSS_ID = Boss.EMP_ID;
Execution Result:
SQL> SELECT
2 Worker.EMP_NAME AS WORKER_NAME,
3 Boss.EMP_NAME AS BOSS_NAME
4 FROM EMP_SAMPLE Worker
5 LEFT JOIN EMP_SAMPLE Boss
6 ON Worker.BOSS_ID = Boss.EMP_ID;
WORKER_NAME BOSS_NAME
-------------------- --------------------
Allen Smith
Ward Smith
Martin Smith
Jones Allen
Smith (null)
Explanation:
- It is important to use
LEFT JOIN. IfINNER JOINwere used, Smith, who has no boss (BOSS_ID is NULL), would disappear from the results. - Since the same table is used, distinct aliases like
WorkerandBossmust be defined to distinguish them.
7. Cross Join (CROSS JOIN)
Generates every combination (Cartesian Product) without specifying a join condition.
SELECT E.EMP_NAME, D.DEPT_NAME
FROM EMP_SAMPLE E
CROSS JOIN DEPT_SAMPLE D;
Execution Result:
SQL> SELECT E.EMP_NAME, D.DEPT_NAME
2 FROM EMP_SAMPLE E
3 CROSS JOIN DEPT_SAMPLE D;
EMP_NAME DEPT_NAME
-------------------- --------------------
Smith Sales
Allen Sales
Ward Sales
Jones Sales
Martin Sales
Smith Research
Allen Research
Ward Research
Jones Research
Martin Research
Smith Accounting
Allen Accounting
Ward Accounting
Jones Accounting
Martin Accounting
Smith HR
Allen HR
Ward HR
Jones HR
Martin HR
20 rows selected.
Result: 5 Employees × 4 Departments = 20 Rows
This is used for generating massive amounts of test data or creating matrix tables that cover all patterns.
8. Troubleshooting and Notes
Missing Join Conditions (Cartesian Product)
If you forget to write the ON clause, it unintentionally becomes the same state as a CROSS JOIN (Cartesian Product), and the number of rows increases explosively. This is called a “Cartesian product” and is a major cause of performance degradation.
About the “(+)” Operator
Oracle’s proprietary join syntax (e.g., WHERE E.DEPT_ID = D.DEPT_ID(+)) is equivalent to LEFT JOIN. While it still works, using the JOIN clause (ANSI syntax) is recommended for the following reasons:
- No compatibility with other RDBMS (SQL Server, PostgreSQL, etc.).
FULL JOINcannot be expressed.- Join conditions and filter conditions are mixed, reducing readability.
Summary
| Join Type | SQL Syntax | Characteristics of Result |
| Inner Join | INNER JOIN | Retrieves only common data (Excludes both Martin and HR) |
| Left Outer Join | LEFT JOIN | Maintains the Left table (Martin remains, HR excluded) |
| Right Outer Join | RIGHT JOIN | Maintains the Right table (Martin excluded, HR remains) |
| Full Outer Join | FULL JOIN | Maintains both (Both Martin and HR remain) |
| Self Join | (Same Table) | Represents hierarchical structures like Boss/Subordinate |
In practice, INNER JOIN or LEFT JOIN are used in 90% of cases.
First, please run the code with the sample data provided to verify the differences between these two.
Also, it is possible to perform joins using tables existing locally and tables existing in a remote DB using DBLINK.
This article explains using Oracle Database 19c (screens and default values may differ in other versions).
コメント