MariaDB Intersect Operator

by Online Tutorials Library July 14, 2022

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.

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:

Note: You can also use INTERSECT operator with multiple expressions.

Next TopicMariaDB Export

MariaDB Intersect Operator

MariaDB INTERSECT Operator

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

INTERSECT Operator with WHERE Clause

Note: You can also use INTERSECT operator with multiple expressions.

Smoke Testing

Traditional Marketing

You may also like