The keyword INTERSECT in SQL is used in order to output the results of 2 or more statements using the SELECT keyword. The results returned have to be in both queries. Otherwise, if a record exists in one query and not the other, it will be excluded from the output.

The number of selected fields in the query must be the same and the data types must be alike.

Structure

SELECT EXPRESSION
FROM TABLE
WHERE [CONSTRAINTS]
INTERSECT
SELECT EXPRESSION
FROM TABLE
WHERE [CONSTRAINTS]

Example

Suppose that we want to see which user ids intersect between the users table and the positions table which contains their positions in a company where both the user is active and the position is active (that is, it has not been cancelled):

SELECT u.id
FROM users u
WHERE u.active = 1
INTERSECT
SELECT p.user_id
FROM positions p
WHERE p.active = 1;