Chapter 9. Multitable Queries
The previous chapter 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 implementations allow you to join data from two or more tables based on a common
column (or multiple columns) — that is, when the column(s) appears in both tables — under the same or a different name; for example (with the Self-join ACME database in mind), the
ORDER_HEADER table could be joined with Right, left, and full outer joins
CUSTOMER using the
ORDHDR_CUSTID FN and
The preceding rule is only enforced logically; nothing prevents you from joining two tables based on unrelated columns (for example,
ORDHDR_ID_N correlated queries and
PROD_ID_N correspondingly), but the result of such a join would be completely meaningless.
All joins can generally be divided into two large groups — inner joins and outer joins.
A knowledge of Set Theory basic principles could be very useful for better understanding how table joins work. See Appendix K, "A Brief Introduction to the Number Systems, Boolean Algebra, and Set Theory."
This section discusses inner joins, which return only rows with matching values from both joined tables, excluding all other rows.