8.11 Combining Queries and Ranges of Values

In some cases the queries you have already seen will not be enough to satisfy your demands. The ANSI SQL standard defines several ways to combine queries, which are covered in this section.

Let's define two tables: The first one stores a list of registered users. The second table contains the postings of the various users:

 phpbook=# CREATE TABLE reg_user (name text, regdate date); CREATE phpbook=# CREATE TABLE user_post (name text, postdate date, message text); CREATE 

For this scenario three users are added to the table:

 phpbook=# INSERT INTO reg_user VALUES ('Paul', '2001/03/12'); INSERT 26059 1 phpbook=# INSERT INTO reg_user VALUES ('Shelley', '2001/02/22'); INSERT 26060 1 phpbook=# INSERT INTO reg_user VALUES ('Alan', '1999/12/01'); INSERT 26061 1 

In addition, the postings of the various users are added to the second table:

 phpbook=# INSERT INTO user_post VALUES ('Paul', '2001/03/13','do you think this is true?'); INSERT 26062 1 phpbook=# INSERT INTO user_post VALUES ('Paul', '2001/03/14','i would use a trigger instead'); INSERT 26063 1 phpbook=# INSERT INTO user_post VALUES ('Shelley', '2001/03/15','rules are much better'); INSERT 26064 1 

Three keywords for combining queries are defined: EXCEPT is used to subtract one result from the other. INTERSECT computes those records that can be found in both queries and UNION adds the results of two queries. Take a look at a query using EXCEPT:

 phpbook=# SELECT name FROM reg_user EXCEPT SELECT name FROM user_post;  name ------  Alan (1 row) 

This query returns all records that cannot be found in the second table. In other words all users who have not posted yet will be returned.

The next example shows a query containing two EXCEPT statements:

 phpbook=# SELECT name FROM reg_user EXCEPT SELECT name FROM user_post EXCEPT SELECT 'Alan';  name ------ (0 rows) 

All people who haven't posted yet except Alan are returned.

If you want to retrieve all names that can be found in both tables, you can use INTERSECT:

 phpbook=# SELECT name FROM reg_user INTERSECT SELECT name FROM user_post;   name ---------  Paul  Shelley (2 rows) 

Two people are registered and have posted messages. The same result can be achieved by performing a simple join operation. However, some details must be taken into consideration:

 phpbook=# SELECT reg_user.name FROM reg_user, user_post WHERE reg_user.name=user_post.name;   name ---------  Paul  Paul  Shelley (3 rows) 

DISTINCT has to be added to the query in order to retrieve every record only once:

 phpbook=# SELECT DISTINCT reg_user.name FROM reg_user, user_post WHERE reg_user.name=user_post.name;   name ---------  Paul  Shelley (2 rows) 

To combine the result of two queries, you can use UNION:

 phpbook=# SELECT name FROM reg_user UNION SELECT name FROM user_post;   name ---------  Alan  Hugo  Paul  Shelley (4 rows) 

All names that can be found in req_user and user_post are retrieved. Let's try to retrieve two columns:

 phpbook=# SELECT name FROM reg_user UNION SELECT name, postdate FROM user_post; ERROR:  Each UNION query must have the same number of columns 

As you can see, PostgreSQL does not accept queries connected with either EXCEPT, INTERSECT, or UNION when the number of columns in the queries do not match. This is an important point that has to be taken into consideration when combining queries.

The queries combined are flexible, as you can see in the next example:

 SELECT name         FROM reg_user UNION SELECT name         FROM user_post         WHERE postdate BETWEEN '2000/1/1' AND '2002/1/1'; 

BETWEEN can be used to define ranges of values. In this case postings between January 1, 2000 and January 1, 2002 are retrieved:

   name ---------  Alan  Paul  Shelley (3 rows)