Home > OS >  How to perform semi join in MySQL?
How to perform semi join in MySQL?

Time:11-22

Hello I am new in MySQL and I am tasked to perform a semi join, how do I perform that in MySQL? So I have these following tables:

Table 1:

 ------ ---------------- -------- -------- 
| SID  | name           | course | Org_ID |
 ------ ---------------- -------- -------- 
| 1001 | JOSHUA OCRAY   | BSIT   |    100 |
| 1002 | RAIZEN CO      | BSIT   |    300 |
| 1003 | EDRIN SAN JUAN | BSIT   |    300 |
| 1004 | IAN GASPAR     | BSIT   |    100 |
| 1005 | DERRICK BONGAR | BSA    |    500 |
| 1006 | KYLE TAYAG     | BSCE   |    200 |
| 1007 | RHEY CRUZ      | BSIT   |    100 |
| 1008 | EDWIN DIAPANA  | BSCS   |    400 |
 ------ ---------------- -------- -------- 

Table 2:
 -------- ---------- 
| Org_ID | Org_Name |
 -------- ---------- 
|    100 | LITS     |
|    200 | ACES     |
|    300 | ACSS     |
|    400 | COES     |
|    500 | BAS      |
 -------- ---------- 

What is the exact MySQL code to perform semi join? Thank you very much.

CodePudding user response:

SemiJoin mysql standard query:

select * from Table1 where Org_ID in (select Org_ID from Table2);

Based on other criteria and join in your query, any one of the below semijoin strategy will be used.

Types of semijoin strategies available in MySQL:

  1. DUPLICATE-WEEDOUT
  2. FIRSTMATCH
  3. LOOSESCAN
  4. MATERIALIZATION
  5. TABLE PULLOUT
  • Related