Inner and Outer Joins

Nothing is more confounding to AIS students than inner and outer joins. To understand them, let’s look at some sample data in two tables from the sales process.

The first table is tblShipProduct. It records shipments to customers.

tblShipProduct

The second table is tblReceiveCash. It records cash receipts from customers. Notice that Shipping# appears in this table as a foreign key. It connects a cash receipt back to the shipment being paid.

Also notice something important: not all shipments have been paid for.

tblReceiveCash

Shipments 1003 and 1006 do not appear in tblReceiveCash. That means those shipments have not been paid for yet.

Inner Join

If we join these two tables with an inner join, we can find shipments for which we have received at least one payment.

An inner join returns only the records that match in both tables.

In this case, a shipment must exist in tblShipProduct, and there must also be a matching receipt in tblReceiveCash.

SELECT
    tblShipProduct.[Shipping#],
    tblReceiveCash.[Receipt#],
    tblReceiveCash.[Receipt Amount]
FROM
    tblShipProduct
INNER JOIN
    tblReceiveCash
ON
    tblShipProduct.[Shipping#] = tblReceiveCash.[Shipping#];

Query View: Inner Join

This result tells us which shipments have related cash receipts. Notice that shipment 1002 appears twice because it had two payments. Shipment 1007 also appears twice for the same reason.

Also notice that shipments 1003 and 1006 do not appear at all. They are in the shipment table, but they do not have matching records in the cash receipt table. Because an inner join only keeps matching records, those unpaid shipments are excluded.

Outer Join

What if we want to see the shipments that have not been paid for? Now we need an outer join.

More specifically, we need a left outer join. A left outer join keeps all records from the first table listed in the query, even if there is no matching record in the second table.

Here, we want to keep all shipments from tblShipProduct, whether or not they have related cash receipts.

SELECT
    tblShipProduct.[Shipping#],
    tblShipProduct.[Shipping Date],
    tblShipProduct.[Customer#],
    tblShipProduct.[Shipment Amount],
    tblReceiveCash.[Receipt#],
    tblReceiveCash.[Receipt Amount]
FROM
    tblShipProduct
LEFT JOIN
    tblReceiveCash
ON
    tblShipProduct.[Shipping#] = tblReceiveCash.[Shipping#];

Query View: Left Outer Join

This query keeps every shipment, even when there is no matching cash receipt. For shipments 1003 and 1006, the receipt fields are blank. In database terms, those values are NULL. That means there was no matching record in tblReceiveCash.

Finding Only the Unpaid Shipments

The left outer join above shows all shipments. But what if we want to see only the shipments that have not been paid?

We can add a WHERE clause that keeps only the rows where the receipt number is missing.

SELECT
    tblShipProduct.[Shipping#],
    tblShipProduct.[Shipping Date],
    tblShipProduct.[Customer#],
    tblShipProduct.[Shipment Amount],
    tblReceiveCash.[Receipt#],
    tblReceiveCash.[Receipt Amount]
FROM
    tblShipProduct
LEFT JOIN
    tblReceiveCash
ON
    tblShipProduct.[Shipping#] = tblReceiveCash.[Shipping#]
WHERE
    tblReceiveCash.[Receipt#] IS NULL;

Query View: Unpaid Shipments

This final query is useful because it answers an important business question: Which shipments have not yet been paid for? In an accounting information system, that question matters. It can help a company follow up with customers, investigate collection issues, and monitor accounts receivable.

The Main Difference

The difference between an inner join and an outer join is simple once you focus on what each one keeps.

• An inner join keeps only matching records.
• A left outer join keeps all records from the left table and adds matching records from the right table when they exist.

In this example:

• The inner join shows shipments that have received payments.
• The left outer join shows all shipments, including those with no payment.
• The left outer join with WHERE Receipt# IS NULL shows only unpaid shipments.

When you are working with accounting data, joins are not just technical database tools. They help answer practical accounting questions. Did we ship the product? Did we receive the cash? Which shipments are still unpaid? Which records match, and which records do not?

Try it yourself

Use the Join Explorer below to see how the query result changes when you switch between an inner join, a left outer join, and unmatched records only.