Self-join nima ne?

Question

Self-join nima ne?

Accepted Answer

A **self-join** shine haɗi inda teburin ya haɗi **da kansa** — mai amfani a lokacin da jere a cikin teburin suna da alaƙa da *sauran jere a cikin teburin ɗaya*, kamar hierarchies (ma'aikata da masu jiyya su) ko kiyaye jere a cikin teburin.

## Misalin gida: ma'aikata da masu jiyya

```sql
-- join employees to itself to pair each employee with their manager
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;   -- same table, two aliases!
-- → Bob | Ann, Carol | Ann, Ann | NULL
```

Maɗauki shine **table aliases** (`e` da `m`) — suna ba ka damar ɗaukar teburin ɗaya azaman "kwafi" guda biyu har ka iya haɗa jere na ma'aiki da jere na mai jiyya sa a cikin teburin ɗaya. (Amfani da LEFT JOIN yana ajiye ma'aikata waɗanda ba su da mai jiyya.)

## Wasu amfanin self-joins

```sql
-- find pairs of products with the same price
SELECT a.name, b.name, a.price
FROM products a
JOIN products b ON a.price = b.price AND a.id < b.id;   -- a.id < b.id avoids duplicates/self-pairs

-- find rows related to other rows in the same table (sequences, comparisons)
```

Self-joins ana kuma amfani dasu don kiyaye jere a cikin teburin (nemo kwafi, nau'i, ko jere masu alaƙa) — yanayin `a.id < b.id` shine wata karfo ta yau-yau don ƙare haɗi jere da kansa da ƙare kwafi biyu.

## Yadda yake aiki a fuska

```text
A self-join is just a regular join where both sides happen to be the SAME table.
The aliases create two logical "instances" of the table, so each row of one instance
can be matched against rows of the other instance.
→ Nothing special syntactically — it's a normal join, with the trick being aliases.
```

## Me yasa ya haɗi

Self-joins sune fasaha mai mahimmanci a SQL don fuskantar **alaƙa a cikin teburin ɗaya** — musamman data masu tsari ko masu nuni na kansa, wanda shine abin da ke faruwa a zahiri, don haka fahimtar su yana da mahimmanci a waɗannan yanayi.

Amfani na gida da mahimmanci shi ne **hierarchies** inda jere suka ambaci sauran jere a cikin teburin ɗaya (ma'aikata waɗanda suka ambaci masu jiyya su, categories masu parent categories, tsarin kasua, jigon shawarwari) — self-joins suna ba ka damar bugi waɗannan alaƙofi (haɗa kowane ma'aiki da mai jiyya sa) wanda mafi sauƙi bugi ba za su iya bayar.

Fahimtar cewa maɗauki mahimmanci shine **table aliases** (samar da nau'i biyu na fasali na teburin don jere su dace da juna a cikin teburin ɗaya) yana fitar da fasaha — shi kawai haɗi na yau-yau inda duka bangare biyu sune teburin ɗaya.

Self-joins suma suna fuskantar kiyaye jere a cikin teburin (nemo nau'i, kwafi, ko jere masu alaƙa, sau da yawa tare da yanayi kamar `a.id < b.id` don ƙare haɗi jere da kansa da kwafi).

Tun da data mai nuni na kansa da hierarchical yana yau-yau (org charts, category trees, alaƙar mai jiyya, kiyaye jigogi a cikin teburin), da tun da self-joins shine hanya ta gida don bugi waɗannan alaƙofi a cikin teburin ɗaya, fahimtar self-joins — cewa teburin na iya haɗi da kansa ta amfani da aliases, da abubuwan da suka saba (hierarchies, kiyaye jere) — yana da amfani, ilmin SQL da ya dace don fuskantar nau'i na bugi waɗanda suka haɗa da alaƙofi a cikin teburin waɗanda ke faruwa akai-akai a zahiri data models, da batu mai yau-yau a tambayoyin shawo da ke nuna fahimtar zurfin haɗi.