Database Joins Made Simple™ — Audit/Accounting Example

Database joins are widely used to extract information from tables in a database linked by a relationship. While familiar to database developers, they are less understood by other professionals, such as accountants or auditors, who may need them to perform data analytics. This article explains database joins using a practical example based on purchase orders and invoices tables present in most modern financial/accounting systems.

We have two tables: Purchase Orders (POs) and Invoices that we need to analyse, as shown in figure 1. The 1st (left) table is called POs, while the 2nd (right) table is called Invoices. POs and Invoices are linked through a common field containing a PO number. Therefore, we need to obtain a dataset containing all fields from POs and Invoices to perform various analyses.

Figure 1. POs and Invoices to be analysed

Extracting corresponding/linked POs and Invoices is usually done by executing a query against a database. In the query, we need to specify the field(s) that link the POs and Invoices and how to link them. Linking can be done in one of four ways described below.

Inner Join — returns all records with a match in the other table. For example, this…