Database Join Types

Joins are basic functions of databases that enable you to merge the contents of two different database tables into a single view. In order to join multiple tables, the tables involved have to share certain pieces of information.

If you need to use multiple tables and/or databases in BarTender, these tables must be connected using one of the supported database join types.

Below, we have two sample tables that will serve as the data sources for the database join types that we will discuss in this article. Table 1 lists employees and their department IDs, while Table 2 lists department names and the department ID associated with each one of them. Having the two tables allows you to easily change information without having to work in multiple rows and columns. For instance, if Jean-Luc is in Department ID 31, a join will show that specifically, Jean-Luc is in the sales department. Given that 31 will always represent Sales, adding additional sales people is as simple as putting a 31 next to their names.

Table 1:

Table 2:

The following database join types are supported:

Inner Join

The 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 combining column values of two tables (in this case Table 1 and Table 2) to find all matching rows of 1 & 2 and returning all records which match in a results table. In situations where there is not a correlation between a row in Table 1 and a row in Table 2, that row is simply omitted. Using our example tables, an inner join would result in the following:

As you can see, because Worf has not been assigned a department ID on Table 1 yet, he does not appear on the resulting inner join table. If we had assigned Worf to a department ID not listed on Table 2, he similarly would not show up, as there wouldn't be any corresponding row with which to associate him.

Right Outer Join • Full Outer Join • Return to Top

Left Outer Join

Outer joins differ from inner joins in that if there is no correspondence between a row on one table and a row on the other, they will still return a result, even if that result is NULL. A Left Outer Join returns results for all the rows on the left table, even if nothing corresponds with the right table. In our example, Table 1 is the left, while Table 2 is the right. This allows you to search for any employee, even if they don't have a department to which they are assigned, as is the case with Worf in the table below.

Full Outer Join • Return to Top

Right Outer Join

Right outer joins behave exactly like left outer joins, except in reverse, so all entries on the right table (Table 2) will return results, even if they don't correspond with any rows on the left table (Table 1). Thus, we can use the right outer join to return results about a department, even if that department does not have any employees associated with it.

Inner Join • Return to Top

Full Outer Join

Full outer joins combine the effects of the left and right outer joins, returning results for all rows, even if a row lacks a corresponding entry from the other table. In these situations, the missing sections return as NULL. Thus, we can search for the Marketing department, even though it has no employees, as well as Worf, who is still without a department. If we hire someone as Marketing Director, we don't have to modify Table 2 at all.

Inner Join • Left Outer Join • Return to Top

Related Topics

• Joins Tab
• Multi-Database Joins Tab

Open topic with navigation