Hizi ni set operations zinazounganisha matokeo ya maswali mawili. UNION huunganisha safu (kuondoa nakala-zinazojirudia), INTERSECT hurudisha safu zilizo katika zote mbili, EXCEPT hurudisha safu zilizo katika la kwanza lakini si la pili. Hufanya kazi kwenye seti nzima za matokeo kiwima (kupanga safu juu kwa juu), tofauti na JOINs (zinazounganisha safuwima kimlalo).
-- 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 hupanga safu za maswali mawili juu kwa juu (ambayo lazima yawe na safuwima zinazolingana — idadi sawa, aina zinazoendana), kuondoa nakala-zinazojirudia. UNION ALL huhifadhi nakala na ni haraka zaidi (hakuna deduplication) — itumie pale unapojua hakuna nakala au hujali.
-- people who are BOTH customers AND suppliers
SELECT email FROM customers
INTERSECT
SELECT email FROM suppliers;
-- customers who are NOT suppliers
SELECT email FROM customers
EXCEPT
SELECT email FROM suppliers;
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
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.
Set operations (UNION, INTERSECT, EXCEPT) ni vipengele vya SQL vyenye manufaa kwa kuunganisha seti za matokeo, na kuzielewa ni cha thamani kwa maswali yanayohitaji kuunganisha, kupishana, au kutoa data kutoka vyanzo vingi.
Inayotumika zaidi ni UNION (kuunganisha safu kutoka maswali mawili — kama kuunganisha data inayofanana kutoka jedwali tofauti), na tofauti muhimu ya kivitendo ni UNION dhidi ya UNION ALL: UNION huondoa nakala-zinazojirudia (ikipata gharama ya kupanga/kulinganisha) wakati UNION ALL huhifadhi safu zote na ni haraka zaidi — kujua kupendelea UNION ALL pale deduplication haihitajiki ni utendaji bora wa utendaji. INTERSECT (safu katika zote mbili) na EXCEPT (safu katika la kwanza lakini si la pili) hushughulikia mantiki ya set-difference kwa ufupi.
Kuelewa mahitaji (idadi ya safuwima zinazolingana na aina zinazoendana) na tofauti muhimu ya kidhana kati ya set operations na JOINs — set operations huunganisha matokeo kiwima (kupanga safu juu kwa juu kutoka maswali tofauti) wakati JOINs huunganisha kimlalo (kuongeza safuwima kutoka safu zinazohusiana) — ni muhimu kwa kuchagua zana sahihi.
Kwa kuwa kuunganisha seti za matokeo kutoka maswali mengi ni hitaji halisi (kuunganisha data kutoka jedwali zinazofanana, kupata maingizo ya pamoja au tofauti kati ya seti), na kwa kuwa kuelewa operations, tofauti ya utendaji wa UNION/UNION ALL, na jinsi set operations zinavyotofautiana na JOINs hukusaidia kueleza mahitaji haya kwa usahihi na ufanisi, kujua set operations ni maarifa ya SQL yenye manufaa, yanayohusika kivitendo ambayo huongezea JOINs na huwezesha aina ya maswali yanayohusisha kuunganisha au kulinganisha seti nzima za matokeo.