SQL dasar: JOIN, WHERE vs HAVING, CTE, dan ROW_NUMBER

Sebelum mulai: jalankan setup lab sekali (ROADMAP, "Hari 4"). Semua contoh memakai tabel dbo.Category (6 baris) dan dbo.Product (100.000 baris) di database ProductCatalogLab. Anda sudah biasa menulis SQL dari Node (pg, knex, atau Prisma), jadi fokus lesson ini adalah jebakan yang sering ditanyakan, bukan sintaks dasar.

1. INNER, LEFT, RIGHT JOIN

Definisi join type: ^[1]

-- Category yang belum punya Product (pola anti-join)
SELECT c.Name
FROM dbo.Category AS c
LEFT JOIN dbo.Product AS p ON p.CategoryId = c.Id
WHERE p.Id IS NULL;

Dua jebakan yang sering muncul:

• ON vs WHERE di outer join. Untuk INNER JOIN posisinya tidak berpengaruh, tapi untuk OUTER JOIN bisa mengubah hasil, "because the predicates in the ON clause are applied to the table before the join, whereas the WHERE clause is semantically applied to the result of the join." ^[1] Menaruh WHERE p.Stock > 0 setelah LEFT JOIN membuang baris yang kolom Product-nya NULL, jadi hasilnya berubah menjadi seperti INNER JOIN.
• NULL tidak cocok dengan NULL. "When there are null values in the columns of the tables being joined, the null values don't match each other." ^[2]

2. GROUP BY, WHERE vs HAVING

SELECT c.Name, COUNT(*) AS InStockCount
FROM dbo.Product AS p
INNER JOIN dbo.Category AS c ON c.Id = p.CategoryId
WHERE p.Stock > 0            -- menyaring BARIS, sebelum grouping
GROUP BY c.Name
HAVING COUNT(*) >= 1000      -- menyaring GRUP, setelah agregasi
ORDER BY InStockCount DESC;  -- alias boleh di sini

• WHERE: "SQL removes rows that don't meet the conditions in the WHERE clause before it performs any grouping operation." HAVING: "SQL uses the HAVING clause to filter groups in the result set." ^[3]
• Kolom non-agregat di SELECT harus ada di GROUP BY, dan "The GROUP BY clause doesn't order the result set", jadi tetap tulis ORDER BY. ^[3]

Semua jebakan di atas berasal dari urutan logis pemrosesan SELECT ^[4]:

FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → TOP

Alias dibuat di tahap SELECT, jadi belum dikenal di WHERE, GROUP BY, dan HAVING, tapi sudah bisa dipakai di ORDER BY. Window function seperti ROW_NUMBER() juga dihitung di tahap SELECT, sehingga tidak bisa difilter langsung di WHERE (bagian 4).

3. Subquery vs CTE

-- Correlated subquery: subquery memakai p.CategoryId dari query luar
SELECT p.Name, p.Price
FROM dbo.Product AS p
WHERE p.Price > (SELECT AVG(p2.Price) FROM dbo.Product AS p2 WHERE p2.CategoryId = p.CategoryId);

-- CTE: langkah yang sama diberi nama, lalu di-JOIN
WITH CategoryAvg AS (
    SELECT CategoryId, AVG(Price) AS AvgPrice FROM dbo.Product GROUP BY CategoryId
)
SELECT p.Name, p.Price
FROM dbo.Product AS p
INNER JOIN CategoryAvg AS a ON a.CategoryId = p.CategoryId
WHERE p.Price > a.AvgPrice;

• CTE adalah "a temporary named result set" yang hanya hidup di satu statement. Statement sebelumnya di batch "must be followed by a semicolon." ^[6]
• Bukan cache: "Query results from common table expressions aren't materialized. Each outer reference to the named result set requires the defined query to be re-executed." Butuh hasil yang dipakai berkali-kali? "consider using a temporary object instead." ^[6]
• Performa umumnya setara: "there's usually no performance difference between a statement that includes a subquery and a semantically equivalent version that doesn't." ^[5] Jadi pilih yang paling mudah dibaca: CTE untuk query bertahap atau rekursif, subquery untuk filter pendek (IN, EXISTS).
• Jebakan NULL: NOT IN (subquery) bisa mengembalikan nol baris bila subquery berisi NULL. Untuk "yang tidak punya", pakai NOT EXISTS atau pola LEFT JOIN … IS NULL di atas. ^[5]

