Usage of union and union all in mysql

UNION and UNION ALL in MySQL are used to merge result sets. The difference lies in the way duplicate rows are handled. UNION removes duplicate rows, while UNION ALL retains all rows. UNION syntax: SELECT column 1, column 2, ...FROM table 1 UNION SELECT column 1, column 2, ...FROM table 2; UNION ALL syntax: SELECT column 1, column 2, ...FROM table 1 UNION ALL SELECT column 1, column 2, ...FROM table 2;

##Usage of UNION and UNION ALL in MySQL

In MySQL, UNION and UNION ALL are operators used to merge the result sets of two or more SELECT statements. Their main difference is how duplicate rows are handled.

UNION

The UNION operator automatically removes duplicate rows when merging result sets. It only returns unique rows from each result set.

UNION ALL

The UNION ALL operator will retain all rows, including duplicate rows, when merging the result set. It simply joins together all rows from two or more result sets.

Usage

The syntax of UNION and UNION ALL is as follows:

<code class="sql">SELECT 列1, 列2, ...
FROM 表1
UNION
SELECT 列1, 列2, ...
FROM 表2;

SELECT 列1, 列2, ...
FROM 表1
UNION ALL
SELECT 列1, 列2, ...
FROM 表2;</code>

Example

Suppose we have Two tables

Students and Teachers:

<code class="sql">CREATE TABLE Students (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE Teachers (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);</code>

Now, we can use UNION and UNION ALL to combine the

name columns of these two tables :

<code class="sql">-- 使用 UNION 移除重复行
SELECT name
FROM Students
UNION
SELECT name
FROM Teachers;

-- 使用 UNION ALL 保留所有行，包括重复行
SELECT name
FROM Students
UNION ALL
SELECT name
FROM Teachers;</code>

Note

The result sets of UNION and UNION ALL must have the same number of columns and the data types of the corresponding columns are compatible.
1. When using UNION ALL, care should be taken to avoid contaminating the results with duplicate data.
2. UNION has better performance because it does not require deduplication before merging.

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