Chapter 9: Multitable Queries

Overview

In the previous chapter we introduced the simple, or single-table query. However, you can hardly find a relational database with just one table — that contradicts the whole idea of RDBMS and normalization rules. To achieve meaningful results you usually have to retrieve information from multiple tables within a single query. All RDBMS allow you to join data from two or more tables based on a common column (or multiple columns), i.e., when this column(s) appears in both tables — under the same or a different name; for example (having ACME database in mind), ORDER_HEADER table could be joined with CUSTOMER using the ORDHDR_CUSTID_FN and CUST_ID_N columns.

Note 

The rule above is only enforced logically; for example, nothing prevents you from joining two tables based on unrelated columns (for example, ORDER_HEADER and PRODUCT using ORDHDR_ID_N and PROD_ID_N correspondingly), but the result of such a join would be completely meaningless nonsense.

All joins can generally be divided into two large groups — inner joins and outer joins.