Problem solve Get help with specific problems with your technologies, process and projects.

UNION, EXCEPT, and INTERSECT clauses

Here's a way for SELECTs to be combined to create more powerful queries.

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


Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close