SQL introductory learning: A brief analysis of the usage of the UNION keyword

During our development process, we often use multiple query results to be displayed directly. So how should we query? This article is about learning SQL. Let’s take a look at the UNION keyword in SQL and how to use it. I hope it will be helpful to everyone!

#The UNION keyword in SQL will display the results of multiple query conditions.

UNION

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

• Each SELECT statement inside a UNION must have the same number of columns.
• Columns must have similar data types.
• The order of the columns in each SELECT statement must be the same.

The same number of columns

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

Create tables respectively s_user and table s_user_1

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 is as follows:

INSERT INTO `test`.`s_user`(`userid`, `user_name`, `name`, `pass_word`, `salt`, `state`, `create_time`) VALUES (1, '小米', '小米', '123123', '123', '1', '2022-02-10 19:48:43');
INSERT INTO `test`.`s_user`(`userid`, `user_name`, `name`, `pass_word`, `salt`, `state`, `create_time`) VALUES (2, '小白', '小白', '123123', '123', '1', '2022-02-10 19:48:43');

INSERT INTO `test`.`s_user_1`(`userid`, `user_name`, `name`, `pass_word`, `salt`, `state`, `create_time`) VALUES (1, '小红', '小红', '123123', '123', '1', '2022-02-10 19:48:43');
INSERT INTO `test`.`s_user_1`(`userid`, `user_name`, `name`, `pass_word`, `salt`, `state`, `create_time`) VALUES (2, '小黄', '小黄', '123123', '123', '1', '2022-02-10 19:48:43');

Each SELECT statement inside UNION must have the same number of columns: query first SQL

SELECT * FROM `s_user` UNION SELECT * FROM `s_user_1`

with the same columns queries all columns. You can see that the execution results are as follows.

If the first query is for all columns and the second query is for three fields, what is the query result? Now execute the following SQL:

Error usage

SELECT * FROM `s_user` UNION SELECT userid,name,pass_word FROM `s_user_1`

##Error message: The SELECT statement used to query a different number of columns

Correct usage

SELECT userid,name,pass_word  FROM `s_user` UNION SELECT userid,name,pass_word FROM `s_user_1`

Data type

As seen above, the number of columns for multiple queries must be The same, then if the number of columns queried is the same but the data type of the query field is different, can it be processed normally? Next, replace pass_word in the s_user query with create_time to query. You can see it after querying. In SQL queries, the data type of the column is irrelevant. Of course, in actual business, they will not be set to different types.

SELECT userid,name,create_time  FROM `s_user` UNION SELECT pass_word,name,userid FROM `s_user_1`

Column order

 When using the UNION keyword to query, is it related to the order of the columns? As seen above, it may be related to the order of the columns. , it may be irrelevant, so let’s do a test. You can see that the execution results have nothing to do with the order of the columns. In actual business, multiple queries are merged, so the order of columns we define in the project needs to be consistent.

SELECT create_time,userid,name  FROM `s_user` UNION SELECT pass_word,name,userid FROM `s_user_1`

The difference between UNION and UNION ALL

In order to test the difference between UNION and UNION ALL, you need to add [Xiaobai] to the data table s_user_1, and compare it with the table s_user [Xiaobai] are both stored in the database, what are the different results between the two?

INSERT INTO `test`.`s_user_1`(`userid`, `user_name`, `name`, `pass_word`, `salt`, `state`, `create_time`) VALUES (3, '小白', '小白', '123123', '123', '1', '2022-02-10 19:48:43');

Execute first:

SELECT user_name,name,pass_word,salt
FROM `s_user` UNION SELECT user_name,name,pass_word,salt FROM `s_user_1`

Execution result:

##Execute again

SELECT user_name,name,pass_word,salt
FROM `s_user` UNION ALL SELECT user_name,name,pass_word,salt FROM `s_user_1`

Execution results:

You can see that UNION association removes duplicate items, while UNION ALL queries all values ​​without removing duplicate data.

Conclusion

 Okay, the above is the usage of the UNION keyword in SQL. Thank you for reading. I hope you like it. If it is helpful to you, please like and collect it. If there are any deficiencies, please comment and correct them. See you next time.

Recommended learning:

mysql video tutorial

The above is the detailed content of SQL introductory learning: A brief analysis of the usage of the UNION keyword. For more information, please follow other related articles on the PHP Chinese website!