Top web site - Joins, reporting queries, and subselects 14.3 Joins, reporting
Joins, reporting queries, and subselects 14.3 Joins, reporting queries, and subselects It s difficult to categorize some queries as advanced and others as basic. Clearly, the queries we ve shown you in the previous sections of this chapter aren t going to get you far. At the least you also need to know how joins work. The ability to arbitrarily join data is one of the fundamental strengths of relational data access. Joining data is also the basic operation that enables you to fetch several associated objects and collections in a single query. We now show you how basic join operations work and how you use them to write a dynamic fetching strategy. Other techniques we d consider advanced include nesting of statements with subselects and report queries that aggregate and group results efficiently. Let s start with joins and how they can be used for dynamic fetching. 14.3.1 Joining relations and associations You use a join to combine data in two (or more) relations. For example, you may join the data in the ITEM and BID tables, as shown in figure 14.1. (Note that not all columns and possible rows are shown; hence the dotted lines.) What most people think of when they hear the word join in the context of SQL databases is an inner join. An inner join is the most important of several types of joins and the easiest to understand. Consider the SQL statement and result in figure 14.2. This SQL statement is an ANSI-style inner join in the FROM clause. If you join tables ITEM and BID with an inner join, using their common attributes (the ITEM_ID column), you get all items and their bids in a new result table. Note that the result of this operation contains only items that have bids. If you want all items, and NULL values instead of bid data when there is no corresponding bid, you use a (left) outer join, as shown in figure 14.3. You can think of a table join as working as follows. First, you take a product of the two tables, by taking all possible combinations of ITEM rows with BID rows. Figure 14.1 The ITEM and BID tables are obvious candidates for a join operation.
If you are looking for cheap and quality webhost to host and run your website check Jboss Web Hosting services.