MariaDB - INTERSECT Operator

In MariaDB, the INTERSECT Operators merge the result set two or more select statements, and it returns the distinct rows as a result. MariaDB supports INTERSECT along with UNION since MariDB10.3.

The Syntax is as follows:

SELECT col_1, col_2 FROM table_name [WHERE condition]
INTERSECT [ALL | DISTINCT]
SELECT col_1, col_2 FROM table_name [WHERE condition]
INTERSECT [ALL | DISTINCT]
SELECT col_1, col_2 FROM table_name [WHERE condition]
..
[ORDER BY sort_exp];
  • Initially mentions the select queries you wish to merge the results of.
  • Next, use the INTERSECT operator to link or connect the queries.
  • Table_name is the name of the table we wish to retrieve data from. We can also specify table name as database_name.table_name
  • We must keep in mind that the select list of the select queries should have the same number of columns, and the data types of those columns should be the same.
  • We can also use WHERE clause to mention the condition which is OPTIONAL
  • We can use the ORDER BY clause with the sort_exp, which specifies the column name based on which the result will be sorted.
  • This operator returns the combined result of all the queries but only the distinct rows.
  • If we use order by clause for the individual select statement, it does not affect the final result. If we wish to order the result set, we can use the global ORDER BY clause specified on the last SELECT statement.
  • All the syntax and working of ORDER BY and LIMIT clauses are the same as UNION.
  • INTERSECT is of higher precedence than UNION and EXCEPT OPERATORS.

We can use DISTINCT and ALL keywords with INTERSECT, which were introduced in MariaDB 10.5.

If we use the ALL operator, it leaves duplicates intact.

Wherein the DISTINCT operator eliminates the duplicates. The default behaviour is the DISTINCT behaviour, even if the keyword is specified or not.

Let's see the example:

We will be using two tables named 'Student' and 'Course'.

The structure of the tables look as follows:

MariaDB [student]> desc student;
MariaDB- INTERSECT Operator
MariaDB [student]>desc course;
MariaDB- INTERSECT Operator

And the data looks like:

MariaDB [student]> select * from student;
MariaDB- INTERSECT Operator
MariaDB [student]> select * from course;
MariaDB- INTERSECT Operator

Let’s see the example for INTERSECT operator:

MariaDB [student]> SELECT student_id FROM student
    -> INTERSECT
    -> SELECT id FROM course;
MariaDB- INTERSECT Operator

Here we can see the common rows in both the table are returned.

The common student_id from both the tables are two, so the result is just two rows. We can also see the data type of the columns from both the tables are same. The title or name of the column in the final result set is the column name of first select query.

Example with ORDER BY:

MariaDB [student]> SELECT student_id, name FROM student
    -> INTERSECT
    -> SELECT id, name FROM course ORDER BY name;
MariaDB- INTERSECT Operator

In the above query, we have used ORDER BY, and we have sorted the result based on the name column, which is varchar type, so the rows are sorted alphabetically. The query returns both the select statement and the distinct rows from those result sets, which is our final result.

Example with multiple select statements

In the following example, we will be using three select statements.

We will be using three tables, so two tables are the one we used earlier, and the new table is 'Employee’ Which looks like:

MariaDB [student]> SELECT * FROM EMPLOYEE;
MariaDB- INTERSECT Operator

Let’s write the query:

MariaDB [student]> SELECT student_id, name FROM student
    -> INTERSECT
    -> select id, name from course
    -> intersect
    -> select emp_id, emp_name from employee;
MariaDB- INTERSECT Operator

Here we can see the result is one row which is the only common row in all three tables.