UNION, INTERSECT da EXCEPT menene?

Question

Accepted Answer

Wadannan su ne **ayyukan saiti** da suke haɗa sakamakon bubugan gida biyu. **`UNION`** yana haɗa layuka (tare da kawar da kwafi), **`INTERSECT`** yana ba da layukan da suke a cikin duka biyu, **`EXCEPT`** yana ba da layukan da suke a cikin na farko amma ba a na biyu. Suna aiki akan dukkan sakamakon sannu a tsaye (tunka layuka), ba kamar JOINs ba (wanda yake haɗa ginshiki a kwance).

## UNION — haɗa sakamakon (tabbatacciyya tare)

```sql
-- combine two result sets into one, removing duplicates
SELECT name, email FROM customers
UNION
SELECT name, email FROM suppliers;
-- → all distinct names/emails from BOTH tables, no duplicates

-- UNION ALL — keeps duplicates (faster, no dedup step)
SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers;
```

`UNION` yana tunka layukan bubugan gida biyu (wanda dole ne su kasance da **ginshiki masu daidai" — sama adadi, nau'i masu daidai), tare da kawar da kwafi. **`UNION ALL`** yana ajiya kwafi da kuma sauri (babu kawar da kwafi) — yi amfani da shi lokacin da kuka san babu kwafi ko ba kuka damu ba.

## INTERSECT — layuka a DUKA

```sql
-- people who are BOTH customers AND suppliers
SELECT email FROM customers
INTERSECT
SELECT email FROM suppliers;
```

## EXCEPT (ko MINUS a Oracle) — layuka a na farko, ba a na biyu

```sql
-- customers who are NOT suppliers
SELECT email FROM customers
EXCEPT
SELECT email FROM suppliers;
```

## Buƙatun da alamar UNION da UNION ALL

```text
Requirements for set operations:
  ✓ Both queries must have the SAME number of columns
  ✓ Corresponding columns must have COMPATIBLE types
  ✓ Column names come from the FIRST query

UNION vs UNION ALL:
  UNION     → removes duplicates (extra work — sorts/compares)
  UNION ALL → keeps all rows (faster) — prefer it when dedup isn't needed
```

## UNION da JOINs

```text
JOIN  → combines tables HORIZONTALLY (adds columns from related rows)
UNION → combines results VERTICALLY (stacks rows from separate queries)
→ Different purposes: JOIN for related data across tables, UNION for merging
  similar result sets.
```

## Me yake matukarsi

Ayyukan saiti (`UNION`, `INTERSECT`, `EXCEPT`) su ne ayyukan SQL masu amfani sosai don **haɗa sakamakon saiti**, kuma fahimtar su yana da daraja don bubugan gida da suke buƙatar haɗa, tsarki, ko kawar da bayanai daga tushe da yawa.

Mafi lallai-lallai yana amfani shi ne **`UNION`** (haɗa layuka daga bubugan gida biyu — kamar haɗa bayani makawa daga teblolin daban-daban), kuma bambanci mai mahimmanci na aiki shi ne **`UNION` da `UNION ALL`**: UNION yana kawar da kwafi (tare da kawar da kamar jiya) yayin da **`UNION ALL` yana ajiya dukkan layuka kuma ya fi sauri** — sanin neman `UNION ALL` ba'a buƙatar kawar da kwafi shi ne aikin saurin aiki mai amfani. `INTERSECT` (layuka a cikin duka) da `EXCEPT` (layuka a cikin na farko amma ba a na biyu) suna daidaita implication na bambanta-saiti a gajeren yatsi.

Fahimtar **buƙatun** (daidaita yawan ginshiki da nau'in daidai) da babban bambanci na tunaninsa a tsakanin **ayyukan saiti da JOINs** — ayyukan saiti suna haɗa sakamakon a tsaye (tunka layuka daga bubugan gida daban-daban) yayin da JOINs suna haɗa a kwance (ƙara ginshiki daga layukan masu alaƙa) — shine mai muhimmanci don zaɓin kayan aikin daidai.
//Tunda haɗa sakamakon daga bubugan gida da yawa shine buƙatun aiki (haɗa bayanai daga teblolin makawa, neman abubuwan gida ko bambance-bambancen tsakanin saiti), kuma tun fahimtar ayyukan gida, bambancin aiki na UNION/UNION ALL, kuma yadda ayyukan saiti ya bambanta daga JOINs yana taimaka ka bayyana bubugun gida da tsoracinsu daidai kuma saurin aiki, sanin ayyukan saiti shine ilimin SQL na aiki mai amfani na aiki wanda ya hadde da JOINs kuma yana ba da damar azaman ayyukan da suka haɗa kasancewar sakamakon haɗa ko kwatanta dukkan sakamakon.