A JOIN is a means for combining fields from two tables by using values common to each. ANSI standard SQL specifies four types of JOIN: INNER, OUTER, LEFT and RIGHT. As a special case, a table can JOIN to itself in a self-join.
The following is the SQL to create two tables:
CREATE TABLE department ( DepartmentID INT, DepartmentName VARCHAR(20) ); CREATE TABLE employee ( LastName VARCHAR(20), DepartmentID INT ); INSERT INTO department(DepartmentID, DepartmentName) VALUES(31, 'Sales'); INSERT INTO department(DepartmentID, DepartmentName) VALUES(33, 'Engineering'); INSERT INTO department(DepartmentID, DepartmentName) VALUES(34, 'Clerical'); INSERT INTO department(DepartmentID, DepartmentName) VALUES(35, 'Marketing'); INSERT INTO employee(LastName, DepartmentID) VALUES('Rafferty', 31); INSERT INTO employee(LastName, DepartmentID) VALUES('Jones', 33); INSERT INTO employee(LastName, DepartmentID) VALUES('Steinberg', 33); INSERT INTO employee(LastName, DepartmentID) VALUES('Robinson', 34); INSERT INTO employee(LastName, DepartmentID) VALUES('Smith', 34); INSERT INTO employee(LastName, DepartmentID) VALUES('John', NULL);
| LastName | DepartmentID |
|---|---|
| Rafferty | 31 |
| Jones | 33 |
| Steinberg | 33 |
| Robinson | 34 |
| Smith | 34 |
| John | NULL |
| DepartmentID | DepartmentName |
|---|---|
| 31 | Sales |
| 33 | Engineering |
| 34 | Clerical |
| 35 | Marketing |
- Cross join
CROSS JOIN returns the Cartesian(笛卡儿积) product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each roow from the second table.
The CROSS JOIN does not apply any predicate to filter records from the joined table. Programmers can further filter the results of a cross join by using WHERE caluse.
SELECT * FROM employee CROSS JOIN department;
| Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
|---|---|---|---|
| Rafferty | 31 | Sales | 31 |
| Jones | 33 | Sales | 31 |
| Steinberg | 33 | Sales | 31 |
| Smith | 34 | Sales | 31 |
| Robinson | 34 | Sales | 31 |
| John | NULL | Sales | 31 |
| Rafferty | 31 | Engineering | 33 |
| Jones | 33 | Engineering | 33 |
| Steinberg | 33 | Engineering | 33 |
| Smith | 34 | Engineering | 33 |
| Robinson | 34 | Engineering | 33 |
| John | NULL | Engineering | 33 |
| Rafferty | 31 | Clerical | 34 |
| Jones | 33 | Clerical | 34 |
| Steinberg | 33 | Clerical | 34 |
| Smith | 34 | Clerical | 34 |
| Robinson | 34 | Clerical | 34 |
| John | NULL | Clerical | 34 |
| Rafferty | 31 | Marketing | 35 |
| Jones | 33 | Marketing | 35 |
| Steinberg | 33 | Marketing | 35 |
| Smith | 34 | Marketing | 35 |
| Robinson | 34 | Marketing | 35 |
| John | NULL | Marketing | 35 |
- Inner join
An inner join is most common join operation used in applications and can be regared ad the default join-type.
INNER JOIN creates a new result table by combining column values of two tables(A and B) based upon the join-predicate. The query compares each row of A with each row of B to all pairs of rows which satisfy the join-predicate.
1. Explicit join notation:
SELECT * FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID;
2. Implicit join notation:
SELECT * FROM employee, department WHERE employee.DepartmentID=department.DepartmentID;
| Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
|---|---|---|---|
| Robinson | 34 | Clerical | 34 |
| Jones | 33 | Engineering | 33 |
| Smith | 34 | Clerical | 34 |
| Steinberg | 33 | Engineering | 33 |
| Rafferty | 31 | Sales | 31 |
Note: Programmers should take special care when joining tables on columns that contain NULL values, since NULL will never match any other value(not even NULL itself), unless the join condition explicitly uses the IS NULL or IS NOT NULL predicates.
Equi-join:
SELECT * FROM employee JOIN department ON employee.DepartmentID = department.DepartmentID;
SELECT * FROM employee, department WHERE employee.DepartmentID = department.DepartmentID;
SELECT * FROM employee INNER JOIN department USING (DepartmentID);
Natural join:
SELECT * FROM employee NATURAL JOIN department;
As with the explicit USING clause, only one DepartmentID column occurs in the joined table, with no qualifier:
| DepartmentID | Employee.LastName | Department.DepartmentName |
|---|---|---|
| 34 | Smith | Clerical |
| 33 | Jones | Engineering |
| 34 | Robinson | Clerical |
| 33 | Steinberg | Engineering |
| 31 | Rafferty | Sales |
- Outer join:
An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table's rows are retained (left, right, or both).
1. Left outer joins:
The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B).
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 |
| John | NULL | NULL | NULL |
| Steinberg | 33 | Engineering | 33 |
2. Right outer join:
Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B.
SELECT * FROM employee RIGHT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;
| Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
|---|---|---|---|
| Smith | 34 | Clerical | 34 |
| Jones | 33 | Engineering | 33 |
| Robinson | 34 | Clerical | 34 |
| Steinberg | 33 | Engineering | 33 |
| Rafferty | 31 | Sales | 31 |
| NULL | NULL | Marketing | 35 |
3. Full outer join:
Conceptually, a full outer join combines the effect of applying both left and right outer joins. Where records in the FULL OUTER JOINed tables do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those records that do match, a single row will be produced in the result set (containing fields populated from both tables).
SELECT * FROM employee FULL OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;
| Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
|---|---|---|---|
| Smith | 34 | Clerical | 34 |
| Jones | 33 | Engineering | 33 |
| Robinson | 34 | Clerical | 34 |
| John | NULL | NULL | NULL |
| Steinberg | 33 | Engineering | 33 |
| Rafferty | 31 | Sales | 31 |
| NULL | NULL | Marketing | 35 |