How to Intersect Operator in MySQL ?

 Set theory, which is let A and B be two sets. The set consisting of all the elements belonging to set A and set B is called the intersection of set A and set B, denoted as A∩B.

The INTERSECT operator is a set operator that only returns different rows of two queries or more. In the MySQL database, INTERSECT can be used to query the intersection content. 

  1. The Syntax of the INTERSECT Operator

(SELECT column_list

FROM table_1)

INTERSECT

(SELECT column_list

FROM table_2);

The INTERSECT operator compares the result set of two queries and returns different rows that are output by the two queries.

To use the INTERSECT operator for two queries, follow these rules:

The order and number of columns in the select list of the query must be the same.

The following graph illustrates the INTERSECT operator:

The result set produced by the left query is (1,2,3).

The correct query returns the result set (2,3,4).

The INTERSECT operator returns different rows, including the two result sets (2,3).
It should be noted that the SQL standard has three set operators, including UNION, INTERSECT, and MINUS.
INTERSECT is simulated in MySQL
Unfortunately, MySQL does not support the INTERSECT operator. However, you can simulate the INTERSECT operator.

Set Up the Sample Table
The following statement creates tables t1 and t2 and then inserts data into the two tables.

CREATE TABLE t1 (

id INT PRIMARY KEY

);

CREATE TABLE t2 LIKE t1;

INSERT INTO t1(id) VALUES(1),(2),(3);

INSERT INTO t2(id) VALUES(2),(3),(4);

The following query returns the rows in the t1 table.

SELECT id FROM t1;

id


1

2

3

The following query returns row t2 in the table:

SELECT id

FROM t2;

id

---

2

3

4

  1. Simulate INTERSECT using DISTINCT and INNER JOIN clauses

The following statement uses the DISTINCToperator and INNER JOIN clauses to return different rows in the two tables:

SELECT DISTINCT

   id

FROM t1

   INNER JOIN t2 USING(id);

id

----

2

3

The INNER JOIN clause returns rows from the left and right tables. The DISTINCT operation will delete duplicate rows.

2. Simulate INTERSECT using IN and subqueries

The following statement uses the IN operator and a subquery to return the two result sets' intersection.

SELECT DISTINCT id

FROM t1

WHERE id IN (SELECT id FROM t2);

id

----

2

3

The subquery returns the first result set. The outer query uses the IN operator to select values ​​that only exist in the first result set. The DISTINCT operator can ensure that only different values ​​are being chosen.

