Urutan pemrosesan kueri

Pengantar Oracle SQL

Hadrien Lacroix

Content Developer

Mengapa urutan pemrosesan penting?

Optimalkan kueri Anda
- Tidak ada hasil yang tidak diinginkan
- Eksekusi lebih cepat

Contoh

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

Contoh

    SELECT BillingCountry, AVG(Total) > 100) AS Average
->  FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

Contoh

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
->  WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

Contoh

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
->  GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

Contoh

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
->  HAVING AVG(Total) > 100
    ORDER BY Average DESC

Contoh

->  SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
    ORDER BY Average DESC

Contoh

    SELECT BillingCountry, AVG(Total) > 100) AS Average
    FROM Invoice
    WHERE BillingCity <> 'Paris' 
    GROUP BY BillingCountry
    HAVING AVG(Total) > 100
->  ORDER BY Average DESC

Apa yang bisa salah?

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100
ORDER BY Average DESC

Alias tidak boleh dipakai di WHERE, GROUP BY, dan HAVING
Alias boleh dipakai di ORDER BY

Apa yang bisa salah?

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100
ORDER BY Average DESC

Nilai agregat tidak bisa difilter di klausa WHERE
Nilai agregat bisa difilter di klausa HAVING

Apa yang bisa salah?

SELECT BillingCountry, 
       AVG(Total) > 100) AS Average
FROM Invoice
WHERE BillingCity <> 'Paris' 
GROUP BY BillingCountry
HAVING AVG(Total) > 100)
ORDER BY Average DESC

Baris tunggal tidak bisa difilter di klausa HAVING
Baris tunggal bisa difilter di klausa WHERE

Urutan eksekusi kueri

FROM dan JOIN: tentukan data yang dikueri
WHERE: filter baris individual
GROUP BY: kelompokkan baris
HAVING: filter grup
SELECT: pilih kolom dan terapkan fungsi pada kolom
DISTINCT: hapus duplikat
UNION, UNION ALL, INTERSECT, MINUS: terapkan operator himpunan
ORDER BY: urutkan baris

Ayo berlatih!

Pengantar Oracle SQL