Joins, Subqueries and Set Operations

Real-world queries almost always involve multiple tables. Oracle provides joins to combine rows from related tables, subqueries to nest one query inside another, and set operations to combine result sets. This lesson covers all three techniques.

---

Joins

INNER JOIN

Returns only rows that have matching values in both tables:

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

LEFT OUTER JOIN

Returns all rows from the left table, plus matching rows from the right table (NULL where no match):

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

RIGHT OUTER JOIN

Returns all rows from the right table, plus matching rows from the left table:

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

FULL OUTER JOIN

Returns all rows from both tables, with NULLs where there is no match:

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

CROSS JOIN

Returns the Cartesian product — every row from the first table paired with every row from the second:

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

Self Join

Joins a table to itself — useful for hierarchical data:

SELECT e.first_name AS employee,
       m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

---

Join Summary

Join Type	Returns
INNER JOIN	Matching rows from both tables
LEFT OUTER JOIN	All left rows + matching right rows
RIGHT OUTER JOIN	All right rows + matching left rows
FULL OUTER JOIN	All rows from both tables
CROSS JOIN	Cartesian product (every combination)
Self Join	A table joined to itself

---

Oracle Traditional Join Syntax

Oracle also supports a legacy join syntax using the WHERE clause and (+) for outer joins:

-- Inner join (traditional)
SELECT e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- Left outer join (traditional)
SELECT e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);

Tip: The ANSI JOIN syntax (JOIN ... ON) is recommended for clarity and portability.

---

NATURAL JOIN and USING

-- NATURAL JOIN — joins on all columns with the same name
SELECT first_name, department_name
FROM employees NATURAL JOIN departments;

-- USING — specify the common column
SELECT first_name, department_name
FROM employees JOIN departments USING (department_id);

Tip: Avoid NATURAL JOIN in production — adding a column can change the join condition unexpectedly.

---

Subqueries

A subquery is a SELECT statement nested inside another statement.

Single-Row Subquery

Returns one row — use with =, <, >, etc.:

-- Employee with the highest salary
SELECT first_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

Multi-Row Subquery

Returns multiple rows — use with IN, ANY, ALL:

-- Employees in departments located in London
SELECT first_name, department_id
FROM employees
WHERE department_id IN (
  SELECT department_id
  FROM departments
  WHERE location_id = 2400
);

ANY and ALL

-- Salary greater than any IT programmer
SELECT first_name, salary
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG');

-- Salary greater than all IT programmers
SELECT first_name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG');

Correlated Subquery