Database Reference
In-Depth Information
In this simple example, the
UNION
might actually be more complicated than
using a
WHERE
clause. But with more complex filtering conditions, or if the
data is being retrieved from multiple tables (and not just a single table), the
UNION
could have made the process much simpler.
As you can see, unions are easy to use. But a few rules govern exactly which
can be combined:
A
UNION
must be comprised of two or more
SELECT
statements, each
separated by the keyword
UNION
(so, if combining four
SELECT
state-
ments, three
UNION
keywords would be used).
■
Each query in a
UNION
must contain the same columns, expressions, or
aggregate functions (although columns need not be listed in the same
order).
■
Column datatypes must be compatible: They need not be the exact
same type, but they must be of a type that MariaDB can implicitly
convert (for example, different numeric types or different date types).
■
Aside from these basic rules and restrictions, unions can be used for any data
retrieval tasks.
Go back to the preceding section titled “Using
UNION
” and look at the sample
SELECT
statements used. Notice that when executed individually, the first
SELECT
statement returns four rows, and the second
SELECT
statement returns
five rows. However, when the two
SELECT
statements are combined with a
UNION
, only eight rows are returned, not nine.
The
UNION
automatically removes any duplicate rows from the query result set
(in other words, it behaves just as multiple
WHERE
clause conditions in a single
SELECT
would). Because vendor
1002
creates a product that costs less than
5
,
that row was returned by both
SELECT
statements. When the
UNION
was used,
the duplicate row was eliminated.
This is the default behavior of
UNION
, but you can change this if you want.
If you do, in fact, want all occurrences of all matches returned, you can use
UNION ALL
instead of
UNION
.
Look at the following example:
