Apa iku UNION, INTERSECT lan EXCEPT?

Question

Accepted Answer

Iki iku **operasi set** sing nggabungake asil saka rong query. **`UNION`** nggabungake baris (mbusak duplikat), **`INTERSECT`** mbalik baris sing ana ing loro-lorone, **`EXCEPT`** mbalik baris sing ana ing pangalane nanging ora ing nomer loro. Operasi iki gunane kanggo result set lengkap sacara vertikal (tumpuking baris), beda karo JOINs (sing nggabungake kolom horizontal).

## UNION — nggabungake asil (ilang duplikat)

```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` numpuke baris saka rong query (sing kudu duwe **kolom cocog** — jumlah padha, tipe cocog), mbusak duplikat. **`UNION ALL`** tetep duwe duplikat lan luwih cepet (ora kudu ilang duplikat) — gunakne yen sampeyan wis ngerti ora ana duplikat utawa ora peduli.

## INTERSECT — baris ing LORO-LORONE

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

## EXCEPT (utawa MINUS ing Oracle) — baris ing pancalane, ora ing nomer loro

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

## Alat lan cathetan UNION vs 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 vs JOIN

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

## Napa iki penting

Operasi set (`UNION`, `INTERSECT`, `EXCEPT`) iku fitur SQL sing berguna kanggo **nggabungake result set**, lan ngerti bab iki penting kanggo query sing kudu nggabungake, intersect, utawa kurangi data saka pirang-pirang sumber.

Sing paling asring digunakke iku **`UNION`** (nggabungake baris saka rong query — kaya nggabungake data padha saka table beda), lan pembedane praktis sing penting iku **`UNION` vs `UNION ALL`**: UNION ilang duplikat (ngasilake biaya sort/compare) dene **`UNION ALL` tetep kabeh baris lan luwih cepet** — ngerti pilih `UNION ALL` yen ilang duplikat ora perlu iku praktik performa sing berguna. `INTERSECT` (baris ing loro) lan `EXCEPT` (baris ing pancalane nanging ora ing nomer loro) nangani logika set-difference kanthi ringkes.

Ngerti **alate** (jumlah kolom cocog lan tipe cocog) lan pembedane konseptual penting antara **operasi set lan JOINs** — operasi set nggabungake asil *vertikal* (tumpuking baris saka query pisah) dene JOINs nggabungake *horizontal* (nambah kolom saka baris sing ana hubungane) — penting kanggo milih alat sing bener.
// nggabungake result set saka pirang-pirang query iku kabutuhan nyata (nggabungake data saka table padha, golek entri padha utawa beda-beda ing antara set), lan ngerti operasi, pembedane performa UNION/UNION ALL, lan carane operasi set beda saka JOINs membantu sampeyan ngucapake alat-alat iki kanthi bener lan efisien, ngerti operasi set iku praktikal lan relevan kanggo pengetahuan SQL sing nglengkapi JOINs lan ngaktifake kelas query sing duwe kaitan nggabungake utawa mbandingi result set lengkap.