4. ROW_NUMBER() OVER (PARTITION BY …): top-N per grup

ROW_NUMBER "returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition." ORDER BY di dalam OVER "is required"; tanpa PARTITION BY, seluruh hasil dianggap satu grup. ^[7]

-- Product termurah per Category (pola yang sama dipakai untuk soal "3 termahal")
WITH Ranked AS (
    SELECT p.CategoryId, p.Name, p.Price,
           ROW_NUMBER() OVER (PARTITION BY p.CategoryId ORDER BY p.Price ASC, p.Id) AS rn
    FROM dbo.Product AS p
)
SELECT CategoryId, Name, Price
FROM Ranked
WHERE rn = 1;   -- filter di query luar

• Kenapa dibungkus CTE? WHERE diproses sebelum SELECT, jadi nomor barisnya belum ada. Contoh resmi Microsoft juga membungkus ROW_NUMBER di CTE lalu memfilter WHERE RowNumber BETWEEN 50 AND 60. ^[7]
• Nilai seri: "ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5). RANK provides the same numeric value for ties (for example 1, 2, 2, 4, 5)." DENSE_RANK memberi 1, 2, 2, 3. ROW_NUMBER "is nondeterministic" bila kolom ORDER BY tidak unik, jadi tambahkan pemecah seri (p.Id) supaya hasilnya stabil. ^[7]

Padanan Node: Prisma tidak punya API untuk window function, jadi di proyek Node query seperti ini biasanya ditulis sebagai raw SQL ($queryRaw, pg, atau knex raw). Di .NET, EF Core juga bisa menjalankan raw SQL (lesson 0020).

5. Selipan desain: normalisasi, constraint, view

Normalisasi adalah "the process of organizing data in a database ... by eliminating redundancy and inconsistent dependency." Bentuk ke-2: "Create separate tables for sets of values that apply to multiple records. Relate these tables with a foreign key." Bentuk ke-3 "is considered the highest level necessary for most applications." ^[10] Itu sebabnya nama Category ada di tabel Category, dan Product hanya menyimpan CategoryId. Harganya: query butuh JOIN. Denormalisasi kadang dipilih untuk performa, "in spite of the dangers of redundancy, such as inconsistent updates." ^[2]

Kenapa tidak cukup di Service? Constraint adalah "rules that the Database Engine enforces for you" ^[9], berlaku untuk setiap INSERT/UPDATE dari klien mana pun: aplikasi lain, skrip, atau Stored Procedure. Untuk nama unik ada juga race condition: dua request bisa sama-sama lolos pengecekan "nama belum ada" di Service, lalu UNIQUE constraint menolak yang kedua. Service tetap memvalidasi supaya client mendapat pesan 400 yang jelas, bukan error database.

View: "A view is a virtual table whose contents are defined by a query. ... Unless indexed, a view does not exist as a stored set of data values in a database." Dipakai untuk menyederhanakan query dan sebagai mekanisme keamanan. ^[11] ORDER BY di definisi view "does not guarantee ordered results when the view is queried", jadi urutkan di query pemakainya. ^[12]

Padanan Node: @unique di schema Prisma juga menjadi UNIQUE constraint di database. Validasi Zod/class-validator di Service hanya lapisan pertama.

6. Hands-on (±15 menit)

1. Pastikan container jalan (docker start sql2022) dan setup lab sudah dijalankan (hasil akhir: 6 Category, 100000 Product).
2. Buka dotnet/lab/0019-sql-dasar.sql di Azure Data Studio atau VS Code mssql (atau salin dari blok di bawah), koneksi localhost,1433, user sa.
3. Jalankan per bagian (pilih blok sampai GO, lalu Run). Sebelum menjalankan, tebak dulu hasilnya:
   • Bagian 1: di query mana Category "Mainan" muncul?
   • Bagian 2: Category mana yang hilang karena HAVING?
   • Bagian 3: apakah kedua angka AboveCategoryAvg sama?
   • Bagian 5: baca nama constraint di setiap pesan error (2627 untuk UNIQUE, 547 untuk CHECK dan FK).

Lewat sqlcmd di dalam container: docker cp dotnet/lab/0019-sql-dasar.sql sql2022:/tmp/0019.sql lalu docker exec sql2022 /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa -P 'Dev_Passw0rd!' -C -i /tmp/0019.sql. Error di bagian 5 memang disengaja.

