The SQL Server FULL JOIN type combines two or more tables and returns all the records (or rows) present in both the left and right tables. So, the FULL JOIN is a combination of LEFT and RIGHT OUTER JOIN. All the unmatched rows from both tables are filled with NULL Values.
The SQL Server FULL JOIN can also be called a FULL OUTER JOIN. So it is optional to use the OUTER Keyword. It uses a common column to establish the connection between the tables.
Let us see the Venn diagram or visual representation of the SQL FULL OUTER JOIN for better understanding. From the image below, you can easily understand that the FULL JOIN displays all the records present in Table 1 and Table 2.
SQL FULL JOIN Syntax
The syntax of the FULL OUTER JOIN to combine two or more tables completely is as shown below:
SELECT Table1.Column(s), Table2.Column(s),
FROM Table1
FULL OUTER JOIN Table2
ON Table1.Common_Col = Table2.Common_Col
--OR We can simply write it as
SELECT Table1. Column(s), Table2. Column(s),
FROM Table1
FULL JOIN Table2
ON Table1.Common_Col = Table2.Common_Col
From the above syntax, you can notice that we have to select the required columns from both tables. The FULL JOIN will establish a connection between Table1 and Table2 based on the condition specified in the ON clause. Here, Common_Col is to establish a connection.
For this SQL Server FULL JOIN demonstration, we create a simple three tables: employees, projects, and budget. To make it even simpler, we insert only a few records in each table. The SQL Server query to CREATE and INSERT data is shown below.
-- Create projects Table
CREATE TABLE projects (
ProjectID INT PRIMARY KEY,
ProjectName VARCHAR(100),
StartDate DATE
);
-- Insert projects
INSERT INTO projects (ProjectID, ProjectName, StartDate) VALUES
(1, 'Website Design', '2023-01-01'),
(2, 'E-Commerce Integration', '2024-04-01'),
(3, 'AI Integration', '2024-12-01'),
(4, 'App Development', '2025-05-10');
-- Create Employees Table
CREATE TABLE employees (
ID INT PRIMARY KEY,
Name VARCHAR(100),
ProjectID INT
);
-- Insert employees
INSERT INTO employees (ID, Name, ProjectID) VALUES
(101, 'Ram', 1),
(102, 'John', 1),
(103, 'Charlie', 2),
(104, 'Veer', NULL);
-- Create Budgets Table
CREATE TABLE budgets (
ProjectID INT PRIMARY KEY,
Budget DECIMAL(10, 2)
);
-- Insert budgets
INSERT INTO budgets (ProjectID, Budget) VALUES
(1, 150000.00),
(2, 100000.00);
SQL FULL OUTER JOIN Example
Suppose there are two tables with products and orders, and the task is to retrieve the list of all products and their corresponding order details, including those without any orders (NULL) and those with no product information (NULL). In such a case, use the FULL JOIN to combine those two tables and print the possible combinations.
The following FULL OUTER JOIN example uses the SELECT * FROM to display all the columns and rows in the projects and budgets tables.
SELECT * FROM projects
FULL OUTER JOIN budgets
ON projects.ProjectID = budgets.ProjectID
Query result explanation:
- If you observe the result set below, it displays all the columns from both tables.
- As there are four rows in the projects table and two rows in the budgets table, and they are linked to each other, it displays four records. However, there is no budget allocated to the AI Integration and APP Development under the budget table; those two records are filled with NULL values.
- If there is any extra row in the budgets table that has no reference in the projects will also be added to the result set.
ProjectID ProjectName StartDate ProjectID Budget
1 Website Design 2023-01-01 1 150000.00
2 E-Commerce Integration 2024-04-01 2 100000.00
3 AI Integration 2024-12-01 NULL NULL
4 App Development 2025-05-10 NULL NULLSQL FULL JOIN without the OUTER keyword
As we earlier said, using an OUTER keyword in this JOIN type is optional. Let me remove the OUTER keyword from the above FULL JOIN example to show the output.
SELECT * FROM projects
FULL JOIN budgets
ON projects.ProjectID = budgets.ProjectID
If you observe the output, it returns the same result as the above example. So, when working with a FULL JOIN, don’t worry about using the OUTER keyword.
Select required Columns
Using SELECT * FROM may be easy to write, but it displays all the columns, which may not be needed for the analysis. For instance, in the above result set, the [ProjectID] column is repeated twice, which annoys the user. To avoid this simple SQL Server FULL JOIN mistake, please select individual column names by placing the required fields after the SELECT Statement to avoid unwanted columns. In this example, we use the employees and projects table.
SELECT ID, Name AS EmployeeName, ProjectName, StartDate
FROM projects
FULL OUTER JOIN employees
ON projects.ProjectID = employees.ProjectID
If you look at the result set, it displays a total of six records.
- The result set returns all the rows from both tables and adds NULL values if there are no matching records
- There are four employees in the second table and four projects in the first table.
- Within them, employees ID 101, 102, and 103 are linked to projects 1 and 2.
- There are no employees linked to the AI Integration and App Development projects. So, it assigns NULL values for employee ID and Name.
- Similarly, employee ID 104 is not assigned to any project. So, the project name and the start date values become NULL for ID 104.
ID EmployeeName ProjectName StartDate
101 Ram Website Design 2023-01-01
102 John Website Design 2023-01-01
103 Charlie E-Commerce Integration 2024-04-01
NULL NULL AI Integration 2024-12-01
NULL NULL App Development 2025-05-10
104 Veer NULL NULLSQL FULL OUTER JOIN With Table ALIAS Names
The above FULL JOIN query will work perfectly as long as the column names from both projects and the employees tables are different. What happens if we have the same Column names in both tables? Well, you will end up in a mess. Let us see how to resolve the issue. Before we get into the solution, let me show you one practical example.
As you can see, we are using the above SQL FULL JOIN query, but this time, we added the ProjectID as an additional column.
SELECT ID, Name AS EmployeeName,
ProjectID, ProjectName, StartDate
FROM projects
FULL OUTER JOIN employees
ON projects.ProjectID = employees.ProjectID
As you can see in the message below, the FULL JOIN query throws an error: Ambiguous column name ‘ProjectID’. It is because the ProjectID is present in both the projects and employees tables. And SQL Server doesn’t know which one you want it to retrieve.
Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'ProjectID'.To resolve this RIGHT JOIN common error, you must use the table name (or ALIAS names) before the column name. For example, the following code uses the ALIAS table name before the column names. By this approach, we can inform the Server that we are looking for a ProjectID belonging to the projects table.
We can write the above query as:
SELECT e.ID, e.Name AS EmployeeName,
p.ProjectID, p.ProjectName, p.StartDate
FROM projects p
FULL OUTER JOIN employees e
ON p.ProjectID = e.ProjectID
SQL FULL JOIN with ORDER BY Clause
When working with a FULL JOIN to combine two or more tables, the result set does not follow any specific order. However, the RIGHT JOIN allows us to use the ORDER BY Clause to sort or rearrange the order of the records. For instance, the query below sorts employees and their project information using the project name in ascending order.
SELECT e.Name AS EmployeeName, p.ProjectName, p.StartDate
FROM projects p
FULL JOIN employees e ON p.ProjectID = e.ProjectID
ORDER BY p.ProjectName;
EmployeeName ProjectName StartDate
Veer NULL NULL
NULL AI Integration 2024-12-01
NULL App Development 2025-05-10
Charlie E-Commerce Integration 2024-04-01
Ram Website Design 2023-01-01
John Website Design 2023-01-01SQL FULL JOIN on Multiple Tables
In all the above-mentioned examples, we used the FULL OUTER JOIN to combine two tables, but you can combine multiple tables to get the result set. For instance, to find project information, allocated budget details, and employees associated with each project. In such a case, use the FULL JOIN with three tables using the common column.
SELECT p.ProjectID, e.Name AS EmployeeName,
p.ProjectName, p.StartDate, b.Budget
FROM projects p
FULL JOIN employees e ON p.ProjectID = e.ProjectID
FULL JOIN budgets b ON p.ProjectID = b.ProjectID
ORDER BY p.StartDate;
SQL FULL JOIN with WHERE Clause
The FULL OUTER JOIN also allows us to use the WHERE clause to restrict the number of rows returned from two tables. It helps to display the required records and restrict the unwanted rows. Therefore, we will use the WHERE Clause and the FULL JOIN in this example.
The query below uses the IS NOT NULL operator and returns only the employees and their project details that are not NULL. It returns the same result as the INNER JOIN (matching records from both tables).
SELECT e.Name AS EmployeeName, p.ProjectName, p.StartDate
FROM projects p
FULL JOIN employees e ON p.ProjectID = e.ProjectID
WHERE p.ProjectName IS NOT NULL AND e.Name IS NOT NULL
ORDER BY p.ProjectName
EmployeeName ProjectName StartDate
Charlie E-Commerce Integration 2024-04-01
Ram Website Design 2023-01-01
John Website Design 2023-01-01Similar to the above, use the WHERE clause when the FULL JOIN is applied on multiple tables, as shown below. It displays project information where StartDate is greater than 1st May 2024.
SELECT p.ProjectID, e.Name AS EmployeeName,
p.ProjectName, p.StartDate, b.Budget
FROM projects p
FULL JOIN employees e ON p.ProjectID = e.ProjectID
FULL JOIN budgets b ON p.ProjectID = b.ProjectID
WHERE p.StartDate > '2024-05-01'
ORDER BY p.StartDate;
ProjectID EmployeeName ProjectName StartDate Budget
3 NULL AI Integration 2024-12-01 NULL
4 NULL App Development 2025-05-10 NULLSQL FULL JOIN with GROUP BY
In all the above examples, we display the individual information of each column in row based. However, if you group the common columns and calculate the total and average for more high-level information. To do so, please use the FULL JOIN and GROUP BY clause to combine the common columns and apply aggregate functions.
The query below counts the total number of employees assigned to each project and the average budget allocated to each employee. For this, we used the COUNT and AVG functions.
SELECT p.ProjectName,
COUNT(e.ID) AS TotalEmployees,
AVG(b.budget) AS AverageBudget
FROM projects p
FULL JOIN employees e ON p.ProjectID = e.ProjectID
FULL JOIN budgets b ON p.ProjectID = b.ProjectID
GROUP BY p.ProjectName
Handling NULL Values
When working with the SQL FULL OUTER JOIN, it is natural to encounter NULL values for the unmatched information. To deal with NULL values in a FULL JOIN, one option is to use the COALESCE function to replace the NULL with 0 or any default value.
SELECT p.ProjectName,
COUNT(e.ID) AS TotalEmployees,
AVG(COALESCE(b.budget, 0)) AS AverageBudget
FROM projects p
FULL JOIN employees e ON p.ProjectID = e.ProjectID
FULL JOIN budgets b ON p.ProjectID = b.ProjectID
GROUP BY p.ProjectName
ProjectName TotalEmployees AverageBudget
NULL 1 0.000000
AI Integration 0 0.000000
App Development 0 0.000000
E-Commerce Integration 1 100000.000000
Website Design 2 150000.000000The second option is to use the WHERE or HAVING clause to filter the NULL values from the FULL JOIN result set.
SELECT p.ProjectName,
COUNT(e.ID) AS TotalEmployees,
AVG(b.budget) AS AverageBudget
FROM projects p
FULL JOIN employees e ON p.ProjectID = e.ProjectID
FULL JOIN budgets b ON p.ProjectID = b.ProjectID
GROUP BY p.ProjectName
HAVING AVG(b.budget) IS NOT NULL
ProjectName TotalEmployees AverageBudget
E-Commerce Integration 1 100000.000000
Website Design 2 150000.000000