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
- The number of columns should be same in each query
- 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.
`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
) ;
`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
คาสิโนออนไลน์ที่น่าเชื่อถือและมีความเป็นมืออาชีพที่สุดในตอนนี้
ReplyDeleteโปรโมชั่นGclub ของทางทีมงานตอนนี้แจกฟรีโบนัส 50%
เพียงแค่คุณสมัคร สล็อตออนไลน์ กับทางทีมงานของเราเพียงเท่านั้น
ร่วมมาเป็นส่วนหนึ่งกับเว็บไซต์คาสิโนออนไลน์ของเราได้เลยค่ะ
สมัครสล็อตออนไลน์ >>> Goldenslot
สนใจร่วมสนุกกับ คาสิโนออนไลน์ คลิ๊กได้เลย
มีทั้งคาสิโนออนไลน์ หวยออนไลน์ ฟุตบอลออนไลน์ สล็อตออนไลน์ และอื่นๆอีกมากมาย
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.
DeleteIEEE 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
Thanks for sharing this information. This is really useful. Keep doing more.
ReplyDeleteEducation Franchise India
Spoken English Franchise
Franchise For Spoken English Classes
Top Education Franchise In India
Best Education Franchise In India
Computer Education Franchise
Education Franchise India
Computer Center Franchise
Education Franchise Opportunities In India
This was truly awesome. Thanks so much for this..!
ReplyDeleteMicrosoft Azure DevOps Online Training
Microsoft Azure DevOps training hyderabad
Azure DevOps online training in hyderabad
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
ReplyDeleteBuy Tramadol online
ReplyDeleteمكافحة حشرات بالخبر افضل شركة مكافحة حشرات بالخبر
مكافحة حشرات بمكة افضل شركة رش حشرات بمكة
مكافحة حشرات بالمدينة المنورة افضل شركة رش حشرات بالمدينة المنورة
مكافحة حشرات بالدمام افضل شركة مكافحة حشرات بالدمام
it is very interesting to read your article about yoga. it is very informative article. thank ypu so much for sharing Best BBA Tuition in Rohini | Best BBA Coaching Classes in Rohini | Best BBA Coaching in Rohini | Best BBA Classes in Rohini | Best BBA Tuition Center in Rohini
ReplyDeleteHello 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.
ReplyDeleteSinging 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
Nice blog was really feeling good to read it. Thanks for this information.
ReplyDeleteSpoken English Classes in Chennai
English Speaking Course in Chennai
french classes
pearson vue test center in chennai
IoT Training in Chennai
spoken english course in chennai
Xamarin Training in Chennai
Node JS Training in Chennai
spanish language in chennai
content writing training in chennai
Spoken English Classes in OMR
Spoken English Classes in Porur
Very nice post with lots of information. Thanks for sharing these updates.
ReplyDeleteAngular Training in hyderabad
Angularjs Training in Bangalore
angular training in bangalore
Angularjs course in Chennai
angular course in bangalore
Angular Training in Coimbatore
salesforce course in bangalore
Big Data Training in Coimbatore
Angularjs course in Chennai
angularjs training in btm
Very informative blog! I am glad that I came across your article. I'm learning a lot from here. Keep us updated by sharing more such blogs.
ReplyDeleteAWS Training in Chennai
Amazon web services Training in Chennai
AWS course in Chennai
DevOps Training in Chennai
AWS Training in OMR
AWS Training in Porur
AWS training in Adyar
AWS training in Thiruvanmiyur
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.
ReplyDeleteThe concept you are saying is good. I was so happy after reading this article. Thank You so much for the good article.
ReplyDeleteLeadership Training in chennai
leadership training companies in chennai
leadership course in chennai
leadership in chennai
sales training in Chennai
sharepoint jobs in chennai
mvc chennai
inplant training in chennai
It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
ReplyDeletehttp://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/
best-liposuction-surgeons-in-turkey
ReplyDeletebest-tummy-tuck-surgeons-in-turkey
best-rhinoplasty-surgeons-in-turkey
best-gynecomastia-surgeons-in-turkey
best-breast-augmentation-surgeons-in-turkey
best-breast-reduction-surgeons-in-turkey
best-breast-lift-surgeons-in-turkey
Thanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website
ReplyDeleteworkday studio online training
best workday studio online training
top workday studio online training
ReplyDeletetrung tâm tư vấn du học canada vnsava
công ty tư vấn du học canada vnsava
trung tâm tư vấn du học canada vnsava uy tín
công ty tư vấn du học canada vnsava uy tín
trung tâm tư vấn du học canada vnsava tại tphcm
công ty tư vấn du học canada vnsava tại tphcm
điều kiện du học canada vnsava
chi phí du học canada vnsava
#vnsava
@vnsava
Nice post thanks for share article. worldstarhiphop.com mobile
ReplyDeleteUtilized for decorative instead of artistic functions are a unique course
ReplyDeletethemoviesbio
entertainmentbee
petrefine
thepetsabout
It’s a free and easy to use trading app
ReplyDeleteStock market trading apps in India
Trading has become easier and comfortable with online trading applications.
ReplyDeleteAdvanced trading apps in India
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.
ReplyDeleteIndira securities believes in giving a cutting edge technology to its clients. In the same Endeavour we have tied up with Tradetron.
ReplyDeleteVery nice blog, Tata steel share price is now on good position.
ReplyDeleteThanks for the nice information. Bajaj finance share price is now on good position
ReplyDeleteOpen Demat Account Online for Free with Indira Securities in just 15 mins.
ReplyDeleteThe company offers open commodity trading account online and traders community which helps them save on brokerage.
ReplyDeleteIndira Securities is top online stock broking company in Indore, India.
ReplyDeleteDo you need Personal Finance?
ReplyDeleteBusiness 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
Great Content. It will be useful for knowledge seekers. Keep sharing your knowledge through this kind of article.
ReplyDeleteJava Vogue
Useful tutorial
ReplyDelete