O'Reilly logo

SQL Bible, Second Edition by Boris M. Trukhnov, Alex Kriegel

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required