Database Tutorial

Set Operators

Learning Outcomes

• Learn how to perform a UNION 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

The UNION operator matches every row in one table against every row in another based on the select list of shared columns. It is a filtered join that returns the intersection plus the symmetric difference of the top and bottom queries. In its most basic approach an UNION operator acts like a nested loop where you match keys from the outer loop against keys from the inner loop and add non-matches as they occur.

Another way to describe an UNION operation is as a matching and reduction of two rows of data into one row of data where they share the same organization, viz-a-viz 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 A intersects (∩) B, plus everything in A not found in B and everything in B not found in A. The intersection works by sorting matched rows of data and eliminating any duplicate copies.

The following syntax lets you create a UNION of two queries. The query returns every row in set A that doesn’t match any row in set B, every row in set B that doesn’t match any row in set A, and one copy of all rows that match between set A and B. 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
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 one copy of all rows from set A and set B. It is effected with the overhead of a sort operation and should only be used when you can’t guarantee the unique between the two query return sets.

ORDINAL                            ORDINAL
ENGLISH                             FRENCH
   ID # ORDINAL_ENGLISH_TEXT          ID # ORDINAL_FRENCH_TEXT
------- -------------------------- ------- --------------------------
      1 One                              1 Un
      2 Two
      3 Three                            3 Trois
      4 Four                             4 Quatre
      5 Five                             5 Cinq
      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.