Join (SQL)

A SQL Join clause is a way to retrieve information from two or more tables in a database.  When a database programmer wants to do a join command, they will type about the databases they want to get information from, and the type of join they want to use.  There are five types of joins.  A normal JOIN, which is also called an INNER JOIN, a LEFT OUTER JOIN,  aRIGHT OUTER JOIN,  aFULL OUTER JOIN and CROSS JOIN. 

In order for a join to work, there must be information in the tables to be joined that is the same between them. 

Join (Also called an Inner Join) 

Imagine two tables called Employee and Department.

+Employee table LastName  DepartmentID Rafferty 31 Jones  33 Heisenberg  33 Robinson  34 Smith 34 Williams  NULL

+Department table DepartmentID  DepartmentName 31  Sales 33  Engineering 34  Clerical 35  Marketing

Suppose a database administrator wanted to know what employee worked in what department.  While someone could just compare the ID numbers between the two tables, a way to have the information in one place is by doing a JOIN, also known as an INNER JOIN.  Because they have one type of data in common, the department ID, the tables can be joined together.

SELECT LastName, DepartmentName FROM employee join department on department.DepartmentID = employee.DepartmentID;

It would make a table that looks like this:

+Result table LastName  DepartmentNameRafferty	 SalesJones	 EngineeringHeisenberg	  EngineeringRobinson	  ClericalSmith	 Clerical

Because Williams in the employee table did not have an department ID, Williams was not printed in the final result.

 Outer Join 

Inner joins are fine if both tables have a matching record. However, if one table does not have a record for what the join is being built on, the query will fail.  But if a database programmer needs to grab information in an event that there is not a matching record for a row on one of the tables, they need to use an outer join.

 Left Outer Join (Also known as a Left Join) 

A left outer join (also known as a left join) will contain all records from the left table, even if the right table does not have a matching record for each row. 

So, using the two tables.

+Employee table LastName  DepartmentID Rafferty 31 Jones  33 Heisenberg  33 Robinson  34 Smith 34 Williams  NULL

+Department table DepartmentID  DepartmentName 31  Sales 33  Engineering 34  Clerical 35  Marketing

This command is run, which joins the two tables together on a left join.

SELECT *
FROM employee 
LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;

 Employee.LastName  Employee.DepartmentID  Department.DepartmentName  Department.DepartmentID Jones  33  Engineering  33 Rafferty  31  Sales  31 Robinson  34  Clerical  34 Smith  34  Clerical  34 Williams  NULL  NULL NULL Heisenberg  33  Engineering  33

Unlike the inner join, Williams is printed in the final result, even though Williams did not have a department ID.  Because Employee was the left table, or the table first mentioned in the SQL query, SQL returns all of the relevant data needed from it.  However, since there was not a department ID for Williams, the Department table was only able to return a null result.

 Right Outer Join (Also known as a Right Join) 

A right outer join works almost like a left outer join, except with how the tables are handled reversed.  This time, all of the relevant information will be returned from the right table, even if the left table does not have a matching result.  If the left table does not have a matching result, null will be in the place of the missing data.

+Employee table LastName  DepartmentID Rafferty 31 Jones  33 Heisenberg  33 Robinson  34 Smith 34 Williams  NULL

+Department table DepartmentID  DepartmentName 31  Sales 33  Engineering 34  Clerical 35  Marketing

This command is run, which joins the two tables together on a right join.

SELECT *
FROM employee RIGHT OUTER JOIN department
  ON employee.DepartmentID = department.DepartmentID;

And this is the final result.

 Employee.LastName  Employee.DepartmentID  Department.DepartmentName  Department.DepartmentID Smith  34  Clerical  34 Jones  33  Engineering  33 Robinson  34  Clerical  34 Heisenberg  33  Engineering  33 Rafferty  31  Sales  31 null  null  Marketing  35

 Flexibility of Joins 
Except for how the tables are treated, left outer joins, and right outer joints work alike.  This means, in the left outer join example, if a database programmer were to switch the order of the tables listed from this:

SELECT *
FROM employee 
LEFT OUTER JOIN '''department''' ON employee.DepartmentID = department.DepartmentID;

to this (notice how employee and department have switched places)

SELECT *
FROM department 
LEFT OUTER JOIN '''employee''' ON employee.DepartmentID = department.DepartmentID;

They would receive the same result as the right outer join example above:
 Employee.LastName  Employee.DepartmentID  Department.DepartmentName  Department.DepartmentID Smith  34  Clerical  34 Jones  33  Engineering  33 Robinson  34  Clerical  34 Heisenberg  33  Engineering  33 Rafferty  31  Sales  31 null  null  Marketing  35

Category:Query languages