A subquery shine buhatantawa da ta keɓe a cikin wata buhatantawa — ana amfani da ita a WHERE, FROM, SELECT, ko tare da EXISTS. Ita tana ba da damar amfani da sakamakon buhatantawa ɗaya a cikin wata, yana ba da dama ga tacewa masu wahala da ƙididdigar lissafi.
-- 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 a WHERE yana ba da ƙimomi don tacewa — jerin (tare da IN) ko ƙima ɗaya (tare da ma'aunin gwaji).
-- 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 yana tunawa da buhatantawa ta waje — yana gudu sau ɗaya ga kowane jere ta waje (mai yuwuwa a hankali). Uncorrelated subquery yana gudu sau ɗaya.
-- 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 yana bincike ko buhatantawa ta dawo da wani jere — sau da yawa ya fi tasiri fiye da IN don binciken wanzuwa (ita zai iya dakewa a farkon takamaiman).
-- 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 shine mahimman dabbar SQL don buhatantawa masu wahala — amfani da sakamakon buhatantawa ɗaya a cikin wata yana ba da damar tacewa da ƙididdigar lissafi waɗanda buhatantawa mai sauƙi ba zai iya bayyana ba, don haka fahimta su yana da mahimmanci don rubuta buhatantawa na ainihi.
Sanin siffofi — subqueries a WHERE (tacewa ta jerin tare da IN ko ƙima ɗaya tare da ƙiyasai), correlated subqueries (tunawa da buhatantawa ta waje, gudu ga kowane jere), EXISTS (binciken wanzuwa mai tasiri), da subqueries a FROM (tebur da aka samo) — yana rufe ƙananan shirye-shirye don bayyana dabaru masu wahala.
Fahimtar bambancin tsakanin correlated da uncorrelated subqueries yana da mahimmanci ga karfi (correlated subqueries suna gudu sau ɗaya ga kowane jere ta waje kuma zai iya jita).
Daidai mahimmi kuma shine sanin dangantaka da JOINs da CTEs: jiyya da yawa na subqueries zai iya sake rubuta azaman JOINs (sau da yawa ya fi tasiri da faɗi) ko CTEs (sauƙi wa'u don batutuwan masu wahala), don haka fahimta lokacin amfani da kowa — subqueries don tacewa/jumlashekewa bayyana, JOINs don haɗa bayanai — shine aiki mai amfani.
Tun da buhatantawa ta duniya sau da yawa tana buƙatar raba (tacewa ta sakamakon ƙididdigar lissafi, binciken wanzuwa, ƙiyasai da ƙimomi da aka ƙida), kuma tun da fahimtar subqueries, nuwannin aiki su (musamman waɗanda aka haɗa), da dangantakinsu da JOINs/CTEs yana da mahimmanci don rubuta aiki mai inganci, masu wahala buhatantawa, masaurin subqueries yana da mahimmanci, ilimin da aka yi akai-akai don SQL waje da buhatantawa tari kuma butun gabatarwa jiya na gida da ke nuna ƙarfin bayyana buƙatun bayanai masu wahala.