A subquery adalah query yang nested ing query liyane — digunakan ing WHERE, FROM, SELECT, utawa karo EXISTS. Iki ndadekake sampeyan bisa nggunakake hasil siji query ing query liyane, ndadekake filtering lan computations sing kompleks.
A subquery adalah query yang nested ing query liyane — digunakan ing WHERE, FROM, SELECT, utawa karo EXISTS. Iki ndadekake sampeyan bisa nggunakake hasil siji query ing query liyane, ndadekake filtering lan computations sing kompleks.
-- find users who have placed an order (their id is in the orders table)
SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders); -- the subquery returns a list of user_ids
-- find products more expensive than the average
SELECT name, price FROM products
WHERE price > (SELECT AVG(price) FROM products); -- subquery returns a single value
Subquery ing WHERE menehi nilai kanggo filter — sedhéla list (karo IN) utawa nilai tunggal (karo comparison operators).
-- for each user, find those with above-average orders FOR THEIR COUNTRY
SELECT name FROM users u
WHERE order_count > (
SELECT AVG(order_count) FROM users
WHERE country = u.country -- references the OUTER query's row (correlated)
);
A correlated subquery referensi outer query — iku mbukak sawise saben baris outer (bisa luwih alon). Uncorrelated subquery mbukak sapisan.
-- users who have at least one order (often more efficient than IN)
SELECT name FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id -- just checks existence
);
EXISTS verify apa subquery bali apa-apa baris — asring luwih efficient tinimbang IN kanggo existence checks (bisa mandheg ing match pisanan).
-- use a subquery result as a table
SELECT country, avg_age FROM (
SELECT country, AVG(age) AS avg_age FROM users GROUP BY country
) AS country_stats
WHERE avg_age > 30;
Many subqueries can be rewritten as JOINs (often more efficient/readable).
WHERE id IN (SELECT ...) → can often be a JOIN
Use subqueries for clarity in filtering/aggregation; JOINs for combining data.
CTEs (WITH) are often a cleaner alternative to complex nested subqueries.
Subqueries minangka teknik SQL penting kanggo complex querying — nggunakake hasil siji query ing query liyane ndadekake filtering lan computations sing query prasaja ora biso express, mangkono ngerti iku mkikat kanggo nulis query nyata.
Ngerti bentuk-bentuke — subqueries ing WHERE (filtering dening list karo IN utawa nilai tunggal karo comparisons), correlated subqueries (referensi outer query, mbukak saben baris), EXISTS (efficient existence checks), lan subqueries ing FROM (derived tables) — nutupi pola umum kanggo express logika kompleks.
Ngerti bedane antara correlated lan uncorrelated subqueries iku penting kanggo performance (correlated subqueries mbukak sapisan saben baris outer lan biso alon).
Sama bernilai iku ngerti hubungan menyang JOINs lan CTEs: akeh subqueries bisa ditulis maneh minangka JOINs (asring luwih efficient lan readable) utawa CTEs (luwih resik kanggo kasus kompleks), mangkono ngerti kapan nggunakake saben — subqueries kanggo filtering/aggregation clarity, JOINs kanggo combine data — minangka skills praktis.
Sajak query nyata asring butuh nesting (filtering dening aggregated results, existence checks, comparing menyang computed values), lan sajak ngerti subqueries, karakteristik performance-nya (khususé correlated ones), lan hubungan-nya menyang JOINs/CTEs iku penting kanggo nulis complex queries sing efektif lan efficient, kuasani subqueries iku ilmu bernilai lan asring digunakake kanggo SQL luwih saka basic querying lan topik interview umum sing nuduhke kemampuan express complex data requirements.