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

Subqueries can be quite complicated. If you have trouble understanding this section, skip over it and return to it later.

Subqueries as Constants

A subquery, also called a subselect, can replace a constant in a query. While a constant never changes, a subquery’s value is computed every time the query is executed.

As an example, let’s use the friend table from the previous chapters. Suppose we want to find friends who are not in the same state as Dick Gleason. We could place his state in the query using the constant string ‘NJ’. If he moves to another state, however, we would have to change the query. Using the state column is more reliable.

8.7 shows two ways to generate the correct result. One query uses a self-join to do the comparison to Dick Gleason’s state. (Self-joins were covered in Section 6.10.) The last query uses a subquery that returns the state as ‘NJ’; this value is used by the upper query. The subquery has taken the place of a constant. Unlike a constant, however, the value is computed every time the query is executed.

Although we have used table aliases in the subquery for clarity, they are not required. A column name with no table specification is automatically paired with a table in the current subquery. If no matching table is found in the current subquery, higher parts of the query are searched for a match. The state, firstname, and lastname in the subquery refer to the instance of the friend table in the subquery. The same column names in the upper query automatically refer to the friend instance in that query. If a column name matches two tables in the same subquery, an error is returned, indicating the column is ambiguous.

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