UNION, EXCEPT, and INTERSECT Clauses

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 in Figure .

 

        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 Figure , 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. Figure  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, Figure  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 in Figure .  

        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. Figure  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 in Figure  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. Figure  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.