Menene Common Table Expressions (CTEs)?

Question

Accepted Answer

**CTE** (Common Table Expression), wanda aka bayyana tare da **`WITH`**, shi ne suna na sakamakon nata mai jiya da kake iya danganta a cikin tambaya. CTEs suna sa hadaddun tambayoyi zama **mai karanta** ta hanyar raba su zuwa suna da abubuwan tunani - kuma suna ba da damar **maimaita** tambayoyi (don tsarin sadarwa).

## CTE mai saufi - suna wa subquery don karanta

```sql
WITH active_users AS (              -- define a named temporary result set
    SELECT id, name FROM users WHERE active = true
)
SELECT * FROM active_users          -- reference it like a table
WHERE name LIKE 'A%';
```

`WITH ... AS (...)` ya bayyana CTE - tambaya mai suna da kake danganta daga gida. Yana kama da subquery, amma **mai suna da karantawa**, musamman ga hadaddun tambayoyi.

## CTEs masu yawa - daituwar matakala na tunani

```sql
WITH
  monthly_sales AS (
    SELECT month, SUM(amount) AS total FROM orders GROUP BY month
  ),
  avg_sales AS (
    SELECT AVG(total) AS avg_total FROM monthly_sales   -- a CTE can reference another
  )
SELECT m.month, m.total
FROM monthly_sales m, avg_sales a
WHERE m.total > a.avg_total;        -- months above the average
```

CTEs masu yawa suna karya hadaddun tambaya zuwa matakala masu karantawa, jere - kowannensu yana gina akan na jiya. Wannan a bayyane sosai fiye da subqueries da su aka jika sosai.

## CTEs da subqueries (karantawa)

```text
Nested subqueries → hard to read when deeply nested
CTEs → name each step, read top-to-bottom like a sequence → much clearer
→ CTEs are often preferred for COMPLEX queries purely for readability/maintainability.
```

## CTEs na maimaita - don tsarin sadarwa

```sql
-- traverse a hierarchy (e.g. all descendants of a category, an org chart)
WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id FROM employees WHERE id = 1   -- base case (the top)
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id               -- recursive step
)
SELECT * FROM subordinates;          -- the whole tree under employee 1
```

**Recursive CTEs** (`WITH RECURSIVE`) suna wucewa a cikin data na tsarin sadarwa/bishiyar (tsarin ma'aikaci, bishiyar nau'i, bill-of-materials, hanyoyin zane) - wani abu wanda a bayyane jiya a SQL.

## Yace take da muhimmanci

CTEs sune wani muhimmin bangare na SQL don **karantawa da maimaita**, kuma fahimta su yana da akwa don rubuta hadaddun tambayoyi da zama masu jiya kuma don shawo kan data na tsarin sadarwa.

Babbar fa'ida ta kullun ita ce **karantawa**: hadaddun tambayoyi tare da matakala masu yawa na subqueries suna zama mai wahala a karanta kuma a kula, yayin da CTEs suke ba da damar raba tambaya zuwa suna da matakala na tunani, jere (karanta sama zuwa ƙasa kamar jerin abubuwan aiki), da kakkafa abin karanta sosai - saboda haka, CTEs sune gibi fiye da subqueries na jika sosai ga hadaddun tambayoyi, suna sa su ta muhimmiyar kayan aiki don rubuta SQL da aka kula.

Daidai mahimmanci ita ce ikon **recursive CTE** (`WITH RECURSIVE`), wanda ke ba da damar bugun tambayoyi na **data na tsarin sadarwa da bishiyar** (tsarin ma'aikaci, bishiyar nau'i, bill-of-materials, wucewa a zane) - nau'i na tambayoyi wanda a bayyane jiya ai tsiya a standard SQL, suna sa recursive CTEs ta muni ga wucewa a tsarin sadarwa.

Fahimta duka abubuwa - CTEs don karya hadaddun tambayoyi zuwa matakala mai karantawa, kuma recursive CTEs ga wucewa a tsarin sadarwa - suna rufe abin fa'ida na aiki.
\