Shulou(Shulou.com)05/31 Report--

Editor to share with you how to use the UNION keyword in SQL, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

The UNION keyword in SQL will be implemented to display the results in multiple query conditions.

UNION

The Chinese meaning of    UNION is union, that is, the result of merging two or more SELECT statements. The tips are as follows:

Each SELECT statement within the UNION must have the same number of columns.

Columns must have similar data types.

The columns in each SELECT statement must be in the same order.

The same number of columns

As shown in the following figure, use the data introduced in the previous chapter as the demonstration data

Set up table s_user and table s_user_1 respectively

CREATE TABLE `s_ user` (`userid` int (11) NOT NULL AUTO_INCREMENT, `user_ name` varchar (255) DEFAULT NULL, `name` varchar (255) DEFAULT NULL, `pass_ word` varchar (255) DEFAULT NULL, `salt` varchar (255) DEFAULT NULL, `state` varchar (255) DEFAULT NULL, `create_ time` datetime DEFAULT NULL, PRIMARY KEY (`userid`) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

The test data are as follows:

INSERT INTO `test`.`s _ user` (`userid`, `test`name`, `name`, `pass_ word`, `salt`, `state`, `create_ time`) VALUES (1, 'millet', 'millet', '123123,' 123,'1, '2022-02-10, 1919, 4814) VALUES INSERT INTO `test`.`s _ user` (`userid`, `user_ name`, `name`, `pass_ word`, `salt`, `state`, `create_ time`) VALUES (2, 'Xiaobai', 'Xiaobai', '123123', '123','1', '2022-02-10 1919 Suzhou 4843') INSERT INTO `test`.`s _ user_ 1` (`userid`, `user_ name`, `name`, `pass_ word`, `salt`, `state`, `create_ time`) VALUES (1, 'Xiao Hong', 'Xiao Hong', '123123', '123','1', '2022-02-10 1919 Suzhou 4814') INSERT INTO `test`.`s _ user_ 1` (`userid`, `user_ name`, `name`, `pass_ word`, `salt`, `state`, `create_ time`) VALUES (2, 'Xiao Huang', 'Xiao Huang', '123123', '123','1', '2022-02-10 1919 Suzhou 4814')

Each SELECT statement within UNION must have the same number of columns: first query the SQL with the same column

SELECT * FROM `s_ user` UNION SELECT * FROM `sroomuser1`

Query all the columns, and you can see that the execution results are as follows.

If the first query has all columns and the second query has three fields, what about the query result? now execute the following SQL:

Misuse

SELECT * FROM `s_ user` UNION SELECT userid,name,pass_word FROM `sroomuser1`

Error tip: the SELECT statement used to query out different columns

Correct usage

SELECT userid,name,pass_word FROM `s_ user`UNION SELECT userid,name,pass_word FROM `sroomuser1`

Data type

   shows that the number of columns in multiple queries must be the same, so can it be handled normally if the number of columns queried is the same and the data types of the query fields are different? Next, replace the pass_word in the s_user query with create_time for the query. You can see after the query. In a SQL query, the data type of the column is irrelevant. Of course, it will not be set to a different type in the actual business.

SELECT userid,name,create_time FROM `s_ user`UNION SELECT pass_word,name,userid FROM `sroomuser1`

Column order

When    uses the UNION keyword to query, whether it is related to the order of the columns, see above may be related to the order of the columns, may also be irrelevant, then do a test. You can see that the execution result is independent of the order of the columns. In the actual business, multiple queries are merged, so we need to define the columns in the same order in the project.

SELECT create_time,userid,name FROM `s_ user`UNION SELECT pass_word,name,userid FROM `sroomuser1`

The difference between UNION and UNION ALL

In order to test the difference between UNION and UNION ALL,    needs to add [rookies] to the data table s_user_1, which is stored in the database with the rookies in table s_user. What will be the different results between the two?

INSERT INTO `test`.`s _ user_ 1` (`userid`, `user_ name`, `name`, `pass_ word`, `salt`, `state`, `create_ time`) VALUES (3, 'Xiaobai', 'Xiaobai', '123123', '123','1', '2022-02-10 1949 48 Swiss 43')

First execute:

SELECT user_name,name,pass_word,saltFROM `s_ user`UNION SELECT user_name,name,pass_word,saltFROM `sroomuser1`

Execution result:

Execute again

SELECT user_name,name,pass_word,saltFROM `s_ user`UNION ALL SELECT user_name,name,pass_word,saltFROM `sroomuser1`

Execution result:

   can see that the UNION association removes duplicates, while the UNION ALL query shows all the values and does not remove duplicate data.

The above is all the content of the article "how to use the UNION keyword in SQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.