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.

Imagine two tables called Employee and Department.

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:

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

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.

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.

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;

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.

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.

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.

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: