The difference between union and union all in sql

UNION only returns unique rows, while UNION ALL contains duplicate rows; UNION deduplicates rows before merging, but UNION ALL does not deduplicate rows.

The difference between UNION and UNION ALL in SQL

UNION and UNION ALL are both used to merge two or SQL operators for rows in multiple tables. However, there are fundamental differences in how they handle duplicate rows.

UNION

• Return only unique (non-duplicate) rows in the result table.
• It will deduplicate the rows in each table before merging the tables.
• If duplicate rows exist in any table, they will appear only once in the result table.

UNION ALL

• Returns all rows, including duplicate rows.
• It does not deduplicate rows in individual tables.
• If duplicate rows exist in any table, they will appear multiple times in the result table.

Example

Suppose there are two tables T1 and T2, as shown below:

<code>T1:
+----+----+
| ID | Name |
+----+----+
| 1  | John |
| 2  | Susan |
| 3  | Mary |
+----+----+

T2:
+----+----+
| ID | Name |
+----+----+
| 2  | Susan |
| 4  | Bob |
+----+----+</code>

If you use the UNION operator to merge these two Table:

<code>SELECT * FROM T1 UNION SELECT * FROM T2;</code>

The result will be:

<code>+----+----+
| ID | Name |
+----+----+
| 1  | John |
| 2  | Susan |
| 3  | Mary |
| 4  | Bob |
+----+----+</code>

where the duplicate row (ID = 2, Name = Susan) has been removed.

If you use the UNION ALL operator to merge these two tables:

<code>SELECT * FROM T1 UNION ALL SELECT * FROM T2;</code>

The result will be:

<code>+----+----+
| ID | Name |
+----+----+
| 1  | John |
| 2  | Susan |
| 2  | Susan |
| 3  | Mary |
| 4  | Bob |
+----+----+</code>

where the duplicate rows are already included in the result.

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