Join (SQL)

From Wikipedia, the free encyclopedia
Jump to navigation Jump to search

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)[change | change source]

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 DepartmentName
Rafferty Sales
Jones Engineering
Heisenberg Engineering
Robinson Clerical
Smith Clerical

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

Outer Join[change | change source]

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)[change | change source]

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)[change | change source]

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[change | change source]

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