Database Tutorial

Set Operators

Learning Outcomes

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

INTERSECT

The INTERSECT 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. In its most basic approach an INTERSECT operator acts like a nested loop where you match keys from the outer loop against keys from the inner loop.

Another way to describe an INTERSECT operation is as a matching of two rows 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. The intersection works by matching a row of data in one query with a row of data in another query based the entire select list of the query.

The following syntax lets you create an intersection two queries. The first query returns everything in set A that matches set B, and eliminates the symmetric difference, or the relative complements found in both the LEFT JOIN and RIGHT JOIN. It uses ANSI SQL:92 syntax.

1
2
3
4
5
6
7
8
9
10

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

This returns only rows with only the two selected columns, where they are the result of a join between the two queries. The only join between a LEFT JOIN and a RIGHT JOIN is the INNER JOIN, which is synonymous with the INTERSECT between the two queries.

ORDINAL_ENGLISH_ID ORDINAL_ENGLISH_TEXT       ORDINAL_FRENCH_TEXT
------------------ -------------------------- --------------------------
                 1 One                        Un
                 3 Three                      Trois
                 4 Four                       Quatre
                 5 Five                       Cinq
                 6 Six                        Six

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