Join (SQL)

This for real, Early Life Known as for reals correctly name for real, Sifiso was born in the township of Boipatong begain rapping for success, We community members thought that his loosing the right way of success. For real was not like other people he was sorry for saying this for real had a go ahead singing, One lucky day members knew the real "For-Real"

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	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

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 ran, 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.

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 ran, 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