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.
The syntax of the
INTERSECT operator is as follows:
SELECT column1, column2, ... FROM table1
SELECT column1, column2, ... FROM table2;
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.
Here is an example of how to use the
SELECT first_name, last_name, email FROM customers
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.
INTERSECT operator is a useful tool for finding the common rows between two or more tables or queries in SQL Server.