SQL Server INTERSECT Operator
In SQL Server, the INTERSECT operator is used to fetch the records that are in common between two SELECT statements or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.
Syntax:
Parameter explanation
expression1, expression2, … expression_n: expressions specify the columns or calculations that you want to compare between the two SELECT statements. There is not necessary to have the same fields in each of the SELECT statements, but the corresponding columns must be similar data types in both tables.
tables: It specifies the tables that you want to retrieve records from. There must be at least one table listed in the FROM clause.
WHERE conditions: It is optional condition. It specifies the conditions that must be met for the records to be selected.
Image representation:
Note: The overlapped blue field specifies the intersect data.
Mandatory conditions for INTERSECT operation
- The number of expressions in both SELECT statements must be same.
- The corresponding columns in each of the SELECT statements must have similar data types.
- The INTERSECT operator fetch only common records the SELECT statements.
INTERSECT operator with single expression
Example:
Output:
INTERSECT operator with single expressions
Output:
It shows blank because nothing is common between them according to the query.