SQL Server: Full Join (or) Full Outer Join
In this article i will explain what is Full Join (or) Full Outer Join in SQL Server.
In previous articles i explained what are joins, what is INNER JOIN in SQL Server, what is LEFT JOIN (or) LEFT OUTER JOIN in SQL Server, and what is RIGHT JOIN (or) RIGHT OUTER JOIN in SQL Server. Now i will explain what is Full Join (or) Full Outer Join in SQL Server.
Inner joins return rows only when there is at least one row from both tables that matches the join condition. Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions. All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join. All rows from both tables are returned in a full outer join.
SQL Server uses the following ISO keywords for outer joins specified in a FROM clause:
- LEFT OUTER JOIN (or) LEFT JOIN
- RIGHT OUTER JOIN (or) RIGHT JOIN
- FULL OUTER JOIN (or) FULL JOIN
FULL OUTER JOIN (or) FULL JOIN:
Step 1: Create Employee, Department tables.
Step 2: Insert data into Employee, Department tables.
Get Employee data:
select * from Employee
Get Department data:
select * from Department
Select * From Table1 full join Table2 on Table1.Col1 = Table2.Col1
Here you will get all rows from Table1 and Table2. Here *(star) means you will get all columns from both the tables.
Select * from Employee E full join Department D on E.DepartmentID = D.DepartmentID
Here you will get all rows from Employee table and Department table.
Note: You will see NULL in query ouput, because there are no rows with DepartmentName 'Java' and 'C++'. Because no employee exists with department name 'Java' and 'C++'.
CreatedNov 03, 2013
UpdatedNov 03, 2013