INTERSECT Operator - (SQL Server Operators)

In SQL Server, the INTERSECT operator is used to combine two SELECT statements into a single result set by retrieving only the rows that are present in both SELECT statements.

Syntax

Here is the syntax for the INTERSECT operator in SQL Server:

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

Example

Suppose we have two tables, Customers and Orders, with the following data:

Customers table:

CustomerID	FirstName	LastName	City
1	John	Doe	New York
2	Jane	Smith	Boston
3	Bob	Adams	Atlanta

Orders table:

OrderID	CustomerID	OrderDate
101	1	2019-01-10
102	2	2019-02-15
103	1	2019-03-20
104	3	2019-04-25

Now, let's use the INTERSECT operator to get the common customers who have placed orders:

SELECT CustomerID, FirstName, LastName, City
FROM Customers
INTERSECT
SELECT CustomerID, '', '', ''
FROM Orders;

The output of this query will be:

CustomerID	FirstName	LastName	City
1	John	Doe	New York

Explanation

The INTERSECT operator returns only those rows that are common between the two SELECT statements. In our example, the first SELECT statement retrieves the customers' information, while the second SELECT statement retrieves only the customer IDs with a blank value for the other columns from the orders table. If the customer ID is present in both tables, the INTERSECT operator returns that row.

Use

The INTERSECT operator is used to compare two sets of data and retrieve the common rows from them. This operator is useful when you want to find matching data in different tables, as in our example. You can also use it in combination with other operators to create complex queries.

Important Points

• The SELECT statements separated by the INTERSECT operator must have the same number of columns.
• The data types of the corresponding columns in both SELECT statements must be compatible.

Summary

In this page, we discussed the INTERSECT operator in SQL Server and demonstrated how to use it to combine two SELECT statements to retrieve the common rows from them. We covered the syntax, example, explanation, use, and important points of the INTERSECT operator. The INTERSECT operator is a useful tool in SQL Server for working with different datasets and finding common data between them.