Urutan kueri

Meningkatkan Kinerja Kueri di SQL Server

Dean Smith

Founder, Atamai Analytics

Kueri gempa besar

SELECT Country, Place, Magnitude
FROM Earthquakes
WHERE Magnitude >= 9
ORDER BY Magnitude DESC;

Country	Place	Magnitude
CL	Bio-Bio; Chile	9.5
US	Southern Alaska	9.2
ID	off the west coast of northern Sumatra	9.1
JP	near the east coast of Honshu; Japan	9.1
...	...	...

Urutan sintaks

                                 -- Urutan Sintaks
SELECT Country, Place, Magnitude -- 1. SELECT
FROM Earthquakes                                            
WHERE Magnitude >= 9            
ORDER BY Magnitude DESC;

Urutan sintaks

                                 -- Urutan Sintaks
SELECT Country, Place, Magnitude -- 1. SELECT
FROM Earthquakes                 -- 2. FROM                            
WHERE Magnitude >= 9            
ORDER BY Magnitude DESC;

Urutan sintaks

                                 -- Urutan Sintaks
SELECT Country, Place, Magnitude -- 1. SELECT
FROM Earthquakes                 -- 2. FROM                            
WHERE Magnitude >= 9             -- 3. WHERE
ORDER BY Magnitude DESC;

Urutan sintaks

                                 -- Urutan Sintaks
SELECT Country, Place, Magnitude -- 1. SELECT
FROM Earthquakes                 -- 2. FROM                            
WHERE Magnitude >= 9             -- 3. WHERE
ORDER BY Magnitude DESC;         -- 4. ORDER BY

Urutan pemrosesan

                                 -- Urutan Sintaks | Urutan Pemrosesan
SELECT Country, Place, Magnitude -- 1. SELECT        
FROM Earthquakes                 -- 2. FROM        1. FROM                       
WHERE Magnitude >= 9             -- 3. WHERE
ORDER BY Magnitude DESC;         -- 4. ORDER BY

Urutan pemrosesan

                                 -- Urutan Sintaks | Urutan Pemrosesan
SELECT Country, Place, Magnitude -- 1. SELECT        
FROM Earthquakes                 -- 2. FROM        1. FROM                       
WHERE Magnitude >= 9             -- 3. WHERE       2. WHERE
ORDER BY Magnitude DESC;         -- 4. ORDER BY

Urutan pemrosesan

                                 -- Urutan Sintaks | Urutan Pemrosesan
SELECT Country, Place, Magnitude -- 1. SELECT      3. SELECT
FROM Earthquakes                 -- 2. FROM        1. FROM                       
WHERE Magnitude >= 9             -- 3. WHERE       2. WHERE
ORDER BY Magnitude DESC;         -- 4. ORDER BY

Urutan pemrosesan

                                 -- Urutan Sintaks | Urutan Pemrosesan
SELECT Country, Place, Magnitude -- 1. SELECT      3. SELECT
FROM Earthquakes                 -- 2. FROM        1. FROM                       
WHERE Magnitude >= 9             -- 3. WHERE       2. WHERE
ORDER BY Magnitude DESC;         -- 4. ORDER BY    4. ORDER BY

Kesalahan pemrosesan


SELECT Country, 
       PlaceName, 
       Magnitude
FROM LargeEarthquakes            
WHERE Strength >= 9                 
ORDER BY Magnitud DESC;

Memroses FROM

                        -- Urutan Pemrosesan
SELECT Country, 
       PlaceName, 
       Magnitude
FROM LargeEarthquakes   -- 1. FROM - tabel LargeEarthquakes tidak ada
WHERE Strength >= 9                 
ORDER BY Magnitud DESC;

--------------------------------------------------------------------------------
-- ERROR
Invalid object name 'LargeEarthquakes'.

Memroses WHERE

                        -- Urutan Pemrosesan
SELECT Country, 
       PlaceName, 
       Magnitude
FROM Earthquakes                              
WHERE Strength >= 9     -- 2. WHERE - kolom Strength tidak ada 
ORDER BY Magnitud DESC;

--------------------------------------------------------------------------------
-- ERROR
Invalid column name 'Strength'.

Memroses SELECT

                        -- Urutan Pemrosesan
SELECT Country, 
       PlaceName,       -- 3. SELECT - kolom PlaceName tidak ada
       Magnitude 
FROM Earthquakes                              
WHERE Magnitude >= 9                 
ORDER BY Magnitud DESC;

--------------------------------------------------------------------------------
-- ERROR
Invalid column name 'PlaceName'.

Memroses ORDER BY

                        -- Urutan Pemrosesan
SELECT Country, 
       Place, 
       Magnitude
FROM Earthquakes                              
WHERE Magnitude >= 9                 
ORDER BY Magnitud DESC;-- 4. ORDER BY - salah eja kolom

________________________________________________________

-- ERROR
Invalid column name 'Magnitud'.

Tanpa error

SELECT Country, 
       Place, 
       Magnitude   
FROM Earthquakes                
WHERE Magnitude >= 9      
ORDER BY Magnitude DESC;

Country	Place	Magnitude
CL	Bio-Bio; Chile	9.5
US	Southern Alaska	9.2
ID	off the west coast of northern Sumatra	9.1
JP	near the east coast of Honshu; Japan	9.1
...	...	...

Urutan pemrosesan logis

1.  FROM

2.  ON

3.  JOIN

4.  WHERE

5.  GROUP BY

6.  HAVING


7.  SELECT


8.  DISTINCT

9.  ORDER BY

10. TOP

Ayo berlatih!

Meningkatkan Kinerja Kueri di SQL Server