MariaDB Intersect

Summary: in this tutorial, you will learn how to use the MariaDB intersect operator to combine result sets of two or more queries.

Introduction to MariaDB intersect operator

The MariaDB intersect operator combines result sets of two or more select statements and returns the distinct rows from the queries’ result sets.

Here is the syntax of the MariaDB intersect operator:

select-statement1
intersect
select-statement2
intersect 
select-statement3
...
[order by sort_expression];
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify queries that you want to combine their result sets.
• Second, connect two queries by the intersect operator.

The select statements such as select-statement1, select-statement2, etc., must follow these requirements:

• The select lists of the select statements must have the same number of columns.
• The data types of the corresponding columns must be the same.

The intersect operator returns the distinct rows that are returned by all the queries.

The intersect operator uses column names of the first select statement as the column names of the final result set.

The order by clause of the individual select statement has no effect on the order of rows in the final result. If you want to sort the final result set, you can use a global order by clause that appears on the last select statement.

The following picture illustrates the intersect operation:

MariaDB intersect operator example

See the following tables guests and vips from the sample database.

This example uses the intersect operator to find contacts who are both guests and vips:

select name from guests
intersect 
select name from vips
order by name;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned about the MariaDB intersect operator and how to use it to combine result sets of queries.