A FULL OUTER JOIN in SQL is a type of join that combines rows from two or more tables based on a specified condition, and includes all rows from both the left (or first) and right (or second) tables. If there is no match, NULL values are filled in for columns from the table where no match is found.

My SQL Doesn’t support Full Outer Join directly. So, we need to perform the left join and right join on two tables and then union their result.

Syntax

Follow the syntax below to perform the full outer join using the left join, right join and union.

SELECT *
FROM table1 t1
LEFT JOIN table2 t2 ON t1.column_name = t2.column_name
 
UNION
 
SELECT *
FROM table1 t1
RIGHT JOIN table2 t2 ON t1.column_name = t2.column_name;

Example

Consider two tables, students and grades:

Image scaled to 85%

Now, let’s perform a FULL OUTER JOIN to retrieve information about all students and their grades:

Database Exercise

Database Schema:

-- Database schema would be rendered here

Exercise Script:

-- Exercise script would be rendered here

Available actions: Execute

Result

Image scaled to 45%

In this example, the FULL OUTER JOIN ensures that all students from the students table and all grades from the grades table are included in the result set. The rows that have no corresponding match in the other table display NULL values for columns from that table.