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