A method of combining multiple tables or result sets in MSSQL

In MSSQL If you combine the results of two or more queries into a single result set that contains all the rows of all the queries in a federated query, the common method is as follows:
I. Use 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 the rows in the result, including duplicate rows.
Ø that is, repeating records are deleted when two tables are combined with a union, and no duplicate records are considered in the result set when the union all is combined with more than two tables.
2. When using the UNION operator, be aware that:
Ø the number of columns and the order of columns must be the same in all queries.
In statements that combine with the UNION operator, the number of display lists must be the same (list contents can include: column names, arithmetic expressions, aggregate functions, and so on);
Ø data types must be compatible.
The corresponding columns in the result set using UNION combination must have the same data type, or there is an 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 columns of datetime data types and columns of binary data types, unless explicit conversions are provided, and the union operator can exist between columns of the Money data type and columns of the INT data type because they can be implicitly converted.
Ø 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. When using the UNION statement with other Transact-SQL statements, you should note that:
Ø If you use the INTO statement in a statement that has a union, 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 to ATable Union all select Bid,bname,bdemo to from Btable
Ø only the end of the UNION statement allows the ORDER BY and COMPUTE clauses to be used to define the sequence of the final result or the calculated rollup value. You cannot use these clauses in a separate query that builds a UNION statement.
Error statement:
Select Aid,aname,ademo from ATable order by AID
Union All
Select Bid,bname,bdemo from Btable order by BID
You can write this:
Select * FROM
(select Aid,aname,ademo from ATable Union all Select Bid,bname,bdemo from Btable) a
ORDER BY A.aid
You can also write this:
Select Aid,aname,ademo from ATable
Union All
Select Bid,bname,bdemo from Btable
ORDER BY AID
This is sorted by aid because Ms-sql names the first column in the final result table as aid. Some DBMS systems do not name table columns for queries that use different column names, so they cannot be sorted by the order by column name, although they can be sorted by reference to the sequence of columns in the result table, for example, by 1, which is equivalent to an ordered by AID
Øgroup by and HAVING clauses can only be used in separate queries, and they cannot be used to affect the final result set.
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 can refer to the description of the SELECT statement)
Note: Some DBMS systems do not allow functions and expressions to be used in the SELECT clause of a union-combined query.

Two. Using the except or INTERSECT operator
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 the results are different.
1. Except operator
Similar to the UNION directive, except also handles the results of two SQL statements. The difference is except. All duplicate information has been cleared from the result set of each table (or result set) before the second table (or result set) is compared. That is, the result set from the except operation will never contain duplicate records (rows).
For example: We want to find incomplete student information in table_a (Student information Form) and Table_b (student's required completion information form)
Select * from Table_a
Except
Select * from Table_b
Note: Many DBMS systems do not support the except operator, and you will have to use the left Outer join operator at this point. In Oracle, however, minus is similar to except.
2. Intersect operator
Similar to the UNION Directive, INTERSECT also handles the results of two SQL statements. The difference is that the Union is the Union, and the INTERSECT is the intersection. That is, Intersect generates a result set that is a record (row) shared by more than one table or result set.
Select Aid,aname,ademo from ATable
INTERSECT
Select Bid,bname,bdemo from Btable

Three. Use union corresponding to combine rows from two or more tables that are incompatible with union
For 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 data in columns in two tables that have a common column name:
SELECT * FROM table_a UNION corresponding select * from Table_b

Note: Not all DBMS support union corresponding operations, including Ms-sql Server.
Http://blog.csdn.net/sxycgxj/archive/2007/03/02/1518751.aspx