Did you find this useful?
Socialize it today.


SQL FULL Join

Tuesday, December 06, 2011

tags SQL

The SQL FULL JOIN keyword returns rows when there is a match in either of the tables.

Departments Table

deptNameemployeeID
Sales1000
HR1002
Accounting1003
Operations1004


Employees Table

employeeIDemployeeName
1000John Smith
1001Fred White
1002Jane Scott
1003Samuel Williams

In this example, employeeID is a primary key in the table called "Employees". You'll notice that employeeID also exists in the "Departments" table, but as a foreign key. Using an SQL Join, we can combine these two tables into one when presenting the results of a query.


Design Example

In this example, using a RIGHT JOIN, we can build a query to display the departments and employees even though not every employee is assigned to a department, such as in the case of 'Fred White'.  The two tables are joined by the 'employeeID' field. We can use the RIGHT JOIN to bind this data together.


SQL Full Join

Syntax

SELECT tableName#.columnName#, tableName#.columnName#, etc...
FROM tableName1
FULL JOIN tableName2
ON tableName1.columnName# = tableName2.columnName#


Example

List the departments and their assigned employees.

SELECT Departments.deptName, Employees.employeeName
FROM Departments
FULL JOIN Employees
ON Departments.employeeID = Employees.employeeID


Results

deptNameemployeeName
SalesJohn Smith
HRJane Scott
AccountingSamuel Williams
Operations 
 Fred White


You should immediately notice that 'Fred White' from the Employees table, and 'Operations' from the Departments table are listed. This is because we used a Full Join and any records matching in either table will be included in the results.

Please help us spread the word by socializing it today!

email contact us

Did you find something wrong with the information on this page? Please take a moment to report it to us so that we can continue to improve the quality of the information on this site. Click here to report an issue with this page.



Recommended Books & Training Resources

SQL Cookbook Head First SQL: Your Brain on SQL A Learners Guide