SQL JOIN function

Here's a continuation of my review of Getting Started with SQL, an O'Reilly guide by Thomas Nield, last blogged about on November 27, 2016.

Chapter 8 of the guide covers the JOIN function, which is roughly analogous to Excel's VLOOKUP formula but allows a whole array to be pulled.

The INNER JOIN function allows a user to merge fields from two different tables by referring to a field that both tables have in common. In this example, we see that the CUSTOMER_ORDER and CUSTOMER tables only have the CUSTOMER_ID field in common.

So we can see this command selects a number of fields from both tables and then with the command FROM [TABLE 1] INNER JOIN [TABLE 2] specifies where they are to come from and which field is used as a cross reference. When the same field name appears in more than one table we put the table name in front of the field name separated with a period. INNER JOIN will exclude any records that are not referenced in both tables by the same CUSTOMER_id. If INNER JOINT is substituted with LEFT JOIN, all of the records from TABLE 1 will be in the results regardless. The fields which only appear in TABLE 2 will show NULL values. RIGHT JOIN simply does the opposite, and OUTER JOIN will get all records from both tables.

If you want to check for orphaned records in TABLE 1 - just find the records from TABLE 1 that don't have any matches in TABLE 2 you can run this script:

The WHERE command limits the results to the null values in TABLE 2.

The script to combine three tables would appear this way, with TABLE 1 being referenced first at FROM, and then TABLE 2 and TABLE 3 being specified by separate INNER JOIN commands.