SQL dasar: JOIN, WHERE vs HAVING, CTE, ROW_NUMBER, constraint
Ringkasan satu halaman. Dari Lesson 0019. Skrip lab: dotnet/lab/0019-sql-dasar.sql.
Urutan logis SELECT [1]
FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → TOP
- Alias SELECT tidak dikenal di WHERE / GROUP BY / HAVING, tapi bisa di ORDER BY.
- Window function (
ROW_NUMBER) dihitung di SELECT → filter di query luar (CTE / derived table).
JOIN [2]
| JOIN | Hasil |
|---|---|
INNER (default) | hanya pasangan cocok; baris tanpa pasangan dibuang |
LEFT | semua baris kiri; kolom kanan NULL bila tak ada pasangan |
RIGHT | semua baris kanan (= LEFT dengan tabel dibalik) |
FULL / CROSS | semua baris dua sisi / semua kombinasi |
-- anti-join: Category tanpa Product
SELECT c.Name FROM dbo.Category c
LEFT JOIN dbo.Product p ON p.CategoryId = c.Id
WHERE p.Id IS NULL;- Outer join: predikat di ON diterapkan sebelum join, di WHERE setelah join.
WHEREpada kolom tabel kanan mengubah LEFT JOIN menjadi seperti INNER. - NULL tidak cocok dengan NULL saat join. [3]
GROUP BY, WHERE vs HAVING [4]
SELECT c.Name, COUNT(*) AS InStockCount
FROM dbo.Product p JOIN dbo.Category c ON c.Id = p.CategoryId
WHERE p.Stock > 0 -- baris, sebelum grouping
GROUP BY c.Name
HAVING COUNT(*) >= 1000 -- grup, setelah agregasi
ORDER BY InStockCount DESC; -- GROUP BY tidak mengurutkanSubquery vs CTE [5] [6]
- Performa umumnya setara → pilih yang paling mudah dibaca. Cek dengan execution plan (lesson 0023).
- CTE = result set bernama dalam satu statement; tidak di-materialize (dieksekusi ulang per referensi). Butuh dipakai berkali-kali → temp table.
- CTE: query bertahap, rekursif. Statement sebelum
WITHharus diakhiri;. - Correlated subquery memakai kolom query luar.
NOT IN+ NULL bisa memberi nol baris → pakaiNOT EXISTS.
Top-N per grup [7]
WITH Ranked AS (
SELECT c.Name AS CategoryName, p.Name, p.Price,
ROW_NUMBER() OVER (PARTITION BY p.CategoryId ORDER BY p.Price DESC, p.Id) AS rn
FROM dbo.Product p JOIN dbo.Category c ON c.Id = p.CategoryId
)
SELECT CategoryName, Name, Price, rn FROM Ranked
WHERE rn <= 3 ORDER BY CategoryName, rn;| Fungsi | Nilai seri (contoh) | Pakai bila |
|---|---|---|
ROW_NUMBER | 1, 2, 3, 4 | tepat N baris; tambahkan pemecah seri (Id) supaya deterministik |
RANK | 1, 2, 2, 4 | yang seri ikut, peringkat melompat |
DENSE_RANK | 1, 2, 2, 3 | yang seri ikut, peringkat tidak melompat |
Normalisasi, constraint, view
- Normalisasi: hilangkan redundansi; nilai yang berulang dipindah ke tabel sendiri + FK. 3NF cukup untuk kebanyakan aplikasi. Denormalisasi = tukar redundansi dengan JOIN lebih sedikit. [8]
| Constraint | Aturan Product | Index |
|---|---|---|
| PRIMARY KEY [9] | Id unik, NOT NULL, satu per tabel | unique index; clustered bila belum ada clustered index |
| FOREIGN KEY [9] | CategoryId harus ada di Category | tidak otomatis (lesson 0022) |
| UNIQUE [10] | nama Product unik | unique non-clustered index |
| CHECK [10] | Price > 0, Stock ≥ 0 | —; NULL lolos (UNKNOWN) → kolom NOT NULL |
- Kenapa di database juga: engine menegakkan untuk semua klien; race condition dua request lolos cek Service; Service tetap validasi untuk pesan 400 yang ramah.
- Error saat dilanggar: 2627 (PK/UNIQUE), 547 (CHECK/FK).
- View = tabel virtual dari query, data tetap di tabel (kecuali indexed view). ORDER BY di definisi view tidak menjamin urutan. [11]
Jawaban 60–90 detik
CTE dengan ROW_NUMBER() OVER (PARTITION BY CategoryId ORDER BY Price DESC, Id): nomor mulai dari 1 per Category, Id sebagai pemecah seri.
JOIN ke Category untuk nama. Filter rn <= 3 di query luar karena WHERE diproses sebelum window function. Harga seri harus ikut → DENSE_RANK/RANK.
Aturan Product dijaga juga oleh UNIQUE/CHECK/FK karena engine menegakkannya untuk semua klien dan menutup race condition; Service tetap memvalidasi untuk pesan error.
Sumber: [1] SELECT: logical processing order · [2] FROM clause plus JOIN · [3] Joins · [4] SELECT - GROUP BY · [5] Subqueries · [6] WITH common_table_expression · [7] ROW_NUMBER · [8] Database normalization basics · [9] Primary and foreign key constraints · [10] Unique and check constraints · [11] Views