-- Lesson 0019 · SQL dasar: JOIN, WHERE vs HAVING, subquery vs CTE, ROW_NUMBER, constraint, view.
-- Jalankan setelah dotnet/lab/00-setup-product-catalog-lab.sql. Boleh dijalankan ulang.
-- Jalankan per bagian (blok sampai GO), lalu baca hasilnya sebelum lanjut.

USE ProductCatalogLab;
SET NOCOUNT ON;
GO

-- 1. JOIN ------------------------------------------------------------------------------------
-- Di lab, semua Category punya Product. Tambahkan sementara satu Category kosong supaya beda
-- INNER dan LEFT terlihat. Transaction + ROLLBACK membatalkannya di akhir bagian ini
-- (transaction dibahas di lesson 0021).
BEGIN TRANSACTION;
INSERT INTO dbo.Category (Name) VALUES (N'Mainan');

-- INNER JOIN: hanya pasangan yang cocok. Mainan tidak muncul.
SELECT c.Name, COUNT(*) AS ProductCount
FROM dbo.Category AS c
INNER JOIN dbo.Product AS p ON p.CategoryId = c.Id
GROUP BY c.Name;

-- LEFT JOIN: semua baris tabel kiri. Mainan muncul dengan 0, karena COUNT(p.Id) tidak menghitung NULL.
SELECT c.Name, COUNT(p.Id) AS ProductCount
FROM dbo.Category AS c
LEFT JOIN dbo.Product AS p ON p.CategoryId = c.Id
GROUP BY c.Name;

-- RIGHT JOIN: hasil sama dengan LEFT JOIN di atas, hanya urutan tabel yang dibalik.
SELECT c.Name, COUNT(p.Id) AS ProductCount
FROM dbo.Product AS p
RIGHT JOIN dbo.Category AS c ON p.CategoryId = c.Id
GROUP BY c.Name;

-- Pola anti-join: Category yang belum punya Product.
SELECT c.Name
FROM dbo.Category AS c
LEFT JOIN dbo.Product AS p ON p.CategoryId = c.Id
WHERE p.Id IS NULL;

ROLLBACK TRANSACTION;
GO

-- 2. GROUP BY, WHERE vs HAVING ---------------------------------------------------------------
-- WHERE menyaring baris SEBELUM grouping, HAVING menyaring grup SETELAH agregasi.
-- Koleksi Langka (20 Product) hilang karena HAVING.
SELECT c.Name, COUNT(*) AS InStockCount, AVG(p.Price) AS AvgPrice
FROM dbo.Product AS p
INNER JOIN dbo.Category AS c ON c.Id = p.CategoryId
WHERE p.Stock > 0
GROUP BY c.Name
HAVING COUNT(*) >= 1000
ORDER BY InStockCount DESC;   -- alias SELECT boleh di ORDER BY, tidak boleh di WHERE/GROUP BY
GO

-- 3. Subquery vs CTE -------------------------------------------------------------------------
-- Pertanyaan: berapa Product yang harganya di atas rata-rata Category-nya?
-- a) Correlated subquery: subquery memakai p.CategoryId dari query luar.
SELECT COUNT(*) AS AboveCategoryAvg
FROM dbo.Product AS p
WHERE p.Price > (SELECT AVG(p2.Price) FROM dbo.Product AS p2 WHERE p2.CategoryId = p.CategoryId);

-- b) CTE: langkah "rata-rata per Category" diberi nama, lalu di-JOIN. Hasilnya harus sama.
--    Statement sebelum WITH harus diakhiri titik koma.
WITH CategoryAvg AS (
    SELECT CategoryId, AVG(Price) AS AvgPrice
    FROM dbo.Product
    GROUP BY CategoryId
)
SELECT COUNT(*) AS AboveCategoryAvg
FROM dbo.Product AS p
INNER JOIN CategoryAvg AS a ON a.CategoryId = p.CategoryId
WHERE p.Price > a.AvgPrice;
GO

-- 4. ROW_NUMBER, RANK, DENSE_RANK ------------------------------------------------------------
-- Product termurah per Category: nomor mulai lagi dari 1 di setiap partisi.
WITH Ranked AS (
    SELECT p.CategoryId, p.Name, p.Price,
           ROW_NUMBER() OVER (PARTITION BY p.CategoryId ORDER BY p.Price ASC, p.Id) AS rn
    FROM dbo.Product AS p
)
SELECT CategoryId, Name, Price
FROM Ranked
WHERE rn = 1          -- filter di query luar: WHERE diproses sebelum window function
ORDER BY CategoryId;

