Java Reference
In-Depth Information
Set operators allow you to combine
ResultSets
returned by different queries into a single
ResultSet
. These are the main set operators:
UNION
returns the combined results of two queries.
INTERSECT
returns only the rows found by both queries.
EXCEPT
returns the rows from the first query that are not present in the second.
Caution
The
INTERSECT
and
EXCEPT
operators are not supported by all SQL dialects.
UNION
, together with the variant
UNION ALL
, works on most SQL versions.
Using UNION and UNION ALL
UNION ALL
returns the results of two queries.
UNION
does the same thing, but it removes duplicate
results. Let's say you wanted to invite all the New York and New Jersey Corleones to a party and
introduce them to Kay Adams. You could use a
UNION
to combine the two queries into one guest list.
Here's an example:
SELECT *
FROM Contact_Info
WHERE Last_Name = 'Corleone' AND (City = 'New York' OR
State = 'NJ')
UNION
SELECT *
FROM contact_info
WHERE first_name = 'Kay';
UNION
, used by itself, returns the results of the two queries without any repetitions.
UNION ALL
, on the
other hand, returns the results of the two queries including all repetitions.
Using INTERSECT and EXCEPT
The
INTERSECT
and
EXCEPT
operators adhere to the same syntax as the
UNION
operator. You
should check with the documentation for the DBMS you are using to ensure that these operators are
supported before committing to using one of them.
Escape Sequences
Escape sequences
are of valuable use in situations where a character has a particular meaning in
SQL, and you want to use that character in a different way. A typical example is the use of the
apostrophe (').
A problem that arises when handling names is the use of the apostrophe in names of Irish origin. Since
the apostrophe is, in effect, a single quote ('), SQL reads it as a
CHAR
or
VARCHAR
terminator and
throws a SQL error when it tries to handle the rest of the string. This problem also arises fairly
frequently in normal free-form text.
