Using union and UNION ALL in SQL

This article was reproduced from: http://www.cnblogs.com/chaobaojun/archive/2009/12/24/1631508.html

In Ms-sql If you combine the results of two or more queries into a single result set that contains all the rows of all queries in a union query, the common methods are as follows:

I. using the Union or union all statement 1. The difference between Union and UNION ALL statements?         UNION combines multiple tables (or result sets) and returns them as a single result set;         UNION all contains all rows in the result, including duplicate rows. In other words, usingUNIONwhen you combine two tables, delete the duplicate records; andUseUNION Allwhen you combine more than two tables, regardless of whether duplicate records exist in the result set, including duplicate rows。 2. Use UNION operator, note that:(1) The Order of columns and columns must be the same in all queries.     In statements combined with the UNION operator, the number of all display lists must be the same (list contents can include: column names, arithmetic expressions, aggregate functions, etc.); (2) Data types must be compatible. The corresponding columns in the result set using the UNION combination must have the same data type, or there is implicit data conversion between the two data types, or a type conversion is provided. For example, there is no possibility of a union operator between a column of the datetime data type and a column of the binary data type, unless an explicit conversion is provided, and the union operator can exist between the columns of the Money data type and the columns of the INT data type because they can be implicitly converted. (3) The corresponding result set columns in each statement combined with the Union operator must appear in the same order, because the union operator compares the columns individually, in the order given by each query. 3. with other Transact-SQL statement to use together UNION statement, you should be aware that:(1) If you use the INTO statement with a UNION statement, you can only use it in the final table (or result set), and if you want to use the into statement in a single table (or result set), Ms-sql will prompt for an error; Error statement: Select Aid,aname,ademo into From ATable Union all Select Bid,bname,bdemo to from Btable (2) only in UNION the end of the statement to allow the use of ORDER by and COMPUTE clausesTo define the order of the final result or calculate the summary value. These clauses cannot be used in separate queries that form UNION statements.ErrorStatement: Select Aid,aname,ademo from ATable order by Aidunion Allselect Bid,bname,bdemo from btable order by BID Positive SolutionCan be written like this: SELECT * FROM (select Aid,aname,ademo from ATable Union all select Bid,bname,bdemo from Btable) a Order by A.aid Positive SolutionYou can also write this: select Aid,aname,ademo from ATable Union all Select Bid,bname,bdemo from btable Order by AIDThe reason this is sorted by aid is because Ms-sql names the first column in the final result table as aid. Some DBMS systems do not name the table columns of queries that use different column names, so they cannot be sorted by the order by column name, but can be sorted by referring to the order of the columns in the result table, for example: Order by 1, which is equivalent to order by AID (1) GROUP by and Havin The G clauses can only be used in separate queries, and they cannot be used to affect the final result set.     (2) The UNION operator can be used in INSERT statements. The FOR BROWSE clause cannot be used in a statement that contains a UNION operator. (For browse use and description you can refer to the description of the SELECT statement) Note: some DBMS system in the UNION of a combination query Select the use of functions and expressions is not allowed in the clause.   two. using the except or INTERSECT operator

Except refers to data that exists in the first collection but does not exist in the second collection.

Intersect refers to data that exists in two collections.

Similar to union, the except or INTERSECT operators can also combine multiple tables (or result sets) to produce a new table (or result set), but with different results. 1. Except operatorSimilar to the UNION directive, except also handles the results of two SQL statements. Different places are except in each table (or result set) with the second table (or result set)All duplicate information has been purged from the result set of each table before comparison。 Which is the result set obtained from the except operation.will never contain duplicate records(line).    For example: We want to find the incomplete student information in table_a (Student information Form) and Table_b (student's compulsory completion information sheet) SELECT * FROM Table_a Except select * from Table_b Note: Many DBMS systems do not support the except operator, which is now implemented using the left Outer join operator. The minus in Oracle is similar to except. 2. INTERSECT operatorSimilar to the UNION Directive, INTERSECT also handles the results of two SQL statements. The different place is that Union is the union, and intersect isintersection。 In other words, the result set generated by Intersect isrecords that are common to multiple or result sets(line). Select Aid,aname,ademo from ATable INTERSECT Select Bid,bname,bdemo from btable three. combining rows from two or more union -Incompatible tables using union correspondingFor example: There are two tables create TABLE table_a (ID int,office int,address varchar), Department char (5), pay money) create TABLE Table_b ( Office int,id int,address VarChar, Department char (5)) You can use the union corresponding to get the data in a column with a common column name in two tables: Select * from Tabl E_a UNION corresponding Select * from Table_b

Note: Not all DBMS support union corresponding operations, including Ms-sql Server.

Using union and UNION ALL in SQL