Lesson 22 Advanced Level
22 of 30 lessons

INNER & OUTER JOINS

Learn about different types of joins to combine data from multiple tables. Joins are essential for working with relational data.

Introduction

Joins are used to combine rows from two or more tables based on a related column between them. Oracle SQL supports different types of joins including INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

Oracle SQL supports different types of joins including INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

INNER JOIN

Returns rows when there is a match in both tables. 

SELECT e.employee_id, e.first_name, d.dept_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.dept_id;

LEFT OUTER JOIN

Returns all rows from the left table and matched rows from the right table. Rows with no match in the right table return NULL. 

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

RIGHT OUTER JOIN

Returns all rows from the right table and matched rows from the left table. 

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

FULL OUTER JOIN

Returns rows when there is a match in one of the tables. It includes unmatched rows from both sides. 

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

Practice Task