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 code>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 be joined that is the same between them.
Join (Also called an Inner Join)
Imagine two tables called Employee and Department.
| LastName | DepartmentID |
|---|---|
| Rafferty | 31 |
| Jones | 33 |
| Heisenberg | 33 |
| Robinson | 34 |
| Smith | 34 |
| Williams | Template:Null result |
| 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.
SELECT LastName, DepartmentName FROM employee join department on department.DepartmentID = employee.DepartmentID;
It would make a table that looks like this:
| LastName | DepartmentName |
|---|---|
| Rafferty | Sales |
| Jones | Engineering |
| Heisenberg | Engineering |
| Robinson | Clerical |
| Smith | Clerical |