SQL | INTERSECT Clause

The INTERSECT clause in SQL is used to combine two SELECT statements but the dataset returned by the INTERSECT statement will be the intersection of the data sets of the two SELECT statements. In simple words, the INTERSECT statement will return only those rows which will be common to both of the SELECT statements.

Syntax:

SELECT column1 , column2 ….

FROM table_names

WHERE condition

INTERSECT

SELECT column1 , column2 ….

FROM table_names

WHERE condition

Let’s assume that we have two table Customer Table and the Orders Table and we will perform some operations related to INTERSECT to understand better SQL intersect.

Customers Table:

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE NOT NULL,
    Phone VARCHAR(20) NOT NULL,
    Address VARCHAR(200) NOT NULL,
    City VARCHAR(50) NOT NULL,
    State VARCHAR(50) NOT NULL,
    ZipCode VARCHAR(10) NOT NULL
);

Output:

Orders Table:

CREATE TABLE Orderss (
    OrderID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    OrderDate DATE NOT NULL,
    ShipDate DATE,
    TotalAmount DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

Let’s insert some random data in the order table

Output:

Sample Queries:

SELECT Customer.CustomerID, Customer.FirstName, Customer.LastName
FROM Customer
LEFT JOIN Orderss ON Customer.CustomerID = Orderss.CustomerID
UNION
SELECT Customer.CustomerID, Customer.FirstName, Customer.LastName
FROM Customer
LEFT JOIN Orderss ON Customer.CustomerID = Orderss.CustomerID
WHERE Orderss.OrderID IS NULL;

Output:

INTERSECT with BETWEEN Operator

As we have already discussed in the initial syntax, we can also use the INTERSECT operator along with some conditional operators. We can use the INTERSECT operator with the BETWEEN operator in SQL to find rows that fall within a specified range.

Let’s assume that we have one table name Customer and another one as orderss.

Query:

SELECT CustomerID, FirstName, LastName
FROM Customer
WHERE CustomerID BETWEEN 100 AND 200
INTERSECT
SELECT CustomerID, FirstName, LastName
FROM Customer
WHERE LastName BETWEEN 'A' AND 'M';

Output:

INTERSECT With LIKE Operator

To match patterns in a string, use the LIKE operator. In SQL, the INTERSECT operator and the LIKE operator can also be used to find common rows that match a given pattern.

Example: Let’s use the LIKE operator and the wildcard ‘%’ to retrieve names that begin with the letter ‘v’ from the common names of both tables.

SELECT John
FROM Customer 
WHERE common_name LIKE 'v%'
INTERSECT
SELECT Bob
FROM orderss
WHERE common_name LIKE 'v%';

Output:

No output