Database Join Types

Joins are basic functions of databases that you can use to merge the contents of two different tables and/or databases into a single view that is based on fields that they have in common (that is, the join condition). For more information, refer to About Table and Database Joins.

The following sample tables are referred to in the examples of the different join types that are supported by BarTender. Table A lists employees and their department IDs, and Table B lists department names and their department IDs. The field that they have in common, which is used as the join condition, is DepartmentID.

FirstName	DepartmentID
Joe	31
Jane	33
Mark	33
Mary	34
Dan	34
Donna	NULL

DepartmentID	DepartmentName
31	Sales
33	Engineering
34	Clerical
35	Marketing

Inner Join

An inner join is a common join command that is often treated as the default join type. In an inner join, new results are created by returning and combining all rows from the specified tables that have matching values for the join fields that you specified. When there is no match between a row in Table A and a row in Table B, that row is omitted from the results.

For the sample tables, an inner join that uses DepartmentID as the join condition results in the following result set.

FirstName	DepartmentID	DepartmentName
Joe	31	Sales
Jane	33	Engineering
Mark	33	Engineering
Mary	34	Clerical
Dan	34	Clerical

Because Donna's department ID in Table A is listed as NULL, she does not appear on the resulting inner join table. Similarly, if she were assigned to a department ID that was not listed in Table B, she would not appear, because there would not be a corresponding row with which to associate her.

NULL values are treated differently than other values and cannot be compared to a 0 (zero) value or even to another NULL value. For this reason, an inner join cannot return any rows that contain a NULL value in a field that is being used in the join condition, because there is no equivalent field value.

Left Outer Join

A left outer join differs from an inner join in that it returns all rows from the left table (Table A), even if there is no matching row in the right table (Table B). The rows from the left table that do not have a matching row in the right table are listed in the join result with NULL values, which shows that there is no matching row in the right table. In this example, the result set lists all employees, even if they do not have an assigned department, as is the case with "Donna" in the following table.

FirstName	DepartmentID	DepartmentName
Joe	31	Sales
Jane	33	Engineering
Mark	33	Engineering
Mary	34	Clerical
Dan	34	Clerical
Donna	NULL	NULL

Right Outer Join

A right outer join behaves exactly like a left outer join except in reverse, so that all entries in the right table (Table B), are returned in the result set, even if there is no matching row in the left table (Table A). In this example, the result set lists all departments, even if there is no employee associated with a department, as is the case with the "Marketing" department in the following table.

FirstName	DepartmentID	DepartmentName
Joe	31	Sales
Jane	33	Engineering
Mark	33	Engineering
Mary	34	Clerical
Dan	34	Clerical
NULL	35	Marketing

Full Outer Join

A full outer join returns a result that lists all rows from both the right and the left table, even if a row has no matching value from the other table. When a row does not have a matching row from other table, the missing value is returned as NULL. In this example, this result includes both the "Marketing" department, even though it has no employees, and the employee "Donna," even though she has no department.

FirstName	DepartmentID	DepartmentName
Joe	31	Sales
Jane	33	Engineering
Mark	33	Engineering
Mary	34	Clerical
Dan	34	Clerical
Donna	NULL	NULL
NULL	35	Marketing