Detailed explanation of the failure of MySQL to use UNION to connect two queries

Overview

UNION

The connection data set keyword can concatenate two query result sets into one, filtering out identical records

UNION ALL

The connection dataset keyword can concatenate two query result sets into one without filtering out identical records.

Today, when I received a request, I used UNION to query and found that if two queries were spliced ​​using ORDER BY respectively, they could not be sorted successfully. After a lot of trouble, I recorded it.

Table structure and data

-- Create table CREATE TABLE test_user (
 ID int(11) NOT NULL AUTO_INCREMENT,
 USER_ID int(11) DEFAULT NULL COMMENT 'User account',
 USER_NAME varchar(255) DEFAULT NULL COMMENT 'User name',
 AGE int(5) DEFAULT NULL COMMENT 'Age',
 COMMENT varchar(255) DEFAULT NULL COMMENT 'Introduction',
 PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
 
-- Data insertion statement INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('1', '111', 'Happy rookie', '18', 'Very happy today');
INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('2', '222', 'Sad rookie', '21', 'Very sad today');
INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('3', '333', 'Serious rookie', '30', 'Very serious today');
INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('4', '444', 'Happy rookie', '18', 'I am very happy today');
INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('5', '555', 'Serious rookie', '21', 'Today is very serious');

The default table data is displayed as follows

Run results analysis

-- Query 1
SELECT
 *
FROM
 test_user u
ORDER BY AGE

Result Set 1

-- Query 2
-- Using UNION
(
 SELECT
  *
 FROM
  test_user u
 ORDER BY AGE
)
UNION
(
 SELECT
  *
 FROM
  test_user u
 ORDER BY AGE
);
 
-- Query 3
-- Using UNION ALL
(
 SELECT
  *
 FROM
  test_user u
 ORDER BY AGE
)
UNION ALL
(
 SELECT
  *
 FROM
  test_user u
 ORDER BY AGE
)

Result set 2: Using UNION

Since UNION combines identical records (which has the same effect as DISTINCT), only five records are displayed here.

Result set 3: Using UNION ALL

If you need to use UNION ALL and sort, you need to query it as a subquery.

-- Query 4
-- Use UNION ALL as a subquery and sort SELECT
 *
FROM
 (
 (
 SELECT
 *
 FROM
 test_user u
 ORDER BY
 AGE
 )
 UNION ALL
 (
 SELECT
 *
 FROM
 test_user u
 ORDER BY
 AGE
 )
 )
ORDER BY
 AGE;

Result Set 4

improve

After searching for relevant experience, I found that I had done something unnecessary. It turns out that the sorting can be done without using a subquery:

-- Query 5
-- The first query does not use sorting. If it does, an error will be reported without parentheses (this is why I wanted to use a subquery before and did not think of this method)
SELECT
 *
FROM
 test_user u
 
UNION ALL
 
SELECT
 *
FROM
 test_user u
ORDER BY
 AGE

The result set is the same as result set 4, so the result will not be pasted here.

in conclusion

When we use the UNION (or UNION ALL) statement, if the two result sets of UNION are sorted separately and then spliced, their ORDER BY is invalid. If we want to sort, there are two ways:

1. Use them as subqueries and query them again in ORDER BY (method 2 is still recommended, as subqueries are not concise enough)
2. Do not use sorting in the first result set, and do not use parentheses to separate the results, and use ORDER BY after the second result set

Reference Links

cnblogs: Use of UNION and UNION ALL in MySQL

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.