MariaDB INTERSECT Operator

In MariaDB, INTERSECT is a built-in set operator that returns the intersection of two result sets, that is, those rows existing in both result sets.

MariaDB INTERSECT Syntax

Here is the syntax of the MariaDB INTERSECT operator:

rs1 INTERSECT rs2
rs1 INTERSECT ALL rs2

Parameters

rs1

Optional. a result set.

rs2

Optional. Another result set.

Both result sets must have the same columns.

Return value

rs1 INTERSECT rs2 returns the intersection of two result sets. That is, INTERSECT return those rows that exist not only in the first result set but also in the second result set.

MariaDB INTERSECT Examples

First, let’s create two tables test_number_1 and test_number_2 to demonstrate the example:

DROP TABLE IF EXISTS test_number_1;
CREATE TABLE test_number_1 (
    num INT NOT NULL
);

DROP TABLE IF EXISTS test_number_2;
CREATE TABLE test_number_2 (
    num INT NOT NULL
);

Then, let’s insert a few rows:

INSERT INTO test_number_1
VALUES (0), (1), (1), (2), (3), (4), (4);
INSERT INTO test_number_2
VALUES (3), (4), (4), (5), (6), (7), (8);

Then, let’s look at the rows in the test_number_1 table:

SELECT * FROM test_number_1;

Output:

+-----+
| num |
+-----+
|   0 |
|   1 |
|   1 |
|   2 |
|   3 |
|   4 |
|   4 |
+-----+

Let’s look at the rows in the test_number_2 table:

SELECT * FROM test_number_2;

Output:

+-----+
| num |
+-----+
|   3 |
|   4 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
+-----+

If you want to get numbers that exist in both the test_number_1 and test_number_2 table, use the following statement:

SELECT * FROM test_number_1
INTERSECT
SELECT * FROM test_number_2;

Output:

+-----+
| num |
+-----+
|   3 |
|   4 |
+-----+

Note that INTERSECT returns the distinct rows by default, if you want to get all the rows, please use INTERSECT ALL, which is supported in MariaDB 10.5.0.

SELECT * FROM test_number_1
INTERSECT ALL
SELECT * FROM test_number_2;

Output:

+-----+
| num |
+-----+
|   4 |
|   3 |
|   4 |
+-----+

Conclusion

In MariaDB, INTERSECT is a built-in set operator that returns the intersection of two result sets, that is, those rows existing in both result sets.