PostgreSQL

test(>

WHERE order id = 14673

test(>

name

);

Fleer Gearworks, Inc. (1 row)

Figure 8.8: Subqueries can replace some joins

test=> SELECT fl.firstname, fl.lastname, fl.age test-> FROM friend fl, friend f2 test-> WHERE fl.state = f2.state

test-> GROUP BY f2.state, fl.firstname, fl.lastname, fl.age test-> HAVING fl.age = max(f2.age) test-> ORDER BY firstname, lastname;

firstname	| lastname |	age
Ned	| Millstone |	27
Sandy	| Gleason |	25
Sandy	| Weber |	33
Victor	| Tabor |	22
(4 rows)
test=> SELECT	fl.firstname, fl.lastname,	fl.age
test-> FROM	friend fl
test-> WHERE	age = (
test(>	SELECT MAX(f2.age)
test(>	FROM friend f2
test(>	WHERE fl.state = f2.	state
test(>	)
csl Ш Q or О Л +-> (/) cu +->	BY firstname, lastname;
firstname	| lastname |	age
Ned	| Millstone |	27
Sandy	| Gleason |	25
Sandy	| Weber |	33
Victor	| Tabor |	22
(4 rows)

Figure 8.9: Correlated subquery

which creates a correlated subquery because the subquery references a column from the upper query. Such a subquery cannot be evaluated once and the same result used for all rows. Instead, it must be evaluated for every row because the upper column value can change.

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