EnterpriseDB: Complex Selects Oracle Tips by Burleson Consulting

In the above section, we covered all of the basic parts of a query.  In this section, we will cover some of the less used, but just as important, complex SQL functionality.

The first up is the UNION keyword.  We could get into a deep philosophical discussion of the mathematics behind SQL, but it has been my experience that most people just aren't that interested.  So, I will use a new analogy.  I call it the pocket analogy.

Let's pretend that I am wearing pants.  That is something I do most times.  Now, let's further pretend that my pants have two pockets, one on my left and one on my right.

My left pocket, hypothetically, has 2 quarters ($0.25), 2 dimes ($0.10), and 3 pennies ($0.01).  My right pocket has 1 quarter, 3 dimes and 2 nickels ($0.05).  

Let's turn this into a couple of tables.  I'll create a left_table that represents my left pocket and a right_table that represents the right pocket.

To follow along with this example, run this code:

CREATE TABLE left_table (amount number);
CREATE TABLE right_table (amount number);
INSERT INTO left_table (amount) VALUES (.25);
INSERT INTO left_table (amount) VALUES (.25);
INSERT INTO left_table (amount) VALUES (.10);
INSERT INTO left_table (amount) VALUES (.10);
INSERT INTO left_table (amount) VALUES (.01);
INSERT INTO left_table (amount) VALUES (.01);
INSERT INTO left_table (amount) VALUES (.01);
INSERT INTO right_table (amount) VALUES (.25);
INSERT INTO right_table (amount) VALUES (.10);
INSERT INTO right_table (amount) VALUES (.10);
INSERT INTO right_table (amount) VALUES (.10);
INSERT INTO right_table (amount) VALUES (.05);
INSERT INTO right_table (amount) VALUES (.05);
COMMIT;

Ok, the UNION ALL of my left pocket and my right pocket would be represented as:

SELECT amount
  FROM left_table
UNION ALL
SELECT amount
  FROM right_table;

The output looks like:

edb=# SELECT amount
edb-#   FROM left_table
edb-# UNION ALL
edb-# SELECT amount
edb-#   FROM right_table; 

 amount
--------
   0.25
   0.25
   0.10
    0.10
   0.01
   0.01
   0.01
   0.25
   0.10
   0.10
   0.10
   0.05
   0.05

(13 rows)

edb=#

So a union all will take all of the rows in the first table and UNION them (or include them in the result set) with all of the rows in the second table.

I am going to run this again, but this time I will do a UNION instead of a UNION ALL:

SELECT amount
  FROM left_table
UNION
SELECT amount
  FROM right_table; 

edb=# SELECT amount
edb-#   FROM left_table
edb-# UNION
edb-# SELECT amount
edb-#   FROM right_table;

 amount
--------
   0.01
   0.05
   0.10
   0.25

(4 rows)

edb=#

This did exactly the same thing as UNION ALL but UNION adds an additional step.  UNION sorts the output and removes duplicate rows. 

If you want to know about ALL of the rows in a UNION, use UNION ALL.  If you are interested in only distinct rows, use UNION.  The distinct part of the UNION applies to only those columns in your select list.  It also always applies to all columns in your select list.

Next up is the EXCEPT keyword.  EXCEPT will return a result that is the difference between two result sets.  Specifically, it will return those rows in the upper table that do not exist in the lower table.  If you are coming from an Oracle environment, EXCEPT is the EnterpriseDB equivalent of MINUS.

If we run this query:

SELECT amount
  FROM left_table
EXCEPT ALL
SELECT amount
  FROM right_table; 

edb=# SELECT amount
edb-#   FROM left_table
edb-# EXCEPT ALL
edb-# SELECT amount
edb-#   FROM right_table;

 amount
--------
   0.01
   0.01
   0.01
   0.25

(4 rows)

edb=#

No 0.01 records exist in the second query and only a single 0.25 exists so that is the result that is returned to us.  If we take off the ALL keyword, the SQL engine removes duplicates like in the UNION but it considers a duplicate to be any row that has a duplicate in the second parent table.

So if we run this query:

SELECT amount
  FROM left_table
EXCEPT
SELECT amount
  FROM right_table;

edb=# SELECT amount
edb-#   FROM left_table
edb-# EXCEPT
edb-# SELECT amount
edb-#   FROM right_table;

 amount
--------
   0.01

(1 row)

edb=#

If the lack of an ALL removes duplicates, you might wonder why the 0.25 went away.  The upper query had a 0.25 so in the second query, 0.25 was a duplicate.  The only value that did not exist in the lower query, which did exist in the upper query, was the 0.01.

Let's turn the query around and put the right table at the top and the left table on the bottom:

SELECT amount
  FROM right_table
EXCEPT
SELECT amount
  FROM left_table;

edb=# SELECT amount
edb-#   FROM right_table
edb-# EXCEPT
edb-# SELECT amount
edb-#   FROM left_table;

 amount
--------
   0.05

(1 row)

edb=#

Can you explain why the 0.05 is all that was returned?  The left_table did not contain any 0.05 so that's what was returned.  Try adding the ALL keyword to see what impact that has on the result set.

The last complex query that I will be covering is the INTERSECT keyword.  If you can grasp UNION and EXCEPT, INTERSECT will be a breeze.

INTERSECT returns all rows that are in both the upper query and the lower query.  ALL works in INTERSECT just as it does in UNION; it sorts the results and allows duplicates.

SELECT amount
  FROM left_table
INTERSECT ALL
SELECT amount
  FROM right_table;

edb=# SELECT amount
edb-#   FROM left_table
edb-# INTERSECT ALL
edb-# SELECT amount
edb-#   FROM right_table;

 amount
--------
   0.10
  0.10
  0.25

(3 rows)

edb=#

Both queries contain 0.10 and 0.25 and they both contain at least two 0.10 and one 0.25.  Neither contains a 0.01 or a 0.05. 

If we remove the ALL:

SELECT amount
  FROM left_table
INTERSECT
SELECT amount
  FROM right_table;

edb=# SELECT amount
edb-#   FROM left_table
edb-# INTERSECT
edb-# SELECT amount
edb-#   FROM right_table;

 amount
--------
   0.10
   0.25

(2 rows)

edb=#

The duplicates were removed from the result set.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.