Using T and intersect to quickly compare SQL table Similarities and Differences

Compare the two query results and return non-repeated values.

Distinct t returns all non-repeated values not found in the right query from the left query.

Intersect returns all non-repeated values returned by both the left and right sides of the Intersect operands.

The following are the basic rules that combine the result sets of two queries that use the distinct T or intersect:

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

• The data type must be compatible.

Method

{<Query_specification> | (<query_expression>)} {response T | intersect }{< query_specification >|( <query_expression> )}

If the data type of the comparable columns returned by the query on the left and right of the operand T or intersect is a character data type with different sorting rules, the comparison is performed based on the sorting rule priority rules. If the conversion fails, Microsoft SQL Server 2005 database engine returns an error.

When a non-repeated value is determined by comparing rows, the two null values are considered equal.

The name of the result set returned by distinct T or intersect is the same as that returned by the query on the left side of the operand.

The column name or alias in the order by clause must reference the column name returned by the left-side query.

The null property of any column in The result set returned by distinct T or intersect is the same as that of the corresponding column returned by the query on the left of the operand.

If T or intersect is used together with other operators in the expression, it is evaluated in the context of the following priority:

1. Expressions in brackets

2. Intersect operations
3. The difference t and Union calculated from left to right based on the position in the expression

If distinct T or intersect is used to compare two or more query sets, the data type conversion is determined by comparing two queries at a time and following the preceding expression evaluate rules.

The partition T and intersect cannot be used in the definition and query notifications of distributed partition views, nor can they be used together with the compute and compute by clauses.

Except and Intersect can be used in Distributed queries, but they are only executed on the local server and will not be pushed to the linked server. Therefore, using t and intersect in Distributed queries may affect performance.

It is fully supported in the result set when the cursor and static cursor are used together with the except T or intersect operation. If the cursor or dynamic cursor driven by the key set is used together with the except T or intersect operation, the cursor of the result set of the operation is converted to a static cursor.

The except operation is displayed , Intersect operation is displayed .

  

<Query_specification> | (<query_expression>)

The query criterion or query expression returns data that is compared with the data from another query criterion or query expression. The column definitions can be different in except T or intersect operations, but they must be compared after implicit conversion. If the data type is different, the type of the result to be compared and returned is determined based on the data type priority rule.

If the type is the same but the precision, decimal places, or length is different, the result is determined based on the same rule used to merge expressions. For more information, see precision, decimal places, and length (TRANSACT-SQL ).

The query specification or expression cannot return XML, text, ntext, image, or non-binary CLR user-defined type columns because these data types cannot be compared.

Except

Return all non-repeated values not returned in the query on the right from the query on the left of the operand T.

Intersect

Returns all non-repeated values returned by both the left and right sides of the Intersect operations.

  Example
A. Use Limit t

In this example, use the data in tablea and tableb.

Tablea (col1 INT)	Tableb (col1 INT)
Null	Null
Null	1
Null	3
1	4
2	4
2
2
3
4
4

The following queryExceptThe query on the left of the operand returns all non-repeated values not found in the query on the right.

	Copy code
Select * From tablea distinct T select * From tableb;

The following is the result set:

	Copy code
Col1 ----------- 2 (1 row (s) affected)

B. Use intersect

In this example, use the data in tablea and tableb.

Tablea (col1 INT)	Tableb (col1 INT)
Null	2
Null	2
Null	2
1	4
2	4
2
2
3
4
4

The following query returnsIntersectAll non-repeated values returned by both queries on both left and right sides of the operand.

	Copy code
Select * From tablea intersect select * From tableb;

The following is the result set:

	copy the Code
col1 ----------- 24 (2 row (s) affected)