Example of UNION collation in MySQL combined query

As you can see, it is very easy to use. But there are a few rules to be aware of when doing a merge.

1.UNION must be composed of two or more SELECT statements, separated by the keyword UNION (therefore, if 4 SELECT statements are combined, 3 UNION keywords will be used).

2. Each query in a UNION must contain the same columns, expressions, or aggregate functions (although the columns do not need to be listed in the same order).

3. Column data types must be compatible: the types do not have to be exactly the same, but they must be types that the DBMS can convert implicitly (for example, different numeric types or different date types). If these basic rules or restrictions are followed, you can use it for any data retrieval task.

##Include or cancel duplicate rows

We noticed that when executing separately, the first The first SELECT statement returns 4 rows, and the second SELECT statement returns 5 rows. But after combining the two SELECT statements with UNION, only 8 rows were returned instead of 9.

UNION automatically removes duplicate rows from the query result set (in other words, it behaves like multiple WHERE clause conditions in a single SELECT statement). Because supplier 1002 also produces an item with a price lower than 5 , both SELECT statements return that row. When using UNION, duplicate rows are automatically suppressed.

This is the default behavior of UNION, but it can be changed if desired. In fact, if you want to return all matching rows, use UNION ALL instead of UNION .

Please see 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:

Analysis: Use UNION ALL, MySQL does not cancel duplicate rows. So the example here returns 9 rows, one of which appears twice.

UNION AND WHERE UNION almost always accomplishes the same job as multiple WHERE conditions. UNION ALL is a form of UNION, which completes the work that the WHERE clause cannot. If you really need all matching rows for each condition to appear (including duplicate rows), you must use UNION ALL instead of WHERE.

Sort the combined query results

The output of the SELECT statement is sorted using the ORDER BY clause. When combining queries with UNION, only one ORDER BY clause can be used, and it must appear after the last SELECT statement. There is no situation where one part of a result set can be sorted one way and another part another way, so multiple ORDER BY clauses are not allowed.

The following example sorts the results returned by the previous UNION:

Input:

select vend_id,prod_id,prod_price from products where prod_price <= 5 union select vend_id,prod_id,prod_price from products where vend_id in (1001,1002) order by vend_id,prod_price;

Output:

Analysis: This UNION uses an ORDER BY clause after the last SELECT statement. Although the ORDER BY clause may appear to be an integral part of only the last SELECT statement, MySQL will actually use it to order all results returned by all SELECT statements.

Combine different tables. To make the expression simpler, the combined queries in the examples in this chapter all use the same table. But the combined query using UNION can apply to different tables.

The above is the detailed content of Example of UNION collation in MySQL combined query. For more information, please follow other related articles on the PHP Chinese website!