SQL Bible, Second Edition

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 CUST_ID_N columns.

Note

The preceding rule is only enforced logically; nothing prevents you from joining two tables based on unrelated columns (for example, ORDER_HEADER and PRODUCT using 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.

Note

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."

Inner Joins

This section discusses inner joins, which return only rows with matching values from both joined tables, excluding all other rows.

Two syntaxes for inner ...