Database Reference
In-Depth Information
UNION
. Same as for UNION ALL, but duplicate rows are only
returned once. In other words, duplicate rows are removed.
INTERSECT
. Returns distinct rows from both queries. An intersec-
tion is a little like an inner join.
MINUS
. Returns one query less the other, a little like the outer part
of a left outer join where only distinct rows in the first query are
returned.
13.1.2
Using Composite Queries
In order to demonstrate a sensible use of composite queries, let's create a
view, removing all styles from genres in the GENRES view. Styles in the
GENRE table are numbered as GENRE_ID 1, 2, and 3; the GENRES
view will include only these rows.
CREATE VIEW GENRES AS
SELECT GENRE_ID AS ID, GENRE
FROM GENRE WHERE STYLE_ID IS NOT NULL;
The following query concatenates the GENRE table and GENRES
view. We are trying to retrieve duplicated rows. The resulting row count
includes all rows in the GENRE table and the GENRES view. The result-
ing duplicated rows can be clearly seen in Figure 13.1. The ORDER BY
clause is used to show duplications (see Chapter 6).
SELECT GENRE_ID, GENRE FROM GENRE
UNION ALL
SELECT * FROM GENRES
ORDER BY 1;
Now let's change the query in Figure 13.1 and remove the duplications
as in the following query using the UNION set operator instead of the
UNION ALL operator. The result is shown in Figure 13.2.
SELECT GENRE_ID, GENRE FROM GENRE
UNION
SELECT * FROM GENRES
ORDER BY 1;
