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:

</>
Copy
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:

</>
Copy
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:

</>
Copy
CREATE DATABASE school;

2. Select the school database:

</>
Copy
USE school;

3. Create a students table:

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

4. Create a grades table:

</>
Copy
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:

</>
Copy
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');