SQL FULL JOIN Keyword

Submitted by admin on Thursday, March 24, 2011 - 23:00.

The FULL JOIN keyword will return all rows from both tables that you specified. The same as other JOINS, FULL JOIN has the same syntax as show below.

SELECT column_name(s) FROM First_table_name FULL JOIN Second_table_name ON First_table_name.column_name = Second_table_name.column_name

Consider the following table for this exercise

Users

Firstname	Lastname	Salary	DeptID
John	Smith	1000	1
Mathew	Simon	3000	1
Bill	Steve	2200	1
Amanda	Rogers	1800	2
Steve	Hills	2800	2
Steve	jobs	2400	2
bill	cosby	700	3

Departments

SELECT Firstname, Lastname, Salary, DepartmentName FROM Users FULL JOIN Departments ON Users.DeptID=Departments.DeptID

Result of the Query

Firstname	Lastname	Salary	DepartmentName
John	Smith	1000	Employee
Mathew	Simon	3000	Employee
Bill	Steve	2200	Employee
Amanda	Rogers	1800	Management
Steve	Hills	2800	Management
Steve	jobs	2400	Management
bill	cosby	700
			HR

We have fully joined the two tables and all records from both tables are not returned.

Note: not all DBMS supports FULL JOIN Keyword.

Add new comment