SQL FULL OUTER JOIN Tutorial 🎯

Welcome to our comprehensive guide on SQL FULL OUTER JOIN! This tutorial is designed to help both beginners and intermediate learners understand this powerful SQL operation. Let's dive in!

What is a FULL OUTER JOIN? 📝

A FULL OUTER JOIN combines rows from two tables that match on a common column, including rows where there are no matches. It returns all records when there is a match in either left, right, or both tables.

Basic Syntax 💡

The basic syntax for a FULL OUTER JOIN is as follows:

sql

SELECT *
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;

Example 📝

Let's consider two tables, Employees and Salaries, with common column EmployeeID.

sql

-- Employees table
CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

-- Salaries table
CREATE TABLE Salaries (
    EmployeeID INT,
    Salary DECIMAL(10, 2),
    Year INT
);

Now, let's add some data:

sql

-- Insert data into Employees table
INSERT INTO Employees (EmployeeID, FirstName, LastName)
VALUES (1, 'John', 'Doe'), (2, 'Jane', 'Smith'), (3, 'Alice', 'Johnson');

-- Insert data into Salaries table
INSERT INTO Salaries (EmployeeID, Salary, Year)
VALUES (1, 50000, 2018), (2, 55000, 2018);

We've missed data for employee 3 in the Salaries table. Let's see how a FULL OUTER JOIN can help us find that missing data.

sql

SELECT *
FROM Employees
FULL OUTER JOIN Salaries
ON Employees.EmployeeID = Salaries.EmployeeID;

The result will be:

EmployeeID | FirstName | LastName | Salary | Year
-----------|-----------|----------|--------|------
1          | John      | Doe      | 50000  | 2018
2          | Jane      | Smith    | 55000  | 2018
3          | Alice     | Johnson  | NULL   | NULL

As you can see, the FULL OUTER JOIN has returned all records from the Employees table and the matching records from the Salaries table. The missing employee 3 from the Salaries table is also included, but with NULL values for Salary and Year.

Quiz 💡

Quick Quiz

Question 1 of 1

What does a FULL OUTER JOIN do in SQL?

Advanced Example 💡

In a real-world scenario, you might have multiple tables to combine using FULL OUTER JOIN. Here's an example with three tables: Departments, Employees, and Salaries.

sql

-- Departments table
CREATE TABLE Departments (
    DepartmentID INT,
    DepartmentName VARCHAR(50)
);

-- Employees table
CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT
);

-- Salaries table
CREATE TABLE Salaries (
    EmployeeID INT,
    Salary DECIMAL(10, 2),
    Year INT
);

Now, let's add some data:

sql

-- Insert data into Departments table
INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES (1, 'HR'), (2, 'IT'), (3, 'Finance');

-- Insert data into Employees table
INSERT INTO Employees (EmployeeID, FirstName, LastName, DepartmentID)
VALUES (1, 'John', 'Doe', 1), (2, 'Jane', 'Smith', 2), (3, 'Alice', 'Johnson', NULL);

-- Insert data into Salaries table
INSERT INTO Salaries (EmployeeID, Salary, Year)
VALUES (1, 50000, 2018), (2, 55000, 2018);

We've missed the department for employee 3. Let's use FULL OUTER JOIN to find that missing data.

sql

SELECT Employees.*, Departments.DepartmentName
FROM Employees
FULL OUTER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

The result will be:

EmployeeID | FirstName | LastName | DepartmentID | DepartmentName
-----------|-----------|----------|--------------|----------------
1          | John      | Doe      | 1            | HR
2          | Jane      | Smith    | 2            | IT
3          | Alice     | Johnson  | NULL          | NULL

As you can see, the FULL OUTER JOIN has returned all records from the Employees table and the matching records from the Departments table. The missing department for employee 3 is also included, but with NULL values for DepartmentID and DepartmentName.

Quiz 💡

Quick Quiz

Question 1 of 1

In the advanced example, which table contains the missing data for employee 3?

That's all for now! We hope this tutorial has helped you understand SQL FULL OUTER JOIN. Happy coding! 💻🌟