SQL FULL JOIN – Syntax and Examples

SQL FULL JOIN

The SQL FULL JOIN clause (or FULL OUTER JOIN) returns all records when there is a match in either the left or right table. If there is no match, NULL values are returned for columns from the table that lacks a match.

A FULL JOIN combines the effects of both LEFT JOIN and RIGHT JOIN, showing all rows from both tables and matching them where possible.

In this tutorial, we will go through SQL FULL JOIN, its syntax, and how to use this join in SQL statements, with the help of well detailed examples.

---

Syntax of SQL FULL JOIN

The basic syntax of the SQL FULL JOIN clause is:

SELECT table1.column1, table2.column2, ...
FROM table1
FULL JOIN table2
ON table1.common_column = table2.common_column;

When using MySQL, you can achieve a similar result with:

SELECT table1.column1, table2.column2, ...
FROM table1
LEFT JOIN table2 ON table1.common_column = table2.common_column
UNION
SELECT table1.column1, table2.column2, ...
FROM table1
RIGHT JOIN table2 ON table1.common_column = table2.common_column;

Each part of this syntax has a specific purpose:

• SELECT: Specifies the columns to retrieve from each table.
• FROM: Specifies the left and right tables for the join operation.
• FULL JOIN: Combines all rows from both tables and matches them based on the join condition.
• ON: Specifies the condition for matching rows, typically a common column.

Step-by-Step Examples with MySQL

Note: MySQL does not natively support FULL JOIN. To achieve similar results in MySQL, you can use a UNION of LEFT JOIN and RIGHT JOIN operations.

We’ll go through examples demonstrating the equivalent of FULL JOIN in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use sample students and grades tables with fields student_id, name, age, and subject.

Setup for Examples: Creating the Database and Tables

1. Open MySQL Workbench and create a new database:

CREATE DATABASE school;

2. Select the school database:

USE school;

3. Create a students table:

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT
);

4. Create a grades table:

CREATE TABLE grades (
    student_id INT,
    subject VARCHAR(50),
    grade CHAR(1),
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

5. Insert sample data into the students and grades tables:

INSERT INTO students (name, age)
VALUES
('Alice', 14),
('Bob', 15),
('Charlie', 14),
('David', 16);

INSERT INTO grades (student_id, subject, grade)
VALUES
(1, 'Math', 'A'),
(2, 'Science', 'B'),
(3, 'Math', 'A'),
(4, 'History', 'C');

---

Examples: Using FULL JOIN Equivalent in MySQL

Now, let’s explore different scenarios of using a FULL JOIN equivalent in MySQL with these tables.

---

1. Retrieving All Students and All Grades

To retrieve a list of all students and their grades, even if a student or grade doesn’t have a corresponding record, use a UNION of LEFT JOIN and RIGHT JOIN:

SELECT students.name, grades.subject, grades.grade
FROM students
LEFT JOIN grades ON students.student_id = grades.student_id
UNION
SELECT students.name, grades.subject, grades.grade
FROM students
RIGHT JOIN grades ON students.student_id = grades.student_id;

This query returns all students and all grades. If there’s no match between a student and grade, NULL will be displayed for the missing values.

---

2. Filtering FULL JOIN Results with WHERE Clause

To find records where either a student or grade is missing, filter the result for NULL values in name or subject:

SELECT students.name, grades.subject, grades.grade
FROM students
LEFT JOIN grades ON students.student_id = grades.student_id
UNION
SELECT students.name, grades.subject, grades.grade
FROM students
RIGHT JOIN grades ON students.student_id = grades.student_id
WHERE students.name IS NULL OR grades.subject IS NULL;

This query returns only records where a student or grade is missing from one of the tables, showing NULL for unmatched values.

Reference: SQL WHERE Clause

---

FAQs for SQL FULL JOIN

1. What does the SQL FULL JOIN do?

The FULL JOIN returns all rows where there is a match in either the left or right table, displaying NULL where no match exists in one of the tables.

2. Does MySQL support FULL JOIN?

No, MySQL does not natively support FULL JOIN. You can simulate it using a UNION of LEFT JOIN and RIGHT JOIN.

3. How is FULL JOIN different from LEFT JOIN?

FULL JOIN includes all rows from both tables, while LEFT JOIN only includes all rows from the left table, with matches from the right table where available.

4. Can FULL JOIN be used with multiple tables?

Yes, but each FULL JOIN would require additional UNION operations if working in MySQL.

5. How do I filter results in a FULL JOIN?

You can use the WHERE clause to filter results in a FULL JOIN, for example, to show only records with NULL in one table by checking for NULL values.