125 comments

  1. slonned
  2. slonned
  3. slonned
  4. Nridkk

    Titufs - edstrongp Vomuqc

  5. Wdhjwj

    what is dapoxetine used for - priligy 90mg online cialis cheap

  6. Ozsrhe

    cheapest ed pills online - ed pills that really work male ed pills

  7. Chngxk

    how to buy accutane online - accutane gel price accutane gel price

  8. Xzspnc

    amoxicillin for humans for sale - amoxicillin walgreens antibiotics without a doctor‘s prescription

  9. Gkpxrc

    vardenafil 10 - cheap levitra vardenafil online pharmacy

  10. Talasa

    cost of ivermectin lotion - ivermectin tablets for humans ivermectin 5ml

  11. Ouiucg

    cialis from us pharmacy - buy cialis over the counter usa india tadalafil

  12. Ounzne

    ivermectin nz - buy ivermectin for humans australia stromectol price

  13. Kshkqs

    price of accutane in australia - accutane pills price in south africa accutane medication

  14. Iamtma

    lyrica 75 mg cost - lyrica 100 online pharmacy usa

  15. Wdtums

    amoxicillin - amoxicillin brand name amoxicillin no prescription

  16. Fbesdr

    sildenafil 50mg brand name - erectile dysfunction causes cheap online viagra

  17. Yyqwwk

    generic tadalafil 5mg cost - cost tadalafil generic cialis 5mg online pharmacy

  18. Voosqn

    stromectol 12mg - ivermectin 3 mg tabs ivermectin 2mg

  19. Ghnyqc

    prednisone 50 mg - fastpredsone.com prednisone 54092

  20. Urvghw

    provigil for sale - modafinil 100mg generic provigil

  21. Cvsbym

    zpak - zithromax medication purchase zithromax z-pak

  22. Williamsmesk

    free chat online singles
    [url="http://datingsitesover.com/?"]swinger dating[/url]

  23. Iroooe

    lasix pills - lasix 10 mg daily can i buy lasix over the counter in australia

  24. Pulazt

    clomid online - buy clomid clomiphene online 100mg

  25. Bpgles

    online viagra prescription usa - viagra tablet 25 mg viagra soft flavoured

  26. Sjhplh

    tadalafil 10mg from canada - cialis online from canada india pharmacy online tadalafil

  27. Hwxzel

    ivermectin tablets order - stromectol 6mg online ivermectin oral 0 8

  28. KevinLig

    disabled gay dating sites
    canada gay dating sites
    [url="http://gaychatgay.com?"]step by step gay dating guide[/url]

  29. Yexelu

    wind creek casino online play - real money casino app casinos online

  30. Gkjmer

    ed pills gnc - buying ed pills online ed pill

  31. KevinLig

    free gay men dating
    big black gay dating
    [url="http://gaychatgay.com?"]gay dating agency sydney[/url]

  32. Zjbaqj

    prednisone order online uk - 10mg prednisone prednisone 20 mg tablets

  33. Ihomlh

    price of 100mg viagra - where to buy viagra in canada safely buy cheap generic viagra

  34. Zatxnz

    tadalafil paypal - Cialis online ordering cheap cialis overnight

  35. Euvxis

    ivermectin tablet 1mg - stromectole stromectol pills canada

  36. KevinLig

    russian gay dating scams
    gay men dating in arkansas
    [url="http://freegaychatnew.com?"]gay dating manhattan[/url]

  37. Rgjamb

    dr oz ed pills free trial - medicines for ed erectile dysfunction doctors near me

  38. Opkgfu

    ventolin cost uk - onventolinp buy albuterol online

  39. Winkpj

    how to buy cytotec in singapore - cytotec 200 mg canadian pharmacy cytotec

  40. Qouphr

    doxycycline price uk - generic doxycycline online doxycycline pharmacy online

  41. Yjnjzp

    neurontin prices - levothyroxine 0.05 mg synthroid 0.75 mg

  42. NoffAnend

    http://buytadalafshop.com/ - cheapest cialis 20mg

  43. Oscineded
  44. Qifxrs

    sildenafil pfizer - sildenafil australia online

  45. Viagra

    Mejor Viagra

  46. Milo

    It's a shame you don't have a donate button! I'd most certainly donate to this superb blog!
    I suppose for now i'll settle for book-marking and adding your RSS feed
    to my Google account. I look forward to brand new updates and will talk about this website
    with my Facebook group. Talk soon!

    Feel free to surf to my web site - there web hosting

  47. Gyezoo

    discount cialis pills - online pharmacy worldwide shipping cialis men

  48. Dane

    Heya i am for the primary time here. I came across this board and I find it
    quest bars (http://j.mp/)
    really useful & it helped me out a lot. I hope to offer
    something again and help others like you aided me.

  49. Nnqudo

    vardenafil dose - viagra cialis vardenafil online generic vardenafil 20 mg

  50. Shannan

    Great site you have here.. It's hard to find high quality writing
    like yours nowadays. I seriously appreciate individuals like you!
    Take care!!

    Here is my blog :: where quest bars [j.mp]

  51. poidayrap
  52. Viagra

    Cephalexin Canine Information

  53. Klgzai

    ivermectin usa price - price of stromectol stromectol generico

  54. Buster

    This paragraph presents clear idea designed for the new
    viewers of blogging, that genuinely how to do
    blogging.

    Look into my web blog :: on asmr

  55. Madge

    WOW just what I was looking for. Came here by searching for when

    My blog my scoliosis surgery

  56. Yhsrqy

    buy prednisone tablets online - prednisone pak where to buy prednisone 20mg

  57. Angel

    magnificent submit, very informative. I ponder why the other
    experts of this sector don't notice this. You should proceed your writing.
    I am confident, you've a huge readers' base already!

    Also visit my website ... their asmr

  58. PatrickSix

    gay dating phone
    gay bdsm dating sites
    [url="http://gaychatgay.com?"]gay chinese dating[/url]

  59. Tbmoax

    can you buy accutane online uk - accutane online accutane costs canada

  60. Sallie

    I always spent my half an hour to read this website's articles all the time along with a cup of coffee.

    Review my web blog: that asmr

  61. cheap non prescription finasteride 5mg

    Cialis Ou Acheter Internet

  62. Sheila

    I know this web site provides quality based articles and extra material, is there any other web site which provides
    such information in quality?

    my website; asmr your

  63. Sharyn

    Great web site you've got here.. It's difficult to find high-quality writing like yours these days.
    I truly appreciate individuals like you! Take care!!

    my webpage when scoliosis surgery

  64. Iwukht

    buy amoxicilina 500 mg online - amoxicillin price without insurance buy amoxicilin 500 mg canada

  65. sefeanaps
  66. PatrickSix

    best gay dating site nyc
    m4m gay dating site
    [url="http://freegaychatnew.com?"]gay ftm dating[/url]

  67. traitly
  68. Twmoen

    medrol price in india - lyrica without prescription lyrica tablets 25mg

  69. itewhex

    http://buypropeciaon.com/ - finpecia from india online

  70. Leowcm

    cheap essays online - essay writer funny help writing a paper for college

  71. Michaelnix

    true gay dating sites
    boston gay dating free sites
    [url="gayonlinedating.net?"]gay anime bears dating[/url]

  72. Bceuja

    sildenafil medication - Canada viagra generic buy cheap sildenafil online uk

  73. Maroweawl
  74. Titismles

    https://buystromectolon.com/ - oral ivermectin

  75. Jsqsdv

    cheapest tadalafil uk - Cialis online us cialis viagra comparison

  76. stromectol ivermectin buy

    order cialis professional

  77. Ivvwtlgp
  78. Upbljbsh
  79. Carl
  80. Jwfriisy
  81. Qpblirtc
  82. Sandra
  83. Nvnzuwxv
  84. Lzgwnxhx
  85. Thglim

    ivermectin tablets order - buy ivermectin nz generic stromectol online

  86. Fnvqvoyc
  87. Msffdnev
  88. Agtkustm
  89. Oxbpzwtn
  90. Jycarwds
  91. Eorvehzi
  92. Opaekkcr
  93. Henrybrura

    gay men coast of kerry dating
    lads gay dating
    [url="http://gaydatinglosangeles.com/?"]dating white gay guy[/url]

  94. Uweobu

    prednisone 1 mg for sale - canada prednisone 50 mg buy prednisone 1 mg mexico

  95. Cindy
  96. Azaylw

    medication furosemide 20 mg - clomiphene tablets lasix pills 20 mg

  97. Henrybrura

    seth moulton dating gay
    gay not dating trans men
    [url="http://gaydatingcanada.com/?"]closet gay dating site[/url]

  98. Hlstqr

    can i buy ventolin over the counter in usa - ventolin australia price ventolin inhalers

  99. suepext

    http://buylasixshop.com/ - furosemide pharmacy pitampura india

  100. Awghqt

    cytotec pills over the counter - where can i get cytotec pills over the counter where can you buy cytotec

  101. twilsOm
  102. Lester
  103. Roberto
  104. Jrrejl

    order doxycycline no prescription - prednisolone 20 mg brand name prednisolone buy

  105. Zithromax

    Ed Meds With No Rx

  106. Kzgzhv

    india ivermectin - can you buy stromectol over the counter ivermectin online

  107. addibly
  108. Plaquenil

    Dapoxetine Pour La Vente En Australie

  109. Qkosfp

    stromectol nz - buy stromectol for humans ivermectin 6mg

  110. Lasix

    Viagra Sin Receta Foro

  111. Axiotolal

    http://buyzithromaxinf.com/ - azithromycin side effects

  112. Deidkq

    generic sildenafil 100mg - sildenafil samples buy generic sildenafil

  113. Baimame
  114. Etozsn

    tadalafil 5mg - tadalapills canadian online pharmacy tadalafil

  115. icolladia
  116. FrauffLup
  117. priligy where to buy

    Ou Acheter Du Cialis Generique

  118. Arthur

    Метод расстановок. Системно-феноменологическая психотерапия Системные
    перестановки. Системные расстановки.

    Системно-феноменологический подход.
    Метод системных семейных расстановок.
    Семейное консультирование и психотерапия.

  119. Fuyagm

    120mg accutane - rx costs accutane best accutane coupon

  120. Xyefij

    how to write my thesis - order research paper online write my thesis

  121. Clarissa
  122. Tpkqop

    stromectol nz - ivermectin stromectol ivermectin cost

Comment

Your email address will not be disclosed. The required fields are marked with*.

Related recommendation

No related articles!

微信扫一扫,分享到朋友圈

How to Intersect Operator in MySQL ?