Shulou(Shulou.com)06/01 Report--

Overview

UNION

Join dataset keyword, you can splice two query result sets into one, which will filter out the same records

UNION ALL

Join dataset keyword, you can splice two query result sets into one without filtering out the same records

Today, when I received a request, I used UNION to query and found that if the two queries could not be sorted successfully after using ORDER BY respectively, after a lot of trouble, record

Table structure and data

Create tables CREATE TABLE test_user (ID int (11) NOT NULL AUTO_INCREMENT, USER_ID int (11) DEFAULT NULL COMMENT 'user account', USER_NAME varchar (25) DEFAULT NULL COMMENT 'user name', AGE int (5) DEFAULT NULL COMMENT 'age', COMMENT varchar (25) DEFAULT NULL COMMENT 'profile', PRIMARY KEY (ID)) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 -- data insertion statements INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('1x, '111,' happy rookie','18, 'very happy today'); INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('2, 22, sad rookie,'21, 'sad today') INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('333,' 333, 'serious rookie','30', 'very serious today'); INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('418,' 444, 'happy rookie','18', 'very happy today') INSERT INTO test_user (ID, USER_ID, USER_NAME, AGE, COMMENT) VALUES ('555', 'serious rookie','21', 'very serious today')

The default table data is displayed as follows

Running result analysis

-- query 1SELECT * FROM test_user uORDER BY AGE

Result set 1

-- query 2Mel-use UNION (SELECT * FROM test_user u ORDER BY AGE) UNION (SELECT * FROM test_user u ORDER BY AGE);-- query 3Mel-use 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

Because UNION merges the same records (with the same effect as DISTINCT), there are only 5 records shown here

Result set 3: using UNION ALL

If you need to use UNION ALL and sort it, look it up as a subquery

-- query 4Mel-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)) aORDER BY AGE

Result set 4

Improve

After some search-related experience, I found that I had gilded the lily to complete the sorting method without using a subquery:

-- query 5 FROM test_user u UNION ALL SELECT-the first query does not use sorting. If it is used, it will report an error without parentheses (which is why I wanted to use subqueries instead of thinking about this way) SELECT * FROM test_user u UNION ALL SELECT * FROM test_user uORDER BY AGE

The result set that runs is the same as result set 4, and the result is no longer glued out here.

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:

ORDER BY query them again as subqueries (it is also recommended to use method 2, which is too unconcise) instead of sorting and not separated by parentheses in the first result set, use ORDER BY after the second result set

Reference link

The use of UNION and UNION ALL in cnblogs:MySQL

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.