Oracle SQL JOINs

Oracle SQL JOINs: Complete Guide

JOINs let you combine data from two or more tables based on related columns. They are one of the most important and frequently used features in Oracle SQL. Understanding the different types of JOINs and when to use each one is essential for writing accurate and efficient queries.

Learning Objectives
By the end of this guide you will understand INNER JOIN, all OUTER JOIN types, CROSS JOIN, NATURAL JOIN, the USING clause, and the legacy (+) outer join syntax, along with the common pitfalls and best practices for each.

1. INNER JOIN — Matched Rows Only

INNER JOIN returns only the rows that have matching values in both tables. Rows without a match in either table are excluded from the result.

SELECT e.last_name, d.department_name
FROM   employees e
JOIN   departments d
  ON   e.department_id = d.department_id;

Key behavior: If an employee has a NULL department_id or a department has no employees, those rows are not included in the result.

2. LEFT OUTER JOIN — All Rows from the Left Table

LEFT OUTER JOIN returns every row from the left table and matching rows from the right table. If there is no match on the right side, the right-side columns return as NULL.

SELECT e.last_name, d.department_name
FROM   employees e
LEFT OUTER JOIN departments d
  ON   e.department_id = d.department_id;

3. RIGHT OUTER JOIN — All Rows from the Right Table

RIGHT OUTER JOIN is the mirror image of LEFT OUTER JOIN. It returns all rows from the right table and matching rows from the left table.

SELECT e.last_name, d.department_name
FROM   employees e
RIGHT OUTER JOIN departments d
  ON   e.department_id = d.department_id;

4. FULL OUTER JOIN — All Rows from Both Tables

FULL OUTER JOIN returns every row from both tables. Where there is no match, the columns from the missing side return as NULL.

SELECT e.last_name, d.department_name
FROM   employees e
FULL OUTER JOIN departments d
  ON   e.department_id = d.department_id;

5. CROSS JOIN — Cartesian Product

CROSS JOIN returns every possible combination of rows from both tables. No join condition is used.

SELECT e.last_name, d.department_name
FROM   employees e
CROSS JOIN departments d;

6. NATURAL JOIN and USING Clause

NATURAL JOIN automatically joins on all columns that have the same name in both tables. The USING clause lets you explicitly choose which column to join on.

SELECT last_name, department_name
FROM   employees
NATURAL JOIN departments;

SELECT last_name, department_name
FROM   employees
JOIN   departments USING (department_id);

Important rule: When using NATURAL JOIN or USING, the join column cannot be qualified with a table alias or table name in the SELECT list or WHERE clause.

7. Oracle Legacy Outer Join Syntax (+)

Oracle’s older syntax uses the (+) operator to indicate the optional side of an outer join. This syntax still appears on the exam, so you need to recognize it.

-- Legacy LEFT OUTER JOIN
SELECT e.last_name, d.department_name
FROM   employees e, departments d
WHERE  e.department_id = d.department_id(+);

-- Legacy RIGHT OUTER JOIN
SELECT e.last_name, d.department_name
FROM   employees e, departments d
WHERE  e.department_id(+) = d.department_id;

8. Interactive Practice Quiz

Q1. The EMPLOYEES table has 107 rows. One employee has a NULL department_id and one department has no employees. How many rows does this INNER JOIN return?

A. 107 rows B. 108 rows C. 106 rows D. 105 rows

Q2. Which query will raise an error?

A. SELECT e.last_name, department_id FROM employees e JOIN departments d USING (department_id); B. SELECT e.last_name, d.department_id FROM employees e JOIN departments d USING (department_id); C. SELECT last_name, department_name FROM employees NATURAL JOIN departments; D. SELECT e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id = d.department_id;

Q3. What does the legacy (+) operator indicate in this syntax?
WHERE e.department_id = d.department_id(+)

A. All department rows are returned B. All employee rows are returned C. Only matched rows are returned D. The query will raise an error

Conclusion

JOINs are the foundation of almost every useful query in Oracle SQL. Mastering INNER JOIN, the three OUTER JOIN types, CROSS JOIN, NATURAL JOIN, and the USING clause gives you the tools to combine data from multiple tables accurately and efficiently.

Practice writing queries with different JOIN types and pay special attention to how unmatched rows are handled in OUTER JOINs — this is where most mistakes occur.