SQL Reference - INTERSECTION

INTERSECTION

INTERSECTION is an SQL operator used to combine the result sets of two or more SELECT queries and return only the distinct rows that are shared by all queries. The returned result must match the number and order of columns in the original SELECT statements. The data types of the respective columns must also be compatible.

Example

-- Assuming we have 2 tables:
-- TABLE_1
-- +-----+
-- | ID  |
-- +-----+
-- | 1   |
-- | 2   |
-- | 3   |
-- +-----+

-- TABLE_2
-- +-----+
-- | ID  |
-- +-----+
-- | 2   |
-- | 3   |
-- | 4   |
-- +-----+

SELECT ID FROM TABLE_1
INTERSECT
SELECT ID FROM TABLE_2;

Output

-- Output
-- +-----+
-- | ID  |
-- +-----+
-- | 2   |
-- | 3   |
-- +-----+

Explanation

The INTERSECT operation in SQL is used to combine two SELECT statements and return only the records common to both. In this example, the INTERSECT operation returns the common ‘ID’ values from TABLE_1 and TABLE_2, resulting in outputs ‘2’ and ‘3’.

Example

CREATE TABLE Films (
    ID INT PRIMARY KEY,
    Name VARCHAR (100)
);

INSERT INTO Films (ID, Name)
VALUES (1, 'Film A'), (2, 'Film B'), (3, 'Film C');

CREATE TABLE Series (
    ID INT PRIMARY KEY,
    Name VARCHAR (100)
);

INSERT INTO Series (ID, Name)
VALUES (2, 'Film B'), (3, 'Film C'), (4, 'Film D');

SELECT Name
FROM Films
INTERSECT
SELECT Name
FROM Series;

Output

  Name
---------
 Film B
 Film C

Explanation

The INTERSECT SQL command is used to return the intersection of two or more result sets. In this example, it is used to retrieve the intersection of the ‘Name’ column in the ‘Films’ and ‘Series’ tables. The output is the names of the films that are also series, which in this case are ‘Film B’ and ‘Film C’.