MariaDB Crash Course - Ben Forta [50]
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 | OL1 | 8.99 |
+---------+---------+------------+
Analysis
The preceding statements are made up of both of the previous SELECT statements separated by the UNION keyword. UNION instructs MariaDB to execute both SELECT statements and combine the output into a single query result set.
As a point of reference, here is the same query using multiple WHERE clauses instead of a UNION:
Input
SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
OR vend_id IN (1001,1002);
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.
UNION Rules
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 statements, 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.
Including or Eliminating Duplicate Rows
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:
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 preceding 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.
* * *
Sorting Combined Query Results
SELECT statement output is sorted using the ORDER BY clause. When combining queries with a UNION, only one ORDER BY clause may be used, and it must occur after the final SELECT statement. There