-- Beda perilaku saat ada nilai seri (data kecil, harga 300 muncul dua kali).
SELECT Name, Price,
       ROW_NUMBER() OVER (ORDER BY Price DESC) AS RowNumber,   -- 1, 2, 3, 4
       RANK()       OVER (ORDER BY Price DESC) AS RankNo,      -- 1, 2, 2, 4
       DENSE_RANK() OVER (ORDER BY Price DESC) AS DenseRankNo  -- 1, 2, 2, 3
FROM (VALUES (N'A', 500), (N'B', 300), (N'C', 300), (N'D', 100)) AS t(Name, Price);
GO

-- 5. Constraint ------------------------------------------------------------------------------
-- Setiap INSERT di bawah SENGAJA gagal. Jalankan satu per satu dan baca pesan error-nya.
-- UNIQUE (UQ_Product_Name): nama sudah dipakai. Error 2627.
INSERT INTO dbo.Product (Name, Price, Stock, CategoryId, CreatedAt)
VALUES (N'Product 000001', 10, 1, 1, SYSDATETIME());
GO
-- CHECK (CK_Product_Price): Price harus > 0. Error 547.
INSERT INTO dbo.Product (Name, Price, Stock, CategoryId, CreatedAt)
VALUES (N'Harga Nol', 0, 1, 1, SYSDATETIME());
GO
-- CHECK (CK_Product_Stock): Stock harus >= 0. Error 547.
INSERT INTO dbo.Product (Name, Price, Stock, CategoryId, CreatedAt)
VALUES (N'Stok Minus', 10, -1, 1, SYSDATETIME());
GO
-- FOREIGN KEY (FK_Product_Category): Category 999 tidak ada. Error 547.
INSERT INTO dbo.Product (Name, Price, Stock, CategoryId, CreatedAt)
VALUES (N'Tanpa Category', 10, 1, 999, SYSDATETIME());
GO

-- 6. View ------------------------------------------------------------------------------------
-- View = query bernama (tabel virtual). Data tetap di tabel Product dan Category.
CREATE OR ALTER VIEW dbo.vProductWithCategory
AS
SELECT p.Id, p.Name, p.Price, p.Stock, c.Name AS CategoryName
FROM dbo.Product AS p
INNER JOIN dbo.Category AS c ON c.Id = p.CategoryId;
GO

-- ORDER BY ditulis di query yang memakai view, bukan di definisi view.
SELECT TOP (5) Name, Price, CategoryName
FROM dbo.vProductWithCategory
WHERE CategoryName = N'Buku'
ORDER BY Price DESC;
GO

7. Latihan tulis query

Tulis sendiri di editor SQL, jalankan di lab, baru buka jawaban. Tidak dinilai otomatis; bandingkan hasil dan bentuk query-nya.

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 AS p
    INNER JOIN dbo.Category AS c ON c.Id = p.CategoryId
)
SELECT CategoryName, Name, Price, rn
FROM Ranked
WHERE rn <= 3
ORDER BY CategoryName, rn;

SELECT c.Name, COUNT(*) AS OutOfStockCount
FROM dbo.Product AS p
INNER JOIN dbo.Category AS c ON c.Id = p.CategoryId
WHERE p.Stock = 0
GROUP BY c.Name
HAVING COUNT(*) >= 10
ORDER BY OutOfStockCount DESC;

Ringkasan untuk dicetak: Reference: SQL dasar · Istilah: GLOSSARY.md (bagian "Database (SQL Server)")

Ada yang belum jelas? Tanyakan ke saya (agent) di terminal, misalnya: "kapan RANK lebih tepat daripada DENSE_RANK?", "tunjukkan CTE rekursif untuk kategori bertingkat", atau "kenapa NOT IN dengan NULL mengembalikan nol baris?"

Sumber: [1] FROM clause plus JOIN, APPLY, PIVOT · [2] Joins (SQL Server) · [3] SELECT - GROUP BY · [4] SELECT: logical processing order · [5] Subqueries (SQL Server) · [6] WITH common_table_expression · [7] ROW_NUMBER · [8] Primary and foreign key constraints · [9] Unique constraints and check constraints · [10] Description of the database normalization basics · [11] Views · [12] CREATE VIEW