SQL FULL JOIN Keyword

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.

SQL FULL JOIN Syntax

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

DeptID DepartmentName
1 Employee
2 Management
4 HR

Example # 1

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