MySQL Union and UNION ALL syntax and usage

1.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 UNION [all]select column,... From Table2 ...

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.

The difference between the 1.1.UNION and UNION all

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.

2.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.

2.1. Using UNION queries

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

Select Aid,title from article UNION SELECT bid,title from blog

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

2.2.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.

3. Use the UNION all query

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

Select Aid,title from article UNION all SELECT bid,title from blog

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

Obviously, when you use UNION all, you simply combine the queries together without judging whether the data is duplicated. Therefore, you should use UNION all to improve query efficiency when you determine that there are no duplicate data in the query results or that you do not need to remove duplicate data.

4.MySQL UNION ORDER by

The use of ORDER by and LIMIT in MySQL UNION is something that requires special attention, so make a separate list.

Here are two original data tables for the example test:

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 ...

Use ORDER by in a 4.1UNION clause

clause, the results of the SELECT clause are sorted first, and then the results of these clause queries are set.

With ORDER by in the clause, the entire clause needs to be parenthesized () and must be used in conjunction with the LIMIT because of the priority problem:

(SELECT aid,title from article ORDER by aid DESC LIMIT 10) UNION All (SELECT bid,title from blog ORDER by bid DESC LIMIT 10)

The results of the returned query are as follows:

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

Use ORDER by in 4.2UNION whole sentence

If you want to use order by or limit to classify or limit all UNION results, you should add parentheses to a single SELECT statement and place ORDER by or limit behind the last one.

(SELECT aid,title from article) UNION All (SELECT bid,title from blog) ORDER by aid DESC

The results of the returned query are as follows:

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

Using order by in the entire sentence, removing the parentheses in the SELECT clause, although the result is the same, it is recommended that you do not omit parentheses for clear statements.

4.3 UNION ORDER by applies to aliases

If a field alias is used in SELECT, the Oeder by must refer to the alias:

(SELECT A as B from table) UNION (SELECT ...) ORDER by B

5.MySQL UNION LIMIT

Use limit in MySQL UNION to limit the number of records returned, and if you restrict the SELECT clause, you need to add parentheses to the Select:

(SELECT aid,title from article LIMIT 2) UNION All (SELECT bid,title from blog LIMIT 2)

The SQL returns two records for the SELECT statement, and if no parentheses are added, the last LIMIT 2 will act on the entire UNION statement and return a total of 2 records.

Similar to ORDER by, it is recommended that each SELECT statement be enclosed in parentheses to make the statement clearer when it is necessary to limit the results of the entire UNION:

(SELECT aid,title from article) UNION All (SELECT bid,title from blog) LIMIT 2

As can be seen, the LIMIT and ORDER by are often used in conjunction with each other in the UNION in the same way.

MySQL Union and UNION ALL syntax and usage