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

How to use Minus in MySQL or MairaDB - MySQL / MariaDB Developer Tutorial

How to use Minus in MySQL or MairaDB

MINUS operator is use to get all the records from first result set which are not present in second result set.
MySQL or MariaDB does not provide the MINUS operator.  We can achieve Minus operation by using Left Join.

Syntax:

SELECT 
    column_list 
FROM 
    table1
    LEFT JOIN table12 ON join_predicate
WHERE 
    table2.id IS NULL;


Example :

Let's create two tables. Customer and Customer1. We will have more records in Customer table and few records in Customer1 table. By using left join , we will achieve results which Minus operator returns in other relational databases systems.



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');

Now insert fewer records in Customer1 table than Customer table. I am going to only insert two records instead of 3.


insert into customer1(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');

Let's write our Left Join query to achieve Minus operator results on customer and customer1 table.


SELECT 
    a.*
FROM
    customer a
        LEFT 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
WHERE
    b.idcustomer IS NULL

As customer table has 3 records and customer1 table has only two matching records. The above query is going to return the single row from table customer which does not match exactly with customer1 table rows or not present in customer1 table.
How to use Minus operator in MySQL or MairaDB - Alternative to Minus Operator in MySQL