Filter dengan WHERE

Meningkatkan Kinerja Kueri di SQL Server

Dean Smith

Founder, Atamai Analytics

Cara kerja WHERE

SELECT *
FROM PlayerStats
WHERE Position = 'SG'

Tabel PlayerStats

Cara kerja WHERE

SELECT *
FROM PlayerStats
WHERE Position = 'SG'

Tabel PlayerStats yang difilter

Urutan pemrosesan WHERE

SELECT PlayerName, 
      Team, 
      (DRebound+ORebound) AS TotalRebounds
FROM PlayerStats
WHERE TotalRebounds >= 1000
ORDER BY TotalRebounds DESC;

WHERE diproses sebelum SELECT

-- ERROR
Invalid column name 'TotalRebounds'.

Menggunakan subkueri

SELECT PlayerName, 
       Team, 
       TotalRebounds
FROM
     -- Awal subkueri tr
    (SELECT PlayerName, Team, 
             (DRebound+ORebound) AS TotalRebounds
     FROM PlayerStats) tr
WHERE TotalRebounds >= 1000 -- dibuat di subkueri
ORDER BY TotalRebounds DESC;

Menggunakan subkueri

SELECT PlayerName, 
       Team, 
       TotalRebounds
FROM
     -- Awal subkueri tr
    (SELECT PlayerName, Team, 
             (DRebound+ORebound) AS TotalRebounds
     FROM PlayerStats) tr
WHERE TotalRebounds >= 1000 -- dibuat di subkueri
ORDER BY TotalRebounds DESC;

PlayerName	Team	TotalRebounds
Andre Drummond	DET	1247
DeAndre Jordan	LAC	1171
Karl-Anthony Towns	MIN	1012
Dwight Howard	CHO	1012

Perhitungan pada kolom

SELECT PlayerName, 
       Team, 
       (DRebound+ORebound) AS TotalRebounds
FROM PlayerStats
WHERE (DRebound+ORebound) >= 1000
ORDER BY TotalRebounds DESC;

Perhitungan pada kolom di kondisi filter WHERE dapat memperlama kueri

PlayerName	Team	TotalRebounds
Andre Drummond	DET	1247
DeAndre Jordan	LAC	1171
Karl-Anthony Towns	MIN	1012
Dwight Howard	CHO	1012

Fungsi pada kolom

SELECT PlayerName, College, DraftYear 
FROM Players
WHERE UPPER(LEFT(College,7)) = 'GEORGIA'; 
-- penggunaan fungsi tidak perlu 
-- pada kolom filter

Menerapkan fungsi pada kolom di kondisi filter WHERE dapat memperlama kueri

PlayerName	College	DraftYear
Damien Wilkins	Georgia
Derrick Favors	Georgia Tech	2010
Iman Shumpert	Georgia Tech	2011
R.J. Hunter	Georgia State	2015
...	...	...

WHERE disederhanakan

SELECT PlayerName, College, DraftYear 
FROM Players 
        -- Tanpa perhitungan atau fungsi
WHERE College like 'Georgia%';

PlayerName	College	DraftYear
Damien Wilkins	Georgia
Derrick Favors	Georgia Tech	2010
Iman Shumpert	Georgia Tech	2011
R.J. Hunter	Georgia State	2015
...	...	...

Ringkasan

WHERE diproses sebelum SELECT
Perhitungan pada kolom di kondisi filter WHERE dapat memperlama kueri
Menerapkan fungsi pada kolom di kondisi filter WHERE dapat memperlama kueri

Ayo berlatih!

Meningkatkan Kinerja Kueri di SQL Server