Database Tutorial

Set Operators

Learning Outcomes

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

MINUS

The MINUS operator finds matches in the second query that it removes from the first query. The only values returned by a MINUS operator are the non-matches from the first query.

Another way to describe a MINUS set operator is to draw a correlation to a LEFT JOIN because it acts like a TOP JOIN where the topmost query replaces the left table in a join. The formula uses a ∀ to indicate given any sets A and B, then A intersects (∩) B plus all rows in A not found in B. The Venn diagram and set notation depicts that definition.

The following syntax lets you subtract set B from set A with a MINUS operator. The first query returns everything in set A that isn’t found in set B. It acts like an anti-join where you take the LEFT JOIN minus INNER JOIN to find the relative complement of the table on the right. Moreover, it returns the rows from the top query not found in the bottom query, which is likewise the relative complement of the bottom set.

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 #"

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
MINUS
SELECT   ordinal_english_id
,        ordinal_english_text
,        ordinal_french_id
,        ordinal_french_text
FROM     ordinal_english INNER JOIN ordinal_french ON ordinal_english_id = ordinal_french_id
ORDER BY ordinal_english_id;

This returns the right relative complement because the INNER JOIN component (the bottom query) is removed from the LEFT JOIN query. It returns the following data set:

ORDINAL                            ORDINAL
ENGLISH                             FRENCH
   ID # ORDINAL_ENGLISH_TEXT          ID # ORDINAL_FRENCH_TEXT
------- -------------------------- ------- --------------------------
      2 Two

You can also use the older Oracle Proprietary syntax for an outer join to mimic this behavior. The MINUS operator returns only rows from the top query not found in the bottom query, like:

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
MINUS
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;

Some think this would return the symmetric difference of the sets because the LEFT JOIN and RIGHT JOIN only share the intersection. They reason that the symmetric difference is the combination of the left and right relative components.

They’re wrong. The top most query contains the intersection of the two sets and the right relative complement. When you subtract the two queries, the only rows they share are the intersection. Therefore, the topmost query subtracts the intersection from the LEFT JOIN (or intersection). That leaves only the right relative complement because you don’t add rows from the bottom query that aren’t found in the topmost query with a MINUS set operator.

It returns the same data set as the preceding example, or the following data set:

ORDINAL                            ORDINAL
ENGLISH                             FRENCH
   ID # ORDINAL_ENGLISH_TEXT          ID # ORDINAL_FRENCH_TEXT
------- -------------------------- ------- --------------------------
      2 Two