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


7 comments:

  1. نجار ابواب بالرياض نجار ابواب بالرياض
    تركيب ستائر بالرياض افضل شركة تركيب ستائر بالرياض
    تنظيف مكيفات بالرياض شركة غسيل مكيفات بالرياض
    شركة تنظيف افران الغاز بالرياض تنظيف افران بالرياض

    ReplyDelete
  2. Great Article. Thank you for sharing! Really an awesome post for every one.

    IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Project Domains for IT It gives you tips and rules that is progressively critical to consider while choosing any final year project point.

    Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining the authors explore the idea of using Java in Big Data platforms.
    Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

    ReplyDelete
  3. Thank you for taking the time to provide us with your valuable information. We strive to provide our candidates with excellent care
    http://chennaitraining.in/solidworks-training-in-chennai/
    http://chennaitraining.in/autocad-training-in-chennai/
    http://chennaitraining.in/ansys-training-in-chennai/
    http://chennaitraining.in/revit-architecture-training-in-chennai/
    http://chennaitraining.in/primavera-training-in-chennai/
    http://chennaitraining.in/creo-training-in-chennai/

    ReplyDelete
  4. When there is a problem with completing difficult tasks on the relevant field. Most of the people would not know how to get rid of this Free Shemale Porn Games and how to improve their way of thinking.

    ReplyDelete
  5. Java Training institute Coimbatore I am mani lives in Chennai. I have read your blog, its really useful for me. I did java development course in coimbatore at reputed java training centre this is useful for me to make a bright career in IT industry. So If anyone want to get best please vist Qtree Technologies.

    ReplyDelete