SQL FULL JOIN

Introduction

In this chapter, we will focus on the FULL JOIN operation in SQL. The FULL JOIN keyword returns all records when there is a match in either the left table (table1) or the right table (table2). The result is NULL from one side when there is no match. This chapter will cover the definition, syntax, and provide examples to help you understand how to use the FULL JOIN effectively.

What is a FULL JOIN?

The FULL JOIN keyword returns all records when there is a match in either left or right table. It combines the results of both LEFT JOIN and RIGHT JOIN. If there is no match, the result is NULL on the side that doesn't have a match.

Syntax for FULL JOIN

Basic Syntax

SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

• columns: The columns you want to retrieve.
• table1: The first table you want to join.
• table2: The second table you want to join.
• table1.column = table2.column: The condition that specifies how the tables should be joined.

Step-by-Step Example

Sample Tables

First, let's create two sample tables named employees and departments.

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Insert Sample Data

INSERT INTO departments (department_id, department_name)
VALUES
(1, 'HR'),
(2, 'Finance'),
(3, 'IT');

INSERT INTO employees (first_name, last_name, email, department_id, salary)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com', 1, 50000),
('Sita', 'Patel', 'sita.patel@example.com', 2, 60000),
('Arjun', 'Singh', 'arjun.singh@example.com', 1, 55000),
('Priya', 'Sharma', 'priya.sharma@example.com', 2, 62000),
('Ravi', 'Verma', 'ravi.verma@example.com', NULL, 50000);

Using FULL JOIN

To retrieve all employees and their department names, including those without a matching department and departments without a matching employee (note: FULL JOIN is not supported by all SQL databases):

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id;

Output

first_name	last_name	department_name
Ramesh	Kumar	HR
Sita	Patel	Finance
Arjun	Singh	HR
Priya	Sharma	Finance
Ravi	Verma	NULL
NULL	NULL	IT

In this example, all employees and all departments are included in the result set. Employees without a matching department_id in the departments table have NULL in the department_name column, and departments without a matching department_id in the employees table have NULL in the first_name and last_name columns.

Emulating FULL JOIN with UNION

Since FULL JOIN is not supported by all SQL databases, you can achieve similar results by using a combination of LEFT JOIN, RIGHT JOIN, and UNION.

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id
UNION
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;

Output

first_name	last_name	department_name
Ramesh	Kumar	HR
Sita	Patel	Finance
Arjun	Singh	HR
Priya	Sharma	Finance
Ravi	Verma	NULL
NULL	NULL	IT

This UNION of LEFT JOIN and RIGHT JOIN ensures that all records from both tables are included, even if there are no matches.

Filtering with FULL JOIN

You can also use additional filtering conditions with FULL JOIN.

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id
WHERE employees.salary > 55000 OR employees.salary IS NULL;

Output

first_name	last_name	department_name
Sita	Patel	Finance
Priya	Sharma	Finance
NULL	NULL	IT

In this example, only the employees with a salary greater than 55000 and departments without employees are included in the result set.

Multiple Table FULL JOIN

You can join more than two tables using FULL JOIN.

CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(100),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO projects (project_id, project_name, department_id)
VALUES
(1, 'Project A', 1),
(2, 'Project B', 2),
(3, 'Project C', 3);

SELECT employees.first_name, employees.last_name, departments.department_name, projects.project_name
FROM employees
FULL JOIN departments ON employees.department_id = departments.department_id
FULL JOIN projects ON departments.department_id = projects.department_id;

Output

first_name	last_name	department_name	project_name
Ramesh	Kumar	HR	Project A
Arjun	Singh	HR	Project A
Sita	Patel	Finance	Project B
Priya	Sharma	Finance	Project B
Ravi	Verma	NULL	NULL
NULL	NULL	IT	Project C

In this example, we join the employees, departments, and projects tables to retrieve employees along with their department names and project names.

Conclusion

The FULL JOIN is used for combining rows from two or more tables based on a related column, including all rows from both tables. This chapter covered the basic syntax for FULL JOIN, provided examples to illustrate its use, and demonstrated how to filter and join multiple tables. Understanding how to use FULL JOIN effectively will greatly enhance your ability to query and analyze data across multiple tables in a relational database. In the next chapter, we will explore CROSS JOIN with examples.