Mysql's union usage and comprehensive application of subqueries, mysqlunion

Mysql's union usage and comprehensive application of subqueries, mysqlunion

Union query combines the query results of two or more SQL statements into a result set.

Example: sql1: N rows, sql2: M rows, sql1 union sql2 ---> N + M rows

1. Can I query two tables and then union them?

Yes. union merges "result sets" and does not distinguish the table from which the union belongs.

2. Take the data from two tables and use the "alias" to make the columns of the two result sets consistent. If the column names of the retrieved result set are different, can union be performed.

Yes, and the final name of the retrieved column is subject to the 1st SQL statements.

3. What conditions can union meet?

As long as the number of columns in the result set is the same (for example, both columns are 2 or N)

4. Can the result set be sorted after union?

Yes. Sql1 union sql2 order by field

Note: order by is the order of the merged result set.

5. What if the Union result is repeated (that is, two or N rows with the same values in all columns?

This situation is quite common and will be de-duplicated by default.

6. What if I don't want to remove duplicates?

Union all

 

The following examples are used to verify common features and applications of union:

CREATE TABLE num_a (    id VARCHAR( 3 ) NOT NULL,    num INT(3 ) UNSIGNED NOT NULL)CHARSET utf8 ENGINE MYISAM;CREATE TABLE num_b (    id VARCHAR( 3 ) NOT NULL,    num INT(3 ) UNSIGNED NOT NULL)CHARSET utf8 ENGINE MYISAM;INSERT INTO num_a VALUES ( 'a', 5 );INSERT INTO num_a VALUES ( 'b', 10 );INSERT INTO num_a VALUES ( 'c', 15 );INSERT INTO num_a VALUES ( 'd', 10 );INSERT INTO num_b VALUES ( 'b', 5 );INSERT INTO num_b VALUES ( 'c', 15 );INSERT INTO num_b VALUES ( 'd', 20 );INSERT INTO num_b VALUES ( 'e', 99 );

 

1. union removes duplicate rows.

SELECT id,num FROM num_a UNION SELECT id, num FROM num_b

 

2. order by sorts the union result sets.

SELECT id,num FROM num_a UNION SELECT id, num FROM num_b ORDER BY num DESC

 

3. union all does not filter duplicate rows.

SELECT id,num FROM num_a UNION ALL SELECT id, num FROM num_b

 

4. Retain the different index results of num_a and num_ B, add the same index results, and then output:

SELECT a.id, ( a.num + b.num ) AS num FROM num_a AS a INNER JOIN num_b AS b ON a.id = b.idUNION ALLSELECT * FROM num_a AS a WHERE NOT EXISTS( SELECT * FROM num_b AS b WHERE a.id = b.id )UNION ALLSELECT * FROM num_b AS b WHERE NOT EXISTS( SELECT * FROM num_a AS a WHERE a.id = b.id )ORDER BY id ASC

 

5. The second method uses the subquery grouping statistics to achieve the same effect.

SELECT id, SUM( num ) AS num FROM ( SELECT * FROM num_a a UNION ALL SELECT * FROM num_b b ) tmpGROUP BY id;