Bayyana INNER, LEFT, da RIGHT JOIN a MySQL tare da misalai.

Question

IT Interview Practice · Accepted Answer

JOIN yana haɗa rows daga tebura biyu ta hanyar daidaita wani sharaɗi, yawanci foreign key. *Nau'in* join ne ke tantance abin da zai faru ga rows waɗanda **ba su da daidaici** a ɗaya gefen.

## Uku da kake buƙata

- **INNER JOIN** — riƙe kawai rows da suka daidaita a bangarorin biyu.
- **LEFT JOIN** — riƙe kowace row daga teburin hagu; columns na dama marasa daidaici suna dawowa `NULL`.
- **RIGHT JOIN** — madubi: riƙe kowace row na dama. Ba a cika amfani da shi ba; mutane suna juya tebura su rubuta LEFT a maimakon.

```sql
-- customers(id, name)   orders(id, customer_id, total)

-- INNER: only customers who have at least one order
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

-- LEFT: every customer, even those with zero orders (total = NULL)
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

-- Classic use: find customers with NO orders
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;   -- the LEFT JOIN's NULL side reveals the gap
```

## Kula da hankali

- Tace teburin dama a cikin `WHERE` (misali `WHERE o.total > 100`) a shiru yana mayar da LEFT JOIN zuwa INNER JOIN, saboda `NULL > 100` ƙarya ce. Sanya irin waɗannan sharuɗɗa a cikin `ON` idan kana son riƙe rows marasa daidaici.
- Ko da yaushe yi join a kan columns masu **index** (foreign key) ko kuma join yana faɗuwa zuwa nested scans.

## Me ya sa yake da muhimmanci

Yawancin queries na gaske suna shafar tebura da yawa, kuma nau'in join da bai dace ba a shiru yana zubar da rows ko ninka su — bug da ke dawo da sakamako *mai gaskatawa*. Sanin cewa LEFT JOIN + `IS NULL` yana samun rows "da suka ɓace", da cewa `WHERE` a kan gefen null yana soke LEFT, yana hana wata gaɓar kurakuran rahoto gaba ɗaya.

Uku da kake buƙata

Kula da hankali

Me ya sa yake da muhimmanci

Tambayoyi masu alaƙa