The FULL OUTER JOIN or FULL JOIN is a useful technique in MySQL that allows us to fetch all rows from both tables when there is a match in the records.
In this article, we will learn about the FULL OUTER JOIN through practical examples and MySQL queries.
MySQL FULL OUTER JOIN
The FULL OUTER JOIN in MySQL returns all rows of both tables involved in the JOIN operation in the result set. If there is no match for a particular row based on the specified condition, the result will include NULL values for columns from the table that do not have a match.
This makes FULL OUTER JOIN useful when we want to include unmatched rows from both tables.
Note: MySQL does not explicitly support a FULL OUTER JOIN. Instead, we can achieve it by combining a LEFT JOIN, a RIGHT JOIN, and a UNION operator. You can use FULL OUTER JOIN in SQL using FULL OUTER JOIN keyword.
Syntax
SELECT *FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name
UNION
SELECT *
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
- The LEFT JOIN fetches all rows from the left table and the matched rows from the right table.
- The RIGHT JOIN fetches all rows from the right table and the matched rows from the left table.
- The UNION operator combines the results of the two SELECT statements.
FULL OUTER JOIN Examples
Let's look at some practical examples for a better understanding of FULL OUTER JOIN.
First, we will create 2 tables called students and courses on which we will perform the FULL OUTER JOIN.
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(255),
course_id INT
);
INSERT INTO students (student_id, student_name, course_id)
VALUES
(1, 'Alice', 101),
(2, 'Bob', 102),
(3, 'Charlie', NULL),
(4, 'David', 103);
CREATE TABLE courses (
course_id INT AUTO_INCREMENT PRIMARY KEY,
course_name VARCHAR(50) NOT NULL
);
INSERT INTO courses (course_id, course_name)
VALUES
(101, 'Mathematics'),
(102, 'Physics'),
(103, 'Chemistry'),
(104, 'Computer Science');
Output:
The students table will look like this:
Students Table
The courses table will look like this:
Courses Table
Example 1: Enrolled Students and Courses
Consider the situation where we want to fetch a list of all students and their respective courses, including those without assigned courses.
Query:
SELECT students.student_id, students.student_name, COALESCE(courses.course_name, 'No Course')
AS course_name
FROM students
LEFT JOIN courses ON students.course_id = courses.course_id
UNION
SELECT students.student_id, students.student_name, courses.course_name
FROM students
RIGHT JOIN courses ON students.course_id = courses.course_id;
Output:
Output
Explanation: In this result, we see all students and their courses. Students without courses and courses without students are also included.
Example 2: Enrolled Courses and Students
Suppose We want to see all courses and their enrolled students.
Query:
SELECT courses.course_id, courses.course_name, COALESCE(GROUP_CONCAT(students.student_name),
'No Students') AS enrolled_students
FROM courses
LEFT JOIN students ON courses.course_id = students.course_id
GROUP BY courses.course_id, courses.course_name
UNION
SELECT courses.course_id, courses.course_name, GROUP_CONCAT(students.student_name) AS enrolled_students
FROM courses
RIGHT JOIN students ON courses.course_id = students.course_id
GROUP BY courses.course_id, courses.course_name;
Output:
Output
Explanation: This result provides a list of all courses and the students enrolled in each course. Courses without students and students not enrolled in any course are also included.
Example 3: Sorting Students and Courses Alphabetically
Let's reconsider Example 1 to include an ORDER BY clause and sort the results alphabetically by student name and then by course name.
Query:
SELECT students.student_id, students.student_name, COALESCE(courses.course_name, 'No Course')
AS course_name
FROM students
LEFT JOIN courses ON students.course_id = courses.course_id
UNION
SELECT students.student_id, students.student_name, courses.course_name
FROM students
RIGHT JOIN courses ON students.course_id = courses.course_id
ORDER BY student_name, course_name;
Output:
Output
Explanation: In this result, the rows are sorted alphabetically by student name and then by course name.
Conclusion
MySQL FULL OUTER JOIN returns data from left and right tables that satisfy the given condition. In MySQL, you can not directly use the FULL OUTER JOIN or FULL JOIN, so we use a combination of LEFT JOIN or RIGHT JOIN and UNION operator.
This tutorial explained how to use these combinations to perform FULL JOIN in MySQL. With the practical example, you can practice FULL OUTER JOIN queries in MySQL and combine records to find insights.