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. Instead of discarding a row with no match, it fills NULL in the cells and returns all the rows (or tuples) from the left and right tables.
FULL OUTER JOIN
To understand FULL OUTER JOIN, you must be familiar with SQL, relational databases, and how to relate two or more tables. Before jumping on the main topic, we have explained how INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN works. If you’re familiar with these terms, we encourage you to read this article from the very beginning.
Now let us understand how Full Outer Join works in MS SQL Server:
The syntax for a SQL Server FULL OUTER JOIN is as follows:
SELECT table1.column1, table1.column2, table2.column3, table2.column4, …
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name;
Here,
- table1.column1, table1.column2, table2.column3, table2.column4, …: are the columns we want to retrieve from the tables.
- table1: The left table.
- table2: The right table.
- ON table1.column_name = table2.column_name: is the condition for joining the tables based on a common column.
The result of a FULL OUTER JOIN includes all rows from both tables, matched where possible and with NULLs in the columns where no match is found.
Note:
– To perform any type of join, we must have a common column in both tables. The column name can be different, but the data type should be the same
– For the sake of our understanding, we treat the first table as the left table and the second table as the right table that are being joined.
Now let us understand different types of joins:
As we talked about, joins in SQL are used to retrieve data from multiple tables based on a related column. Let’s have a quick look over INNER, LEFT, and RIGHT OUTER JOINS.
1. INNER JOIN
In a INNER JOIN operation, the SQL server returns only those rows that match the record from the left and the right table. If there is no match, it returns NULL.
Syntax:
SELECT * FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
In the above diagram, only two rows were returned, which have matching records in the right table. To learn more about INNER JOIN refer to this article.
2. LEFT OUTER JOIN
In a Left Outer Join, the SQL server returns all the rows from the left table (table 1) and the matching records from the right table (table 2) are included. If there is no match in the right table, it fills NULL values in the columns of the right table.
Syntax:
SELECT * FROM table1
LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name;
Instead of discarding the first row, the SQL server fills nulls in the column for the first row and returns all the rows of the left table. To learn more about SQL left join refer to this article.
3. RIGHT OUTER JOIN
In a Right Outer Join, the SQL server returns all the rows from the right table (table 2) and the matching records from the left table (table1) are included. If there is no match in the left table, it fills NULL values in the columns of the left table.
Syntax:
SELECT * FROM table1
RIGHT OUTER JOIN table2 ON table1.column_name = table2.column_name;
It also filled the NULL value for the third row and returned all rows from the right table with matching records from the left table. To learn more about RIGHT OUTER JOIN refer to this article.
4. FULL OUTER JOIN
FULL OUTER JOIN combines or performs union on the results of both LEFT OUTER JOIN and RIGHT OUTER JOIN.
Note: In the result of a FULL OUTER JOIN operation, the number of rows/tuples will be equal to the unique entry in the common column (or related column).
Implementation of FULL OUTER JOIN in MS SQL Server.
Let’s consider two tables: Employees (Employee_id, Employee_name, Departement_Id) and Departments (Department_id, Department_name). The Employees table contains information about employees, while the Departments table contains information about different departments in a company.
We have inserted the following data in the Employee Table:
INSERT INTO EMPLOYEE (EMPLOYEE_ID, EMPLOYEE_NAME, DEPARTMENT_ID)
VALUES
(1, 'Priya', 2),
(2, 'Arun', 3),
(3, 'Aditya', 3),
(4, 'Rahul', 4),
(5, 'Akshay', 6);
Let’s see the entries:
SELECT * FROM EMPLOYEE;
And following data in the Department Table:
INSERT INTO DEPARTMENT (DEPARTMENT_ID, DEPARTMENT_NAME)
VALUES
(2, 'Marketing'),
(3, 'IT Services'),
(4, 'Management'),
(5, 'HR');
Checking the entries:
SELECT * FROM DEPARTMENT;
Note: Here, we are not considering any referential integrity as we have not set the foreign key. The department_id column in Employee table can have an id that don’t exist in the Department Table.
Full Outer Join is generally performed on common columns other than the foreign key.
Simple FULL OUTER JOIN:
SELECT Employee.Employee_ID, Employee.Employee_Name, Department.Department_ID, Department.Department_Name
FROM Employee
FULL OUTER JOIN Department
ON Employee.Department_ID = Department.Department_ID;
Output: When we run the above query, we get the following results.
In this example, we retrieve the Employee_ID and Employee_Name from the Employee table and the Department_Name and Department_ID from the Department table.
The result includes all employees, along with their respective departments, whether they are assigned to a department or not. As we can see Akshay do not belong to any department and the HR department has no employee.
Note: Here, the OUTER keyword is optional. We can simply use FULL JOIN instead of FULL OUTER JOIN. Both give us the same output.
Removing Outer Keyword:
SELECT *
FROM Employee
FULL JOIN Department
ON Employee.Department_ID = Department.Department_ID;
Result:
We get the same result.
Handling NULLs:
If we don’t want to show NULL values in the result, we can handle it explicitly using the COALESCE() function. Here is how we can handle nulls:
SELECT COALESCE(Employee.Employee_Name, 'No Employee') AS Employee_Name,
COALESCE(Department.Department_Name, 'No Department') AS Department_Name
FROM Employee
FULL OUTER JOIN Department
ON Employee.Department_ID = Department.Department_ID;
Output: When we run the above query, we get the following results/rows.
In this example, we use the COALESCE function to handle or replace NULL values. If an employee is not assigned to a department or a department has no employees, the result displays “No Employee” or “No Department,” respectively.
Conclusion
In this article, we look over INNER, LEFT, and RIGHT OUTER JOINs to build an intuition on how joins work. Later, we discussed FULL OUTER JOIN in depth with an example. We also learned how to handle nulls. Understanding FULL OUTER JOIN enhances your ability to write sophisticated SQL queries for diverse data scenarios. If you face any difficulty, you can pin down the same in the comment section.