The difference between union and union all in mysql

Difference between UNION and UNION ALL set operators in MySQL: UNION returns unique rows, while UNION ALL returns all rows, including duplicate rows. UNION sorts the result set in ascending order, while UNION ALL does not sort. Select UNION to eliminate duplicate rows, and UNION ALL to retain duplicate rows.

UNION and UNION ALL: The difference between the two set operators in MySQL

In MySQL, UNION and UNION ALL is a powerful operator for combining data from two or more tables. However, there are important differences between them.

UNION

• Returns only the unique rows in the result set.
• Rows are sorted in ascending order before they are added to the result set.
• Eliminate duplicate rows, even if they are from different tables.

UNION ALL

• Returns all rows in the result set, including duplicate rows.
• Rows will not be sorted.
• Keep duplicate rows even if they come from different tables.

Choose which operator to use

Choose UNION or UNION ALL depending on your specific needs:

• If you need to eliminate duplicates OK, please use UNION.
• If you need to keep all rows, including duplicate rows, use UNION ALL.
• If you need to sort the results, use UNION.
• If ordering doesn't matter, use UNION ALL.

Example

Consider the following two tables:

Table A:

Student_ID	Student_Name
1	John
3	Mary

Table B:

##Student_IDStudent_Name3Mary5BobUse UNION operator:

<code class="sql">SELECT * FROM A UNION SELECT * FROM B;</code>

Result:

<code>| Student_ID | Student_Name |
|---|---|
| 1 | John |
| 3 | Mary |
| 5 | Bob |</code>

Use UNION ALL operator:

<code class="sql">SELECT * FROM A UNION ALL SELECT * FROM B;</code>

Result:

<code>| Student_ID | Student_Name |
|---|---|
| 1 | John |
| 3 | Mary |
| 3 | Mary |
| 5 | Bob |</code>

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