Скачать в pdf «PostgreSQL»

Subqueries can also eliminate table joins. For example, consider the mail-order parts company used in Figures 6.3 and 6.4 on page 61. To find the customer name for order number 14673, we join the salesorder and customer tables, as shown in the first query in Figure 8.8. The second query in the figure does not have a join, but instead gets the customerjd from a subquery. In general, if a table is involved in only one join, and no columns from the table appear in the query result, the join can be eliminated and the table moved to a subquery.

In this example, we have specified salesorder.customer_id and customer.customerjd to clearly indicate the tables being referenced. However, this specification is not required. We could have used only customerjd in both places. PostgreSQL finds the first table in the same subquery or higher that contains a matching column name.

Subqueries can be used anywhere a computed value is needed. Each has its own from and where clauses. It can also have its own aggregate, group by, and having clauses. A subquery’s only interaction with the upper query is the value it returns. This approach allows sophisticated comparisons that would be difficult if the subquery’s clauses had to be combined with those of the upper query.

Subqueries as Correlated Values

In addition to acting as constants in queries, subqueries can act as correlated values. Correlated values vary based on the row being processed. A normal subquery is evaluated once and its value used by the upper query. In a correlated subquery, the subquery is evaluated repeatedly for every row processed.

Скачать в pdf «PostgreSQL»