Demo of MySQLunion syntax code

The following article mainly describes the demo of MySQLunion syntax code. I saw the demo of MySQLunion syntax code on the relevant website two days ago. I think it is quite good. I will share it with you. I hope this will help you in your future studies.

Copy code

 
 

  
  
SELECT ...   
  
  
UNION [ALL | DISTINCT]   
  
  
SELECT ...   
  
  
[UNION [ALL | DISTINCT]   
  
  
SELECT ...]   
  
  
SELECT ... UNION [ALL | DISTINCT] SELECT ... [UNION [ALL | DISTINCT] SELECT ...]  
 
 

UNION is used to combine the results from many SELECT statements into a result set. If you want to merge the query results of multiple tables, for example, group messages are separated from the personal message table, but you can extract and display them together, you can do this. You can use MySQLUNION to join the query results)

The selected columns at the corresponding position of each SELECT statement must have the same type. The precondition is that the two SELECT columns must be of the same type !). For example, the first column selected by the first statement should be of the same type as the first column selected by other statements .) The column name used in the first SELECT statement is also the name of the Column Used for the result.

The SELECT statement is a regular selection statement, but is subject to the following restrictions:

Only the last SELECT statement can use into outfile.

HIGH_PRIORITY cannot be used with SELECT statements that are part of the UNION operation. If you specify HIGH_PRIORITY for the first SELECT statement, it does not work. If you specify HIGH_PRIORITY for other subsequent SELECT statements, a syntax error occurs.

If you do not use the keyword ALL for UNION, ALL returned rows are unique, as if you have used DISTINCT for the entire result set. If ALL is specified, ALL matched rows are obtained from ALL used SELECT statements.

The DISTINCT keyword is a self-selected word and does not play any role, but can be used in the syntax according to the requirements of the SQL standard. In MySQL, DISTINCT represents the default working nature of a Common Object .)

You can mix union all and union distinct in the same query. The mixed UNION type is treated in this way, that is, the DISTICT shared body overwrites ALL the shared bodies located on the left. The DISTINCT shared body can be generated explicitly using union distinct, or implicitly using the DISTINCT or ALL keyword after UNION.

If you want to use the order by or LIMIT clause to classify or LIMIT all UNION results, parentheses should be added to a single SELECT statement, and put order by or LIMIT behind the last one. The following example uses both clauses:

Copy code

 
 

  
  
(SELECT a FROM tbl_name WHERE a=10 AND B=1)UNION(SELECT a FROM tbl_name WHERE a=11 AND B=2)ORDER BY a LIMIT 10;  
 
 

If you want to implement paging, You can process the result set of the two queries as a large result set and then perform LIMIT processing on the large result set !) Good ~!
(SELECT a FROM tbl_name WHERE a = 10 and B = 1) MySQLUNION (SELECT a FROM tbl_name WHERE a = 11 and B = 2) ORDER BY a LIMIT 10;

This order by statement cannot be referenced BY columns that contain table names, that is, names in tbl_name.col_name format. You can provide a column alias in the first SELECT statement, and refer to the alias in order by, or use the column position to refer to the column in order. Aliases are preferred because column locations are not recommended .)

In addition, if a column with a category has an alias, the order by clause must reference the alias instead of the column name. The first statement in the following statement must be run, but the second statement will fail. An error occurs when the 'A' column is unknown in 'order clause:

Copy code

 
 

  
  
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b;
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a;
To apply ORDER BY or LIMIT to an individual SELECT, 
place the clause inside the parentheses that enclose the SELECT:   
  
  
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b;
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a;
To apply ORDER BY or LIMIT to an individual SELECT, 
place the clause inside the parentheses that enclose the SELECT:  
 
 

To use order by or LIMIT for a single SELECT statement, the clause should be placed in parentheses. Parentheses include SELECT:

Copy code

 
 

  
  
(SELECT a FROM tbl_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION(SELECT a FROM tbl_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10);   
  
  
(SELECT a FROM tbl_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION(SELECT a FROM tbl_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10); 
 
 

2. instance Expansion

MySQLunion can combine two queries of the same table. the benefits of doing so are also very obvious. For example, in the blog application, you can use an SQL statement to display the top blog and ordinary blog pages.

Copy code

 
 

  
  
(   
  
  
SELECT *   
  
  
FROM `blog`   
  
  
WHERE top=1   
  
  
ORDER BY created DESC   
  
  
)   
  
  
UNION (   
  
  
SELECT *   
  
  
FROM `blog`   
  
  
WHERE top = 0   
  
  
ORDER BY created DESC   
  
  
) LIMIT 2 , 3   
  
  
 
 
 

The above content is an introduction to the MySQLunion syntax. I hope you will find some gains.