Join (SQL) - Sample Tables

Sample Tables

Relational databases are often normalized to eliminate duplication of information when objects may have one-to-many relationships. For example, a Department may be associated with many different Employees. Joining two tables effectively creates another table which combines information from both tables. This is at some expense in terms of the time it takes to compute the join. While it is also possible to simply maintain a denormalized table if speed is important, duplicate information may take extra space, and add the expense and complexity of maintaining data integrity if data which is duplicated later changes.

All subsequent explanations on join types in this article make use of the following two tables. The rows in these tables serve to illustrate the effect of different types of joins and join-predicates. In the following tables the DepartmentID column of the Department table (which can be designated as Department.DepartmentID) is the primary key, while Employee.DepartmentID is a foreign key.

Employee table

LastName	DepartmentID
Rafferty	31
Jones	33
Steinberg	33
Robinson	34
Smith	34
John	NULL

Department table

DepartmentID	DepartmentName
31	Sales
33	Engineering
34	Clerical
35	Marketing

Note: In the Employee table above, the employee "John" has not been assigned to any department yet. Also, note that no employees are assigned to the "Marketing" department.

This is the SQL to create the aforementioned 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);