Full Outer Join in SQL: A Comprehensive Guide

Full Outer Join in SQL

Full Outer Join in SQL! What exactly is it..? Just assume you have two different tables and you wish to merge them in a way that shows all of the data from both tables, even though if there is no matching information between them. This is where the Full Outer Join comes in! It returns everything from both tables, even NULL where there is no match. It's similar to merging two lists: you keep everything, even if some things don't line up properly between them. It's quite useful when you want a full view of both tables!

In this SQL Server Tutorial, we will examine the full outer join in SQL in detail, explaining how it works and when to use it, what is full outer join in SQL exactly. We will also see some full outer join in SQL examples to solidify our understanding.

What is a Full Outer Join?

A Full Outer Join in SQL combines the results of an LEFT JOIN with a RIGHT JOIN. Basically, It retrieves all rows from both tables, even if one of them does not contain a matching entry. If there is no match found, the result will have NULL values in columns where data from the other table would typically appear. This join is useful when you want to retrieve all records from two tables, even if some of the data isn't matching.

Syntax of Full Outer Join

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;

How Does Full Outer Join Work?

When a full outer join is executed :

• It returns all rows from the left table (table1), with NULLs in columns from the right table (table2) when there is no match.
• It returns all rows from the right table (table2), with NULLs in columns from the left table (table1) when there is no match.
• It returns only matched rows from both tables where a match is found.

This ensures that even if a record in one of the tables has no corresponding record in the other table, that record is still included in the result, with NULL values for the missing columns.

Example of Full Outer Join

Let’s elaborate on this with real-time examples.

Full outer join in SQL example: Suppose you have two tables: Students and Courses.

Students Table:

Courses Table:

Now, we’ll perform a full outer join on these tables using the StudentID column:

SELECT Students.StudentID, Students.Name, Courses.CourseName
FROM Students
FULL OUTER JOIN Courses
ON Students.StudentID = Courses.StudentID;

Result:

Explanation:

• The first two rows show the students Pradnya and Aman who are matched with their respective courses.
• Shailesh has no course listed in the Courses table, so CourseName is NULL.
• The last row shows a course (Chemistry) with no corresponding student, so StudentID and Name are NULL.

When to Use a Full Outer Join

Full outer joins are particularly useful in the following scenarios:

• When you need a complete dataset from both tables, regardless of whether there’s a match. This is especially common when analyzing data from multiple sources that might not always have a perfect match.
• Reporting purposes: When creating comprehensive reports, full outer joins ensure that all possible data points are included, even if some data is missing in one of the tables.
• Handling missing data: In situations where data is incomplete, full outer joins allow you to view all records and easily spot where values are missing by checking for NULLs.

Full Outer Join vs Other Joins

To understand the uniqueness of a full outer join, let’s compare it to other types of joins:

1. Inner Join:

The inner join returns only the matching records from both tables. If there’s no match, the row is excluded.

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

2. Left Join (Left Outer Join):

Left Outer Join returns all records from the left table and matching records from the right table. Non-matching records from the right table will be NULL.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

3. Right Join (Right Outer Join):

Right Outer Join returns all records from the right table and matching records from the left table. Non-matching records from the left table will be NULL.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

4. Full Outer Join:

Combines the results of both left and right joins, ensuring that all records from both tables are returned, with NULL values for any non-matching records.

Implementing FULL OUTER JOIN With WHERE Clause and Alias

Demo Database

Table: students

Table: courses

Sample Data

students Table

INSERT INTO students (id, name, age) VALUES
(1, 'Pradnya', 20),
(2, 'Shailesh', 17),
(3, 'Aman', 22),
(4, 'Sourav', 19);
    

courses Table

INSERT INTO courses (course_id, student_id, course) VALUES
(101, 1, 'Mathematics'),
(102, 1, 'Physics'),
(103, 3, 'Chemistry'),
(104, 5, 'Biology');  -- Notice student_id 5 does not exist in students table
    

1. FULL OUTER JOIN with WHERE Clause

SELECT 
    s.id AS student_id,
    s.name AS student_name,
    c.course AS course_name
FROM 
    students AS s
FULL OUTER JOIN 
    courses AS c ON s.id = c.student_id
WHERE 
    s.age > 18;  -- Filtering condition
    

2. FULL OUTER JOIN with AS Alias

SELECT 
    s.id AS student_id,
    s.name AS student_name,
    c.course AS course_name
FROM 
    students AS s
FULL OUTER JOIN 
    courses AS c ON s.id = c.student_id;
    

Expected Results

For the FULL OUTER JOIN with WHERE Clause:

For the FULL OUTER JOIN with AS Alias:

Performance Considerations

Full outer joins are useful, but they can be resource-intensive, especially with large datasets. A full outer join retrieves all data from both tables, including those that do not match, hence the result set might be substantially bigger than those of other join types, resulting in slower performance in some circumstances.

Conclusion

In conclusion, We have explored what is full outer join in SQL, and full outer join in SQL examples And got to know that the Full Outer Join in SQL is a useful technique for combining all data from two tables, regardless of whether they have matching rows. It ensures that you get a complete dataset by combining unmatched rows from both tables and replacing missing data with NULL. This makes it particularly useful for creating thorough reports or analyzing data from many sources that may contain gaps. If you want to get an idea of other SQL concepts just go through our SQL Server Course.

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE