How to use INTERSECT in MySQL or MariaDB - MySQL / MariaDB Developer Tutorial

How to use INTERSECT in MySQL or MariaDB 

INTERSECT operator returns distinct records from two more more query results. To use INTERSECT operator below rules show be satisfied

1. The number of columns should be same in each query 
2. The data type of columns should be compatible

Neither MySQL nor MariaDB has INTERSECT operator. We have to work around to achieve INTERSECT operator results. We can use Distinct with INNER JOIN to achieve INTERSECT.

Syntax:

SELECT DISTINCT

    a.column1, a.column2, b.column1, b.column2

FROM

    table1 a

        INNER JOIN

    table12 b ON a.column1 = b.column1

        AND a.column2 = b.column2;

Example: 

Let's create two tables. Customer and Customer1. We will have more records in Customer table and few records in Customer1 table. We will use distinct with Inner Join to achieve Intersect operator results.

CREATE TABLE `customer` (
  `idcustomer` int,
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

 

Let's create Customer1 table with same definition.

CREATE TABLE `customer1` (
  `idcustomer` int,
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

Let's insert records in Customer table.

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Aamir','Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),

(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'); 

Let's write our query for customer and customer1 table to achieve intersect operator results.

SELECT DISTINCT

    a.*

FROM

    customer a

        INNER JOIN

    customer1 b ON a.idcustomer = b.idcustomer

        AND a.firstname = b.firstname

        AND a.lastname = b.lastname

        AND a.age = b.age

        AND a.phonenumber = b.phonenumber

        AND a.dob = b.dob

        AND a.gender = b.gender;

The above query returned two matching distinct records from customer and customer1 table that we will get if INTERSECT operator is used in any other relational database system.

INTERSECT Operator in MySQL / MariaDB - Alternative to INTERSECT Operator in MySQL