How to Use Full Outer Join in MySQL
Last Updated : 05 Jun, 2024
The FULL OUTER JOIN or FULL JOIN in MySQL is a powerful technique to fetch all rows from both tables, including matched and unmatched records. In this article, we will explore how to use FULL OUTER JOIN with practical examples and MySQL queries. Although MySQL doesn't natively support FULL OUTER JOIN, we can achieve the same result using a combination of LEFT JOIN, RIGHT JOIN, and the UNION operator.
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 including 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 the 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 OUTFULL OUTER JOINER JOIN. First, we will create two tables called students and courses on which we will perform the FULL OUTER JOIN.
Creating the Tables:
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 TableThe courses table will look like this:
Courses TableExample 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:
OutputExplanation: 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:
OutputExplanation: 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:
OutputExplanation: 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.
Similar Reads
FULL OUTER JOIN in SQLite
In the area of data querying and manipulation, the ability to combine information from different sources is important. SQLite, a popular embedded database engine, offers a range of join operations to fast process. One such operation FULL OUTER JOIN is particularly powerful as it allows us to merge d
4 min read
MySQL Outer Join
MySQL is a popular and strong system that is extensively employed for handling data in the field of relational databases. A crucial aspect of working with databases is the ability to retrieve and handle data from different tables. SQL joins combine rows from different tables by connecting them based
4 min read
How to Join First Row in SQL?
SQL (Structured Query Language) is a powerful tool used to manage and manipulate data in relational databases. A common requirement when dealing with data relationships is to fetch only the first row from a table based on specific conditions or criteria. This can be particularly useful when joining
6 min read
How UPDATE JOIN Works in PostgreSQL?
In PostgreSQL, updating records in one table based on values in another is a common scenario in relational databases. While PostgreSQL does not have a direct UPDATE JOIN syntax like other databases (e.g., MySQL). Hence, it provides a powerful alternative by using the FROM clause in the UPDATE statem
6 min read
PostgreSQL - FULL OUTER JOIN
In PostgreSQL, the FULL OUTER JOIN is a powerful feature that combines the effects of both LEFT JOIN and RIGHT JOIN. This join operation retrieves all rows from both tables involved in the join, including unmatched rows from each table. For any unmatched rows, PostgreSQL fills the result with NULL v
4 min read
How to Concat Two Columns Into One in MySQL?
Concatenating columns in MySQL is a key operation for combining data from multiple fields into a single, unified column. This process is essential for generating comprehensive reports and merging data elements like names or addresses. This article explores two effective methods for column concatenat
3 min read
MySQL ANY and ALL Operators
When working with databases, there are often scenarios where we need to compare a value against multiple other values. MySQL offers two powerful operators for this purpose such as ANY and ALL Operators. These operators allow for more complex and flexible data retrieval, enabling comparisons between
5 min read
When should we use CROSS APPLY over INNER JOIN in MySQL
CROSS APPLY and INNER JOIN are used in MySQL to combine data from multiple tables. When dealing with row-wise operations or dynamic subqueries, CROSS APPLY is more efficient than INNER JOIN. CROSS APPLY allows for more detailed control on individual rows, while INNER JOIN operates on sets of data. I
4 min read
How to Use the IN Operator with a SubQuery in SQL?
In this article, we will see the use of IN Operator with a SubQuery in SQL. IN operator is used to compare the column values with the list of values. The list of values is defined after IN query in SQL. If we don't know the exact list of values to be compared, we can generate the list of values usin
2 min read
SQL Server FULL OUTER JOIN
Joins in SQL are used to retrieve data from multiple tables based on a related column (or common column) between them. In this article, we will learn how to use FULL OUTER JOIN, which returns all rows from both tables being joined. It combines the results of both LEFT OUTER JOIN and RIGHT OUTER JOIN
6 min read