The SQL Server INTERSECT operator is used to return the intersection of two or more result sets obtained from separate SELECT statements. In other words, it returns only the rows that appear in both sets, eliminating any duplicates.

Syntax

The syntax of the INTERSECT operator is as follows:

SELECT column1, column2, ... FROM table1
INTERSECT
SELECT column1, column2, ... FROM table2;

The INTERSECT operator can only be used with two or more SELECT statements. Each SELECT statement must have the same number of columns and the same data types in corresponding columns.

Example

Here is an example of how to use the INTERSECT operator:

SELECT first_name, last_name, email FROM customers
INTERSECT
SELECT first_name, last_name, email FROM orders;

This SQL statement will return only the rows that appear in both the customers and orders tables based on the matching columns of first_name, last_name, and email.

It is important to note that the INTERSECT operator only returns distinct rows. This means that if a row appears more than once in one or both of the result sets, it will only appear once in the final result set.

In addition, the order of the columns in the SELECT statements does not matter, as long as the corresponding columns have the same data type. However, the order of the columns in the final result set will be based on the order of the columns in the first SELECT statement.

Overall, the INTERSECT operator is a useful tool for finding the common rows between two or more tables or queries in SQL Server.