MySQL Union and UNION ALL syntax and usage

MySQL UNION Syntax

MySQL UNION is used to combine the results from multiple SELECT statements into a single result set. The syntax is:

SELECT column,... From table1  

In multiple SELECT statements, the corresponding column should have the same field property, and the field name used in the first SELECT statement is also used for the field name of the result.

Union and UNION ALL differences

When you use union, MySQL deletes the duplicate records in the result set, and with union ALL, MySQL returns all records and is more efficient than union.

MySQL UNION Usage Example

UNION is commonly used for two or more table queries with similar data, such as different data classification tables, or data history tables. Here are two original data tables for the example:

Article Article table:

Aid	title	content
1	Article 1	Article 1 body content ...
2	Article 2	Article 2 body content ...
3	Article 3	Article 3 body content ...

Blog Log table:

Bid	title	content
1	Log 1	Log 1 body content ...
2	Article 2	Article 2 body content ...
3	Log 3	Log 3 body content ...

In the above two table data, the aid=2 data record is the same as the bid=2 data record.

Using UNION queries

Query the article ID number and title in both tables, and remove duplicate records:

The results of the returned query are as follows:

Aid	title
1	Article 1
2	Article 2
3	Article 3
1	Log 1
3	Log 3

UNION Query Result description

1. Duplicate records are records that are completely duplicated in each field in the query, as in the previous example, if the title is the same but the ID number does not count as a different record.
2. The field name used in the first SELECT statement is also used for the field name of the result, such as the aid for the previous example.
3. Each SELECT statement field name can be different, but the field properties must be the same.

Use UNION all query

Query the article ID number and title in both tables and return all records:

The results of the returned query are as follows:

Aid	title
1	Article 1
2	Article 2
3	Article 3
1	Log 1
2	Article 2
3	Log 3