MariaDB INTERSECT Operator

INTERSECT Operator is used to return intersection of 2 or more tables. If a record exists in both tables, it will be included in the INTERSECT results. Otherwise, it will be omitted from the INTERSECT results.

Syntax:

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
INTERSECT
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

Image representation

Note: MariaDB does not suppoort INTERSECT operator but you can see the same thing by using IN operator to simulate the INTERSECT query as follows:

Return Single Field using INTERSECT Operator

Following is a general query for INTERSECT operator but it will not be supoported in MariaDB.

SELECT student_name
FROM Student
INTERSECT
SELECT student_name
FROM Students;

Use the following one:

SELECT Students.student_name
FROM Students
WHERE Students.student_name IN (SELECT Student.student_name FROM Student);

Output:

It shows only common names between both the tables "Students" and "Student".

INTERSECT Operator with WHERE Clause

Following is a general query for INTERSECT operator but it will not be supoported in MariaDB.

SELECT student_name
FROM Student
INTERSECT
SELECT student_name
FROM Students, 
WHERE salary > 1500;

Use the following syntax:

SELECT Students.student_name
FROM Students
WHERE Students.student_id < 10
AND Students.student_id IN
(SELECT Student.student_id
FROM Student
WHERE Student.salary > 1500);

Output: