What is the usage of SQL Union?

#What is the usage of sql union?

Summary of sql union usage:

The UNION operator combines the results of multiple SELECT statements into a result set.

(1) The following conditions must be met when using UNION:
A: All queries must have the same structure (that is, the number and order of columns in the query must be the same).

Ｂ: The data types of the corresponding columns can be different but must be compatible (the so-called compatibility means that implicit conversion can be performed between the two types, and an error will be reported if implicit conversion cannot be performed). You can also use explicit conversion to the same data type.

(When the data types are different, the resulting data type is determined according to the data type precedence rules. If the types are the same but the precision, scale, or length are different, the resulting data type is determined according to the same Rules to determine the result) (Refer to MSDN: Data type priority)

C: If it is an XML data type, the columns must be equivalent (all columns must be typed as XML schema, or untyped), If typed, the columns must be typed to the same XML schema collection.

(2) UNION result set
Ａ: The column names of the result set after UNION are the same as the column names of the result set of the first SELECT statement. Result set column names from another SELECT statement are ignored.

B: UNION deletes duplicate records in the result set by default. If you use the ALL keyword, all records will be retained in the result set (duplicate records are retained)

(3) Notes
A : Multiple UNION operators can be used in Transact-SQL statements

Ｂ: Each SELECT statement using the UNION operator cannot contain their own ORDER BY or COMPUTE clause. Instead, you can use an ORDER BY or COMPUTE clause only in the final combined result set (that is, after the last SELECT statement).

ＣC: GROUP BY and HAVING clauses can be used in each SELECT statement using the UNION operator.

D: By default, SQL Server 2005 evaluates statements containing the UNION operator from left to right. You can use parentheses to specify the order of evaluation.

ＭＳＤＮ Examples are as follows

For example, the following statements are not equivalent:

/* First statement. */ 
  SELECT * FROM TableA 
  UNION ALL 
 ( SELECT * FROM TableB 
  UNION 
 SELECT * FROM TableC 
 ) 
 GO 
 /* Second statement. */ 
 (SELECT * FROM TableA 
 UNION ALL 
 SELECT * FROM TableB 
 ) 
 UNION 
 SELECT * FROM TableC) 
 GO

Recommended tutorial: "SQL Video Tutorial"

The above is the detailed content of What is the usage of SQL Union?. For more information, please follow other related articles on the PHP Chinese website!