PostgreSQL

Скачать в pdf «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»