Database Reference
In-Depth Information
▼
Input
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION ALL
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001,1002);
▼
Output
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
| 1003 | FC | 2.50 |
| 1002 | FU1 | 3.42 |
| 1003 | SLING | 4.49 |
| 1003 | TNT1 | 2.50 |
| 1001 | ANV01 | 5.99 |
| 1001 | ANV02 | 9.99 |
| 1001 | ANV03 | 14.99 |
| 1002 | FU1 | 3.42 |
| 1002 | OL1 | 8.99 |
+---------+---------+------------+
▼
Analysis
Using
UNION ALL
, MariaDB does not eliminate duplicates. Therefore, the pre-
ceding example returns nine rows, one of them occurring twice.
Tip
UNION
Versus
WHERE
The beginning of this chapter said that
UNION
almost always
accomplishes the same thing as multiple
WHERE
conditions.
UNION ALL
is the form
of
UNION
that accomplishes what cannot be done with
WHERE
clauses. If you do, in
fact, want all occurrences of matches for every condition (including duplicates), you must
use
UNION ALL
and not
WHERE
.
SELECT
statement output is sorted using the
ORDER BY
clause. When com-
bining queries with a
UNION
, only one
ORDER BY
clause may be used, and
it must occur after the final
SELECT
statement. There is little point in sorting
part of a result set one way and part another way, and so multiple
ORDER BY
clauses are not allowed.
