Eleza INNER, LEFT, na RIGHT JOIN katika MySQL kwa mifano.

Question

IT Interview Practice · Accepted Answer

JOIN inaunganisha rows kutoka majedwali mawili kwa kulinganisha sharti, kwa kawaida foreign key. *Type* ya join huamua nini kinatokea kwa rows ambazo **hazina linganifu** upande wa pili.

## Tatu unazozihitaji

- **INNER JOIN** — weka tu rows zinazolingana pande zote mbili.
- **LEFT JOIN** — weka kila row kutoka jedwali la kushoto; columns za kulia zisizolingana zinarudi `NULL`.
- **RIGHT JOIN** — kioo cha LEFT: weka kila row ya kulia. Hutumika mara chache; watu hugeuza majedwali na kuandika LEFT badala yake.

```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
```

## Kuwa mwangalifu

- Kuchuja jedwali la kulia ndani ya `WHERE` (mfano `WHERE o.total > 100`) kimyakimya hurudisha LEFT JOIN kuwa INNER JOIN, kwa sababu `NULL > 100` ni false. Weka masharti kama hayo katika `ON` clause ikiwa unataka kuweka rows zisizolingana.
- Daima join kwenye columns zenye **index** (foreign key) au join itashuka hadi nested scans.

## Kwa nini ni muhimu

Queries nyingi halisi hugusa majedwali mengi, na type ya join isiyo sahihi kimyakimya huondoa au hurudufu rows — bug inayorudisha matokeo *yanayoonekana ya kweli*. Kujua kwamba LEFT JOIN + `IS NULL` hupata rows "zinazokosekana", na kwamba `WHERE` upande wa null hufuta LEFT, huzuia kundi zima la makosa ya ripoti.

Tatu unazozihitaji

Kuwa mwangalifu

Kwa nini ni muhimu

Maswali yanayohusiana