8.3 Working with Subselects

Subqueries can be used to obtain search criteria within a SELECT statement. Normally the WHERE clause contains fixed parameters that cannot be modified. In subqueries, the parameters of a WHERE clause can be generated at runtime, which means that the query can stay the same even if the data in your tables changes. In addition, it will help you to reduce the number of queries needed to achieve a certain result.

8.3.1 Why Should You Use Subselects?

To see what you have just learned, we have included a simple example:

 phpbook=# CREATE TABLE stock (name text, pday date, price numeric(9,2)); CREATE 

You have just created a table containing information about the stock market. Now that the table has been created, it is time to insert some data. To save some overhead, you can use a COPY command instead of multiple INSERT commands:

 phpbook=# COPY stock FROM stdin; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> Cybertec     2002/06/12      23.4 >> Cybertec     2002/06/13      27.8 >> Cybertec     2002/06/14      27.5 >> Cybertec     2002/06/15      29.0 >> Cybertec     2002/06/16      32.1 >> \. 

The target is to retrieve all records where the price of the stock is higher than the average price in the table. This can be done by using two SELECT statements:

 phpbook=# SELECT AVG(price) FROM stock;       avg ---------------  27.9600000000 (1 row) 

The first SELECT statement retrieves the average price and this price can be used in the second query:

 phpbook=# SELECT * FROM stock WHERE price>'27.96';    name   |    pday    | price ----------+------------+-------  Cybertec | 2002-06-16 | 32.10  Cybertec | 2002-06-15 | 29.00 (2 rows) 

The result is correct. However, that's not the way things should be done for various reasons. The next example shows what happens when the database has to face concurrent operations on the data:

Table . Concurrent Operations

User 1	User 2
phpbook=# SELECT AVG(price) FROM stock; avg --------------- 27.9600000000 (1 row)
	INSERT INTO stock VALUES ('Cybertec', '2002/06/17', '50');
phpbook=# SELECT * FROM stock WHERE price>'27.96'; name | pday | price ----------+------------+------- Cybertec | 2002-06-16 | 32.10 Cybertec | 2002-06-15 | 29.00 Cybertec | 2002-06-17 | 50.00 (3 rows)

First User 1 computes the average of the values in the database. While User 1 is preparing a second SELECT statement, User 2 inserts a value into the database. After that User 1 queries the table again, and all of a sudden three values instead of two values are returned. The problem with this result is that the average value has changed after the record has been inserted, so the second query of User 2 does not return the records where the price is higher than the average price because the value of June 15th is still in the result. The new average price is found as follows:

 phpbook=# SELECT AVG(price) FROM stock;       avg ---------------  31.6333333333 (1 row) 

To get around the problem, you must use a subquery. One query is always looking at a consistent snapshot of data, so the result will always be correct changes during one query no longer affect running queries.

Take a look at a subquery:

 phpbook=# SELECT * FROM stock WHERE price > (SELECT AVG(price) FROM stock);    name   |    pday    | price ----------+------------+-------  Cybertec | 2002-06-16 | 32.10  Cybertec | 2002-06-17 | 50.00 (2 rows) 

As you can see, the price is computed using a subquery and the result is added to the WHERE clause afterward. The concept of subqueries is easy, but there is one thing you have to take care of. Take a look at a slightly modified version of the query:

 phpbook=# SELECT * FROM stock WHERE price > (SELECT * FROM stock); ERROR:  Subselect must have only one field 

PostgreSQL complains that the query returns more than just one cell, so the WHERE clause is wrong. This seems logical because PostgreSQL needs exactly one cell as price.

PostgreSQL provides a flexible system for handling subqueries. Every subquery can contain additional subqueries, so it is possible to perform even extremely complex queries with many subqueries involved.

8.3.2 Alternatives to Subselects

If you don't like complex subselects, it is possible to build simple workarounds by using views. The values generated by the subselect can be computed by the view and used by a query. Let's see how this can be done:

 phpbook=# CREATE VIEW view_stock_avgprice AS SELECT AVG(price) FROM stock; CREATE 

First a view is defined that is used to compute the average price of the stock. In the next step you can write a query to find the records:

 SELECT stock.*         FROM stock, view_stock_avgprice         WHERE price > view_stock_avgprice.avg; 

The correct result is returned:

    name   |    pday    | price ----------+------------+-------  Cybertec | 2002-06-16 | 32.10  Cybertec | 2002-06-17 | 50.00 (2 rows) 

In this case PostgreSQL will also make sure that the result is based on a consistent snapshot of data. Because the view does not contain the data, it will always return the most recent average value, so the query will return the right result.