Database Join Types

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

Below are two sample tables that will be referred to in the examples of the different join types supported by BarTender. Table A lists employees and their department IDs, while Table B lists department names and the department ID associated with each one of them. The common field that they have between them that 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 one of the most common join commands, and 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 field(s) that you specified. When there is no match between a row in Table A and a row in Table B, that row is simply omitted from the results. Using our example tables, an inner join using DepartmentID as the join condition would result in the following:

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

As you can see, because Donna's department ID on Table A is listed as NULL, she does not appear on the resulting inner join table. If we had assigned Donna to a department ID that was not listed on Table B, she similarly would not show up, as there wouldn't be any 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, as there is no equivalent field value.

Left Outer Join

A left outer join differs from an inner join in that it additionally 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, showing that there is no matching row in the right table. In this case, the result set lists all employees, even if they don't have an assigned department, as is the case with "Donna" in the table below.

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 additionally returned in the result set, even if there is no matching row in the left table, Table A. In this case, 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 table below.

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. In the case that a row does not have a matching row from other table, the missing value returns as NULL. 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