Database Tutorial

Set Operators

Learning Outcomes

• Learn how to perform a UNION ALL of two queries.

Lesson Materials

You can joins rows between two queries by several techniques. They all work by comparing all the columns returned by the top query with all the columns returned by the bottom query. Moreover, they apply the following four rules:

Rule(s):

1. The top query SELECT-list must mirror the bottom query.
2. The top query column names must match the bottom query column names.
3. The top query must have the same number of columns as the bottom query.
4. The top query must match the positional ordering of the bottom query.

UNION ALL

The UNION ALL operator matches every row in one table against every row in another based on the select list of shared columns. It is an unfiltered join that returns two copies of the intersection plus the symmetric difference of the top and bottom queries. The UNION ALL set operator should only be used when the set of rows returned from the top query is distinct from the set of rows returned from the bottom query.

Another way to describe an UNION operation is as a listing of all rows returned by each query without any attempt to match and reduce to a unique set. As with other set operators, they must have a common column name, positional order, number of columns, and data type. The formula uses a ∀ to indicate given any sets A and B, then 2 times the quantity of A intersect (∩) B, plus everything in A not found in B and everything in B not found in A. Two copies are returned in this case because both a LEFT JOIN and a RIGHT JOIN return a copy of the INNER JOIN.

The following syntax lets you create a UNION ALL of two queries. The query returns every row returned by both queries. It uses ANSI SQL:92 syntax.

You can format the output with the following SQL*Plus command in an Oracle database but it doesn’t work in the others.

1
2

COLUMN ORDINAL_ENGLISH_ID FORMAT 99 HEADING "ORDINAL|ENGLISH|ID #"
COLUMN ORDINAL_FRENCH_ID  FORMAT 99 HEADING "ORDINAL|FRENCH|ID #"

The query works in Oracle, MySQL, or SQL Server but the output only formats in Oracle.

1
2
3
4
5
6
7
8
9
10
11
12

SELECT   ordinal_english_id
,        ordinal_english_text
,        ordinal_french_id
,        ordinal_french_text
FROM     ordinal_english LEFT JOIN ordinal_french ON ordinal_english_id = ordinal_french_id
UNION ALL
SELECT   ordinal_english_id
,        ordinal_english_text
,        ordinal_french_id
,        ordinal_french_text
FROM     ordinal_english RIGHT JOIN ordinal_french ON ordinal_english_id = ordinal_french_id
ORDER BY ordinal_english_id;

This returns two copies of all rows from set A and set B where they’re duplicates and one row where they’re not duplicates. You should only use this type of set operator when you can guarantee the uniqueness between the two query return sets.

ORDINAL                            ORDINAL
ENGLISH                             FRENCH
   ID # ORDINAL_ENGLISH_TEXT          ID # ORDINAL_FRENCH_TEXT
------- -------------------------- ------- --------------------------
      1 One                              1 Un
      1 One                              1 Un
      2 Two
      3 Three                            3 Trois
      3 Three                            3 Trois
      4 Four                             4 Quatre
      4 Four                             4 Quatre
      5 Five                             5 Cinq
      5 Five                             5 Cinq
      6 Six                              6 Six
      6 Six                              6 Six
                                         7 Sept
                                         8 Huit

You can also use the older Oracle Proprietary syntax for an outer join to mimic this behavior.