Regular expressions, aggregates, and joins are powerful SQL features that allow the construction of complex queries....
In some cases, however, even these tools may prove inadequate. This tip, excerpted from Bruce Momjian's new book PostgreSQL: Introduction and Concepts, shows one method for how SELECTs can be combined to create even more powerful queries.
Sometimes a single SELECT statement cannot produce the desired result. UNION, EXCEPT, and INTERSECT allow SELECT statements to be chained together, enabling the construction of more complex queries.
For example, suppose we want to output the friend table's firstname and lastname in the same column. Normally, two queries would be required, one for each column.With UNION, however, the output of two SELECTs can be combined in a single query, as shown below.
test=> SELECT firstname test-> FROM friend test-> UNION test-> SELECT lastname test-> FROM friend test-> ORDER BY 1; firstname ---------------------- Dean Dick Gleason Millstone Ned Sandy Tabor Victor Weber Yeager (10 rows)
The query combines two columns into a single output column.
UNION allows an unlimited number of SELECT statements to be combined to produce a single result. Each SELECT must return the same number of columns. If the first SELECT returns two columns, the other SELECTs must return two columns as well. The column types must also be similar. If the first SELECT returns an INTEGER value in the first column, the other SELECTs must return an INTEGER in their first columns, too.
With UNION, an ORDER BY clause can be used only at the end of the last SELECT. The ordering applies to the output of the entire query. In the example above, the ORDER BY clause specifies the ordering column by number. Instead of a number, we could use ORDER BY firstname because UNION's output labels are the same as the column labels of the first SELECT.
As another example, suppose we have two tables that hold information about various animals. One table holds information about aquatic animals, and the other contains data about terrestrial animals. Two tables are used because each records information specific to one class of animal. The aquatic_animal table holds information meaningful only for aquatic animals, like preferred water temperature. The terrestrial_animal table holds information meaningful only for terrestrial animals, like running speed. We could have included the animals in the same table, but keeping them separate was clearer. In most cases, we will deal with the animal types separately.
Suppose we need to list all of the animals, both aquatic and terrestrial. No single SELECT can show the animals from both tables. We cannot join the tables because no join key exists; joining is not desired. Instead, we want rows from the terrestrial_animal table and the aquatic_animal table output together in a single column. The example below shows how these two tables can be combined with UNION.
test=> INSERT INTO terrestrial_animal (name) VALUES ('tiger'); INSERT 19122 1 test=> INSERT INTO aquatic_animal (name) VALUES ('swordfish'); INSERT 19123 1 test=> SELECT name test-> FROM aquatic_animal test-> UNION test-> SELECT name test-> FROM terrestrial_animal; name -------------------------------- swordfish tiger (2 rows)
By default, UNION prevents duplicate rows from being displayed. For example, the code below inserts penguin into both tables, but penguin is not duplicated in the output.
test=> INSERT INTO aquatic_animal (name) VALUES ('penguin'); INSERT 19124 1 test=> INSERT INTO terrestrial_animal (name) VALUES ('penguin'); INSERT 19125 1 test=> SELECT name test-> FROM aquatic_animal test-> UNION test-> SELECT name test-> FROM terrestrial_animal; name -------------------------------- penguin swordfish tiger (3 rows)
To preserve duplicates, you must use UNION ALL, as shown here:
test=> SELECT name test-> FROM aquatic_animal test-> UNION ALL test-> SELECT name test-> FROM terrestrial_animal; name -------------------------------- swordfish penguin tiger penguin (4 rows)
You can perform more complex operations by chaining SELECTs. EXCEPT allows all rows to be returned from the first SELECT except rows that appear in the second SELECT. The listing below shows an EXCEPT query.
test=> SELECT name test-> FROM aquatic_animal test-> EXCEPT test-> SELECT name test-> FROM terrestrial_animal; name -------------------------------- swordfish (1 row)
Although the aquatic_animal table contains swordfish and penguin, the query above returns only swordfish. The penguin is excluded from the output because it is returned by the second query. While UNION adds rows to the first SELECT, EXCEPT subtracts rows from it.
INTERSECT returns only rows generated by all SELECTs. The listing below uses INTERSECT to display only penguin. While several animals are returned by the two SELECTs, only penguin is returned by both SELECTs.
test=> SELECT name test-> FROM aquatic_animal test-> INTERSECT test-> SELECT name test-> FROM terrestrial_animal; name -------------------------------- penguin (1 row)
You can link any number of SELECTs using these methods. The previous examples allowed multiple columns to occupy a single result column. Without the ability to chain SELECTs using UNION, EXCEPT, and INTERSECT, it would be impossible to generate some of these results. SELECT chaining can enable other sophisticated operations, such as joining a column to one table in the first SELECT, then joining the same column to another table in the second SELECT.
About the Author
Bruce Momjian is Vice President, Database Development, at Great Bridge, Inc. He is a leader and co-founder of the PostgreSQL Global Development Team and has independently developed open source software since 1991. Momjian maintains the official Postgres "ToDo" list, and is heavily involved in the meritocracy-driven peer review of software code contributed to the Postgres project. Since 1989, he has served as a senior consultant to Wilson Technology Associates, where he created customer relational database applications for some of the nation's largest law firms. He holds a master's degree in mathematics education from Beaver College and a bachelor's degree in history from Columbia University.
For More Information
- What do you think about this tip? E-mail us at [email protected] with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Check out our new Ask the Experts feature: Our SQL gurus are waiting to answer your toughest questions.