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

29 comments:

  1. คาสิโนออนไลน์ที่น่าเชื่อถือและมีความเป็นมืออาชีพที่สุดในตอนนี้
    โปรโมชั่นGclub ของทางทีมงานตอนนี้แจกฟรีโบนัส 50%
    เพียงแค่คุณสมัคร สล็อตออนไลน์ กับทางทีมงานของเราเพียงเท่านั้น
    ร่วมมาเป็นส่วนหนึ่งกับเว็บไซต์คาสิโนออนไลน์ของเราได้เลยค่ะ
    สมัครสล็อตออนไลน์ >>> Goldenslot
    สนใจร่วมสนุกกับ คาสิโนออนไลน์ คลิ๊กได้เลย
    มีทั้งคาสิโนออนไลน์ หวยออนไลน์ ฟุตบอลออนไลน์ สล็อตออนไลน์ และอื่นๆอีกมากมาย

    ReplyDelete
    Replies
    1. IEEE Final Year Project centers make amazing deep learning final year projects ideas for final year students Final Year Projects for CSE to training and develop their deep learning experience and talents.

      IEEE Final Year projects Project Centers in India 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.

      corporate training in chennai corporate training in chennai

      corporate training companies in india corporate training companies in india

      corporate training companies in chennai corporate training companies in chennai

      I have read your blog its very attractive and impressive. I like it your blog. Digital Marketing Company in Chennai

      Delete
  2. When you feel any kind of body pain, it is best if you go to the doctor for treating it. Sometimes body pain can be the symptom of some serious disease. Sometimes body pain attacks us suddenly because of which you may not able to get the help of the doctor. In those situations, to get quick and effective pain relief, you can take the help of painkillers though they cannot cure your pain. As your painkiller, choose Tramadol 50 mg which is very effective. This painkiller is available in the market with the name of Ultram. To use this painkiller, you can get it easily. Buy Tramadol online and get this painkiller at an affordable price
    Buy Tramadol online

    ReplyDelete
  3. Hello There. I found your blog using msn. This is a very well written article. I'll be sure to bookmark it and come back to read more of your useful info. Thanks for the post. I'll definitely return.

    Singing Institute in Delhi | Best Fashion Designing Course in Delhi | Best Academy For Makeup in Delhi | Academy of Event Planning Delhi | Interior Designing Institute in Delhi

    ReplyDelete
  4. It's very useful blog post with inforamtive and insightful content and i had good experience with this information.I have gone through CRS Info Solutions Home which really nice. Learn more details About Us of CRS info solutions. Here you can see the Courses CRS Info Solutions full list.Find the best Hadoop Training with great faculty. Go to know about crs info solutions Workday Training program.

    ReplyDelete
  5. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    http://chennaitraining.in/building-estimation-and-costing-training-in-chennai/
    http://chennaitraining.in/embedded-systems-training-in-chennai/
    http://chennaitraining.in/vlsi-training-in-chennai/
    http://chennaitraining.in/matlab-training-in-chennai/
    http://chennaitraining.in/iot-training-in-chennai/

    ReplyDelete
  6. Thanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website
    workday studio online training
    best workday studio online training
    top workday studio online training

    ReplyDelete
  7. Utilized for decorative instead of artistic functions are a unique course
    themoviesbio
    entertainmentbee
    petrefine
    thepetsabout

    ReplyDelete
  8. Trading has become easier and comfortable with online trading applications.
    Advanced trading apps in India

    ReplyDelete
  9. Algo trading is the new popular trading mechanism in the Indian stock market for Algo trading profits. Here, you can trade using the computer and predefined programs.

    ReplyDelete
  10. Indira securities believes in giving a cutting edge technology to its clients. In the same Endeavour we have tied up with Tradetron.

    ReplyDelete
  11. Thanks for the nice information. Bajaj finance share price is now on good position

    ReplyDelete
  12. Open Demat Account Online for Free with Indira Securities in just 15 mins.

    ReplyDelete
  13. The company offers open commodity trading account online and traders community which helps them save on brokerage.

    ReplyDelete
  14. Indira Securities is top online stock broking company in Indore, India.

    ReplyDelete
  15. Do you need Personal Finance?
    Business Cash Finance?
    Unsecured Finance
    Fast and Simple Finance?
    Quick Application Process?
    Finance. Services Rendered include,
    *Debt Consolidation Finance
    *Business Finance Services
    *Personal Finance services Help
    contact us today and get the best lending service
    personal cash business cash just email us below
    Contact Us: financialserviceoffer876@gmail.com
    call or add us on what's app +918929509036

    ReplyDelete