INTERSECTION

Oracle

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’.

PostgreSQL

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’.