When working with Oracle Database, it’s very common to retrieve information from multiple tables. The SQL JOIN clause allows you to combine data across tables based on related columns.
In this beginner-friendly article, we’ll cover the basics of JOINs, including INNER JOIN, OUTER JOIN (LEFT and RIGHT), and SELF JOIN. We’ll use text-based diagrams and practical examples to help you understand exactly how JOINs work.
What is a JOIN?
A JOIN clause allows you to combine rows from two or more tables based on a related column between them. For example, you might want to join an employee table with a department table to display “which employee belongs to which department.”
Create Sample Tables
We’ll use the following two sample tables for our demonstration:
-- Departments table
CREATE TABLE departments (
dept_id NUMBER PRIMARY KEY,
dept_name VARCHAR2(50)
);
INSERT INTO departments VALUES (10, 'Administration');
INSERT INTO departments VALUES (20, 'Sales');
INSERT INTO departments VALUES (30, 'Development');
-- Employees table
CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50),
dept_id NUMBER
);
INSERT INTO employees VALUES (1, 'John Smith', 10);
INSERT INTO employees VALUES (2, 'Emily Johnson', 20);
INSERT INTO employees VALUES (3, 'Michael Davis', NULL);
SQL> set lin 1000
SQL> col dept_name for a30
SQL> select * from departments;
DEPT_ID DEPT_NAME
---------- ------------------------------
10 Administration
20 Sales
30 Development
SQL> col emp_name for a30
SQL> select * from employees;
EMP_ID EMP_NAME DEPT_ID
---------- ------------------------------ ----------
1 John Smith 10
2 Emily Johnson 20
3 Michael Davis
INNER JOIN
An INNER JOIN returns only rows that have matching values in both tables.
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id;
Result
| emp_name | dept_name |
|---|---|
| John Smith | Administration |
| Emily Johnson | Sales |
Diagram: INNER JOIN
employees departments
+---------+ +----------+
| dept_id | <---> | dept_id |
+---------+ +----------+
Only rows with matching dept_id in both tables are returned.
Rows like Michael Davis (with NULL dept_id) are excluded.
SQL> SELECT e.emp_name, d.dept_name
2 FROM employees e
3 INNER JOIN departments d
4 ON e.dept_id = d.dept_id;
EMP_NAME DEPT_NAME
------------------------------ ------------------------------
John Smith Administration
Emily Johnson Sales
OUTER JOIN
An OUTER JOIN returns unmatched rows as well, filling in NULLs for missing values.
LEFT OUTER JOIN
Returns all rows from the left table (employees), and matching rows from the right table (departments).
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.dept_id = d.dept_id;
Result
| emp_name | dept_name |
|---|---|
| John Smith | Administration |
| Emily Johnson | Sales |
| Michael Davis | NULL |
Diagram: LEFT OUTER JOIN
employees departments
+---------+ +----------+
| dept_id | <---> | dept_id |
+---------+ +----------+
All rows from employees are returned, even if no match is found.
SQL> SELECT e.emp_name, d.dept_name
2 FROM employees e
3 LEFT OUTER JOIN departments d
4 ON e.dept_id = d.dept_id;
EMP_NAME DEPT_NAME
------------------------------ ------------------------------
John Smith Administration
Emily Johnson Sales
Michael Davis
RIGHT OUTER JOIN
Returns all rows from the right table (departments), and matching rows from the left table (employees).
SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT OUTER JOIN departments d
ON e.dept_id = d.dept_id;
Result
| emp_name | dept_name |
|---|---|
| John Smith | Administration |
| Emily Johnson | Sales |
| NULL | Development |
Note: This is essentially a LEFT OUTER JOIN with the tables swapped.
SQL> SELECT e.emp_name, d.dept_name
2 FROM employees e
3 RIGHT OUTER JOIN departments d
4 ON e.dept_id = d.dept_id;
EMP_NAME DEPT_NAME
------------------------------ ------------------------------
John Smith Administration
Emily Johnson Sales
Development
SELF JOIN
A SELF JOIN joins a table to itself. For example, suppose each employee has a mentor.
-- Add a mentor_id column
ALTER TABLE employees ADD mentor_id NUMBER;
-- Assign John Smith as mentor to Emily
UPDATE employees SET mentor_id = 1 WHERE emp_id = 2;
-- Use SELF JOIN to show mentor name
SELECT e.emp_name AS mentee, m.emp_name AS mentor
FROM employees e
LEFT JOIN employees m
ON e.mentor_id = m.emp_id;
Result
| mentee | mentor |
|---|---|
| John Smith | NULL |
| Emily Johnson | John Smith |
| Michael Davis | NULL |
Diagram: SELF JOIN
employees e employees m
+-----------+ +---------+
| mentor_id | <---> | emp_id |
+-----------+ +---------+
Matches mentor_id from one record to emp_id in the same table.
SQL> ALTER TABLE employees ADD mentor_id NUMBER;
The table has been changed.
SQL> UPDATE employees SET mentor_id = 1 WHERE emp_id = 2;
1 row updated.
SQL> select * from employees;
EMP_ID EMP_NAME DEPT_ID MENTOR_ID
---------- ------------------------------ ---------- ----------
1 John Smith 10
2 Emily Johnson 20 1
3 Michael Davis
SQL> commit;
The commit has been completed.
SQL> col mentee for a30
SQL> col mentor for a30
SQL> SELECT e.emp_name AS mentee, m.emp_name AS mentor
2 FROM employees e
3 LEFT JOIN employees m
4 ON e.mentor_id = m.emp_id;
MENTEE MENTOR
------------------------------ ------------------------------
John Smith
Emily Johnson John Smith
Michael Davis
Oracle-Specific Legacy Syntax (Not Recommended)
Oracle once used the (+) symbol to indicate OUTER JOINs. Although still supported, this syntax is discouraged in favor of standard ANSI JOINs.
SELECT e.emp_name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.dept_id(+);
SQL> SELECT e.emp_name, d.dept_name
2 FROM employees e, departments d
3 WHERE e.dept_id = d.dept_id(+);
EMP_NAME DEPT_NAME
------------------------------ ------------------------------
John Smith Administration
Emily Johnson Sales
Michael Davis
Summary
| JOIN Type | Description |
|---|---|
| INNER JOIN | Returns only matching rows in both tables |
| LEFT OUTER JOIN | Returns all left rows + matched right rows |
| RIGHT OUTER JOIN | Returns all right rows + matched left rows |
| SELF JOIN | Joins a table with itself |
Understanding JOINs is essential for working with relational databases like Oracle. Once you master how to join tables effectively, your SQL skills will improve dramatically.
[reference]
Joins
コメント