Bagaimanakah saya menggunakan subqueries dalam SQL untuk membuat pertanyaan kompleks?

Artikel ini menerangkan subqueries SQL (pertanyaan bersarang), mempamerkan penggunaannya dalam Pilih, dari, dan di mana klausa. Ia menyoroti faedah, perangkap biasa (subqueries berkorelasi, penggunaan tidak cekap), dan teknik pengoptimuman (bergabung, CTE, wujud

Cara menggunakan subqueries di SQL untuk membuat pertanyaan kompleks

Subqueries, juga dikenali sebagai pertanyaan bersarang, adalah pertanyaan yang tertanam dalam pertanyaan SQL yang lain. Mereka sangat berguna untuk mewujudkan pertanyaan kompleks yang sukar atau mustahil untuk dicapai dengan satu pertanyaan mudah. Mereka membolehkan anda memecahkan masalah yang kompleks ke bahagian yang lebih kecil dan lebih mudah diurus. Subqueries boleh digunakan dalam pelbagai klausa pertanyaan utama, termasuk SELECT , FROM , WHERE , dan HAVING klausa.

Mari kita gambarkan dengan contoh:

Contoh 1: Subquery dalam klausa WHERE:

Katakan anda mempunyai dua jadual: Customers (CustomerID, Nama, Bandar) dan Orders (OrderID, CustomerID, OrderDate, Totalamount). Anda ingin mencari nama pelanggan yang telah membuat pesanan dengan jumlah yang lebih besar daripada jumlah pesanan purata.

 <code class="sql">SELECT Name FROM Customers WHERE CustomerID IN (SELECT CustomerID FROM Orders GROUP BY CustomerID HAVING AVG(TotalAmount) > (SELECT AVG(TotalAmount) FROM Orders));</code>

Pertanyaan ini menggunakan subquery dalam klausa WHERE untuk mencari CustomerID yang memenuhi kriteria yang ditentukan sebelum memilih nama yang sepadan dari jadual Customers . Subquery paling dalam mengira jumlah pesanan purata di semua pesanan.

Contoh 2: Subquery dalam klausa pilih:

Bayangkan anda mahu mengambil nama pelanggan bersama -sama dengan jumlah yang mereka belanjakan.

 <code class="sql">SELECT c.Name, (SELECT SUM(TotalAmount) FROM Orders o WHERE o.CustomerID = c.CustomerID) AS TotalSpent FROM Customers c;</code>

Di sini, subquery dalam klausa SELECT mengira TotalSpent untuk setiap pelanggan.

Contoh 3: Subquery dalam klausa dari (menggunakan CTE - Ekspresi Jadual Biasa - untuk kebolehbacaan):

Untuk kebolehbacaan yang lebih baik, terutamanya dengan subqueries yang kompleks, menggunakan Ekspresi Jadual Biasa (CTE) adalah disyorkan. Mari cari pelanggan yang membuat pesanan pada bulan lalu.

 <code class="sql">WITH RecentOrders AS ( SELECT CustomerID FROM Orders WHERE OrderDate >= DATE('now', '-1 month') ) SELECT c.Name FROM Customers c JOIN RecentOrders ro ON c.CustomerID = ro.CustomerID;</code>

Contoh ini menggunakan CTE, RecentOrders , yang merupakan subquery yang ditakrifkan sebelum pertanyaan utama. Pertanyaan utama kemudian menyertai Customers dengan RecentOrders untuk mendapatkan hasil yang diinginkan. Pendekatan ini meningkatkan kebolehbacaan dengan ketara berbanding dengan terus membenamkan subquery dalam klausa FROM .

Apakah perangkap biasa untuk dielakkan apabila menggunakan subqueries dalam SQL?

Walaupun subqueries berkuasa, beberapa perangkap boleh membawa kepada masalah prestasi atau hasil yang salah:

• Subqueries berkorelasi: subqueries ini bergantung pada data pertanyaan luar. Walaupun kadang -kadang perlu, mereka boleh menjadi lebih perlahan daripada subqueries yang tidak dikawal kerana pertanyaan dalaman dilaksanakan berulang kali untuk setiap baris dalam pertanyaan luar. Mengoptimumkan dengan teliti memeriksa jika korelasi benar -benar diperlukan.
• Subqueries yang tidak cekap: subqueries yang mengimbas jadual besar tanpa pengindeksan yang betul boleh menjadi sangat perlahan. Pastikan indeks yang sesuai disediakan di lajur yang digunakan dalam klausa Subquery's WHERE .
• Penggunaan yang tidak betul IN vs EXISTS : EXISTS umumnya lebih cekap daripada IN memeriksa kewujudan baris, terutama dengan dataset yang besar. EXISTS berhenti mencari sebaik sahaja perlawanan dijumpai, sementara IN perlu memproses semua baris.
• N 1 Masalah: Ini berlaku apabila subquery dilaksanakan sekali untuk setiap baris dalam pertanyaan luar, yang membawa kepada kesesakan prestasi yang signifikan. Sering kali, bergabung atau CTE dapat menyelesaikannya.

Bagaimanakah saya dapat mengoptimumkan prestasi pertanyaan SQL yang menggunakan subqueries?

Mengoptimumkan subqueries melibatkan beberapa strategi:

• Gunakan indeks: Pastikan indeks yang sesuai wujud pada jadual dan lajur yang terlibat dalam subqueries, terutama yang ada di dalam klausa WHERE .
• Tulis semula subqueries sebagai bergabung: Dalam banyak kes, subqueries boleh ditulis semula menggunakan gabungan, yang sering lebih efisien.
• Penggunaan wujud bukannya dalam: Seperti yang dinyatakan sebelum ini, EXISTS umumnya lebih cekap daripada IN memeriksa kewujudan.
• Gunakan Ekspresi Jadual Biasa (CTE): CTE meningkatkan kebolehbacaan dan kadang -kadang boleh membantu pengoptimal pangkalan data menjana pelan pelaksanaan yang lebih cekap.
• Menganalisis pelan pelaksanaan: Gunakan alat sistem pangkalan data anda (misalnya, EXPLAIN PLAN di Oracle, EXPLAIN dalam MySQL dan PostgreSQL, SQL Server Profiler) untuk menganalisis pelan pelaksanaan pertanyaan anda dan mengenal pasti kesesakan.
• Elakkan subqueries berkorelasi (jika boleh): Cuba tulis semula subqueries berkorelasi sebagai yang tidak dikawal atau gunakan gabungan sebagai alternatif.
• Jenis data yang betul dan pembersihan data: Pastikan jenis data anda sesuai dan data itu bersih untuk mengelakkan penapisan atau perbandingan yang tidak perlu.

Bolehkah saya menggunakan subqueries dengan pangkalan data SQL yang berbeza (misalnya, MySQL, PostgreSQL, SQL Server)?

Ya, subqueries disokong oleh hampir semua pangkalan data SQL utama, termasuk MySQL, PostgreSQL, SQL Server, Oracle, dan lain -lain. Sintaks asas adalah serupa di seluruh pangkalan data ini, walaupun terdapat variasi kecil dalam sintaks atau ciri yang disokong. Walau bagaimanapun, ciri -ciri prestasi dan strategi pengoptimuman mungkin berbeza sedikit bergantung kepada sistem pangkalan data tertentu dan pengoptimalnya. Memahami spesifik pengoptimuman pertanyaan sistem pangkalan data anda adalah penting untuk penulisan pertanyaan yang cekap.

Atas ialah kandungan terperinci Bagaimanakah saya menggunakan subqueries dalam SQL untuk membuat pertanyaan kompleks?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!