Methods of combining multiple tables or result sets in a MS-SQL

If the MS-SQL combines two or query results into a single result set that contains all rows of all queries in the Union query, a common method

1. Use the Union or union all statement

1. 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.

That is to say, when Union is used to combine two tables, duplicate records are deleted. When Union all is used to combine two or more tables, the existence of duplicate records in the result set is not considered.

2. Note the following when using the Union operator:

The columns and columns in all queries must be in the same order.

In a statement that uses the Union operator combination, the number of all displayed lists must be the same (the list content can include column names, arithmetic expressions, Aggregate functions, and so on );

The data type must be compatible.

The corresponding columns in the result set using the Union combination must have the same data type, or implicit data conversion exists between the two data types, or type conversion is provided. For example, the Union operator cannot exist between columns of the datetime data type and columns of the binary data type, unless explicit conversion is provided, 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 result set columns in the statements combined with the Union operator must appear in the same order, because the Union operator compares the columns one by one based on the given order of each query.

3. When using the Union statement with other Transact-SQL statements, note the following:

If an into statement is used in a Union statement, it can only be used in the final table (or result set). If you want to use an into statement in a single table (or result set, the MS-SQL will prompt an error;

Incorrect statement: select aid, aname, ADEMO into from atable Union all select bid, bname, bdemo into from btable

The order by and compute clauses can be used only at the end of the Union statement to define the order of the final result or calculate the sum. These clauses cannot be used in separate queries of Union statements.

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 as follows:

Select * from

A

Order by A. Aid

You can also write as follows:

Select aid, aname, ADEMO from atable

Union all

Select bid, bname, bdemo from btable

Order by aid

Sort by aid here because the first column in the final result table is named aid in the MS-SQL. Some DBMS systems do not name the columns queried by different column names. In this way, order by column names cannot be used for sorting. However, the order of columns in the result table can be referenced to sort columns. For example: order by 1, which is equivalent to order by aid

Group by and having clauses can only be used in separate queries; they cannot be used to affect the final result set.

The Union operator can be used in an insert statement.

The for browse clause cannot be used in statements containing the Union operator. (For the usage and description of for browse, refer to the description of select statements)

Note: Some DBMS systems do not allow the use of functions and expressions in the select clause queried by Union combinations.

Ii. Use the except T or intersect Operator

Similar to union, the except T or intersect operator can combine multiple tables (or result sets) to generate a new table (or result set), but the results are different.

1. Random t operator

Similar to the Union command, limit t processes the results of two SQL statements. The difference is that distinct T clears all duplicate information from the result set of each table before comparing each table (or result set) with the second table (or result set. That is, the result set obtained from the distinct T operation will never contain duplicate records (rows ).

For example, we need to find unfinished student information in table_a and table_ B.

Select * From table_a

Except

Select * From table_ B

Note: Many DBMS systems do not support the except t operator, so you have to use the left Outer Join operator. Minus in Oracle are similar to those in Oracle.

2. Intersect Operator

Similar to the Union command, Intersect processes the results produced by two SQL statements. The difference is that union is the Union set, while intersect is the intersection. That is to say, the result set generated by intersect is a record (ROW) shared by multiple tables or result sets ).

Select aid, aname, ADEMO from atable

Intersect

Select bid, bname, bdemo from btable

3. Use Union corresponding to combine rows from two or more tables incompatible with Union

For example, there are two tables

Create Table table_a, Department char, pay money)

Create Table table_ B, Department char)

You can use Union corresponding to obtain the data in the columns with the same column name in the two tables:

Select * From table_a Union corresponding select * From table_ B

Note: not all DBMS support the Union corresponding operation, including the MS-SQL server.