Join types

There are three types of join:

INNER JOIN

 

LEFT JOIN

(otherwise known as "left outer join")

RIGHT JOIN

(otherwise known as "right outer join")

Inner joins are the most common types of join and only return records that match in both tables.

For example, joining the Holiday table and Employee table using:

SELECT Employee.*, Holiday.* FROM Employee INNER JOIN Holiday ON Employee.EmployeeID = Holiday.EmployeeID;

produces the following results table:

EmployeeID

FirstName

LastName

HolidayID

StartDate

DaysHoliday

1002

Chris

England

1

1/2/97

5

1002

Chris

England

3

24/2/97

3

1003

Andreas

Smith

2

21/2/97

1

1004

Jim

Smith

4

1/3/97

4

Note that there no records here for EmployeeID 1001 or 1005 since there are no matching records in the Holiday table, that is, these employees have not taken any holiday to date.

If you wanted to include every employee, you’d use a LEFT JOIN as follows:

SELECT Employee.*, Holiday.* FROM Employee LEFT JOIN Holiday ON Employee.EmployeeID = Holiday.EmployeeID;

Every record from the table "to the left" of the join statement is included.

Similarly, you can issue a RIGHT JOIN statement to include all records from the table to the right of the join statement:

SELECT Employee.*, Holiday.* FROM Employee RIGHT JOIN Holiday ON Employee.EmployeeID = Holiday.EmployeeID;

Strictly speaking, this should return the same results table as the INNER JOIN, since it makes no sense to assign holidays to non-existent employees. However, this can sometimes happen, especially with "historical" data – data imported from a system not set-up to obey referential integrity.

See also:

Relational links and joins

Unmatched queries

Other types of SQL statements