Database Reference
In-Depth Information
.
.
.
The first three records from each table are shown in the output. Because the
resulting records from both tables are appended together in the output, it is
important that the columns are specified in the same order and that the data
types of the columns are compatible.
UNION ALL
merges the results of the two
SELECT
statements regardless of any duplicate records appearing in both
SELECT
statements. If only
UNION
was used, any duplicate records, based on all the
specified columns, would be eliminated.
The
INTERSECT
operator determines any identical records that are returned by
two
SELECT
statements. For example, if one wanted to know what items were
purchased prior to 2013 as well as later, the SQL query using the
INTERSECT
operator would be this.
SELECT product_id
FROM orders_arch
INTERSECT
SELECT product_id
FROM orders_recent
product_id
22
30
31
.
.
.
It is important to note that the intersection only returns a
product_id
if it
appears in both tables and returns exactly one instance of such a
product_id
.
Thus, only a list of distinct product IDs is returned by the query.
To count the number of products that were ordered prior to 2013 but not after that
point in time, the
EXCEPT
operator can be used to exclude the product IDs in the
orders_recent
table from the product IDs in the
orders_arch
table, as shown
in the following SQL query.
