Course contentsShow
← Computer Science
Lesson 1581 of 2,87236. Database FundamentalsFree lesson

Full Outer Join

Combining both left and right joins to return all rows from both tables with NULLs where needed.

Full Outer Join

What you'll learn: You'll learn how to combine all rows from two tables, keeping everything even when there's no match on either side.

The Complete Picture

A Full Outer Join is the union of a Left Outer Join and a Right Outer Join. While a left join keeps all rows from the left table and a right join keeps all rows from the right table, a full outer join keeps everything from both tables, filling in NULL where matches don't exist.

Think of it like merging two guest lists for a wedding where some people RSVP'd to the bride's side, some to the groom's side, and some to both. A full outer join shows everyone: matched couples sit together, but singles from either side still get a seat (with an empty chair next to them).

How It Works

When you perform a full outer join between two tables:

  1. All matching rows appear (like an inner join)
  2. All unmatched rows from the left table appear with NULL for right-side columns
  3. All unmatched rows from the right table appear with NULL for left-side columns

SQL Syntax

SELECT employees.name, departments.dept_name
FROM employees
FULL OUTER JOIN departments
ON employees.dept_id = departments.id;

This returns every employee (even those without departments) and every department (even those without employees). Unmatched records show NULL in the columns from the opposite table.

When to Use It

Full outer joins are ideal when you need a comprehensive view showing all data from both sides—for example, reconciling records, finding orphaned entries, or generating complete reports that highlight both matches and gaps.

Key Takeaway: A full outer join returns all rows from both tables, using NULL to fill gaps where no match exists on either side—it's the most inclusive join operation.