How to use union in mysql

In MySQL, the UNION operator merges result sets from multiple tables or subqueries, returning only unique result rows. It is primarily used to merge disjoint result sets and allows retaining duplicate rows via UNION ALL.

UNION usage in MySQL

UNION operator is used in MySQL to merge from Result set of multiple tables or subqueries. It only returns unique result rows without repeating the same rows.

Syntax:

<code>SELECT 列名1, 列名2, ...
FROM 表名1
UNION
SELECT 列名1, 列名2, ...
FROM 表名2
...
[UNION ALL]</code>

Parameters:

• Column name: to be merged Column name. They must have the same order and data type.
• Table name: The table or subquery to merge the result set.
• UNION ALL (optional): Keep duplicate rows.

Usage:

UNION is mainly used to merge disjoint result sets from different tables or subqueries. It only keeps unique result rows.

For example, to merge the result sets of tables customers and orders and display each customer's information and order information, you can use the following query:

<code>SELECT *
FROM customers
UNION
SELECT *
FROM orders;</code>

If you want to keep duplicate rows, you can use UNION ALL:

<code>SELECT *
FROM customers
UNION ALL
SELECT *
FROM orders;</code>

Note:

• The UNION operator only merges rows with the same Number of columns and data type of the result set.
• If the column names are different, you need to use aliases or wildcards (*) to match them.
• If columns have different data types, you need to use a conversion function (such as CAST() or CONVERT()) to ensure they are compatible. The
• UNION operator does not consider sort order unless an ORDER BY clause is used.

The above is the detailed content of How to use union in mysql. For more information, please follow other related articles on the PHP Chinese website!