Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Comparing Sets with UNION, INTERSECT & EXCEPT

While joins are used in SQL to combine column values into a single row, the UNION, INTERSECT and EXCEPT clauses exist to merge or omit row data by comparing column values, returning a new result set based on this comparison. Each of these keywords may be used at the end of a valid SQL query and followed by a second query, in order to compare the resultant data sets, and then either merge or omit rows based on that comparison.

When comparing data sets in this manner, it is required that they each have the same number of columns, as well as the same column type. Note that they do not need to have the same name, or be queried from the same table or data source.

UNION

A pair of queries merged with the UNION keyword will combine all non-distinct rows into a single data set. Like rows will not be duplicated.

INTERSECT

A pair of queries merged with the INTERSECT keyword will cause any rows not found in both data sets to be omitted. As such, the only rows returned are those that overlap between the two query result sets.

EXCEPT

A pair of queries merged with the EXCEPT keyword will cause any rows found in both data sets to be omitted from the returned data set. As such, only rows found in the query to the left of the EXCEPT clause that are not found in the query to the right of the clause will be returned.

Example 4-46, Example 4-47, and Example 4-48 each demonstrate these keywords by combining and omitting rows from comparative data sets. Example 4-46 creates a result set by combining several authors' last names with book titles via the UNION keyword.

Example 4-47 demonstrates the selection of ISBN numbers from the books table, limited to rows which intersect with the query on the shipments table for books which have records of more than two shipments. Finally, Example 4-48 demonstrates the removal of any rows from the first query which are matched completely in the second.

Example 4-46. Using UNION

booktown=# 
SELECT title FROM books

booktown-# 
       UNION

booktown-# 
       SELECT last_name FROM authors

booktown-# 
       LIMIT 11;

            title
-----------------------------
 2001: A Space Odyssey
 Alcott
 Bartholomew and the Oobleck
 Bianco
 Bourgeois
 Brautigan
 Brite
 Brown
 Christiansen
 Clarke
 Denham
(11 rows)

Example 4-47. Using INTERSECT

booktown=# 
SELECT isbn FROM editions

booktown-# 
       INTERSECT

booktown-# 
       SELECT isbn FROM shipments

booktown-# 
              GROUP BY isbn

booktown-# 
              HAVING count(id) > 2;

    isbn
------------
 039480001X
 0394800753
 0451160916
 0590445065
 0694003611
(5 rows)

Example 4-48. Using EXCEPT

booktown=# 
SELECT last_name, first_name

booktown-# 
       FROM authors

booktown-# 
       EXCEPT

booktown-# 
       SELECT last_name, first_name

booktown-# 
              FROM authors AS a (author_id)

booktown-# 
              NATURAL INNER JOIN books

booktown-# 
       ORDER BY first_name ASC;

 last_name | first_name
-----------+------------
 Denham    | Ariel
 Gorey     | Edward
 Brite     | Poppy Z.
 Brautigan | Richard
(4 rows)

In Example 4-48, only rows that do not match the second query are returned. Notice that the effective result of this is that only authors who do not have a book in the books table are returned. This is due to the INNER JOIN clause, which causes the second query to omit any authors whose author_id is not found in the books table.

While the use of these keywords in a single SQL query precludes the ability to use the LIMIT clause, this limitation can be circumvented by PostgreSQL's support for sub-queries. By grouping in parentheses each of the queries involved between a UNION, EXCEPT, or EXCEPT clause, the returned result sets from the sub-queries are compared, as demonstrated in Example 4-49.

Example 4-49. Comparing sub-query result sets

booktown=# 
(SELECT title FROM books ORDER BY title DESC LIMIT 7)

booktown-# 
    EXCEPT

booktown-# 
    (SELECT title FROM books ORDER BY title ASC LIMIT 11)

booktown-# 
    ORDER BY title DESC;

        title
----------------------
 The Velveteen Rabbit
 The Tell-Tale Heart
 The Shining
 The Cat in the Hat
(4 rows)

Notice that the query used in Example 4-49 creates a set from the books table that is constrained to the last seven rows and sorted alphabetically by title. The EXCEPT clause then removes from that data set the first eleven rows, sorted alphabetically in an ascending fashion. The result consists of the last four rows from the table, sorted from the bottom by the final ORDER BY clause on the new exception set.

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire