Subquery ni swali lililofungwa ndani ya swali jingine — linalotumika katika WHERE, FROM, SELECT, au na EXISTS. Hukuruhusu kutumia matokeo ya swali moja ndani ya jingine, kuwezesha uchujaji na ukokotoaji changamano.
Subquery ni swali lililofungwa ndani ya swali jingine — linalotumika katika WHERE, FROM, SELECT, au na EXISTS. Hukuruhusu kutumia matokeo ya swali moja ndani ya jingine, kuwezesha uchujaji na ukokotoaji changamano.
-- 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 katika WHERE hutoa thamani za kuchuja dhidi yake — orodha (kwa IN) au thamani moja (kwa operators za ulinganisho).
-- 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)
);
Subquery ya correlated hurejelea swali la nje — huendeshwa mara moja kwa kila safu ya nje (yawezekana polepole zaidi). Subquery isiyo ya correlated huendeshwa mara moja.
-- 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 hukagua iwapo subquery inarudisha safu yoyote — mara nyingi yenye ufanisi zaidi kuliko IN kwa ukaguzi wa uwepo (inaweza kusimama kwenye ulinganifu wa kwanza).
-- 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 ni mbinu muhimu ya SQL kwa uulizaji changamano — kutumia matokeo ya swali moja ndani ya jingine huwezesha uchujaji na ukokotoaji ambao maswali rahisi hayawezi kueleza, hivyo kuzielewa ni cha thamani kwa kuandika maswali ya ulimwengu halisi.
Kujua aina — subqueries katika WHERE (kuchuja kwa orodha na IN au thamani moja na ulinganisho), correlated subqueries (kurejelea swali la nje, kuendeshwa kwa kila safu), EXISTS (ukaguzi wa uwepo wenye ufanisi), na subqueries katika FROM (derived tables) — hufunika miundo ya kawaida ya kueleza mantiki changamano.
Kuelewa tofauti kati ya subqueries za correlated na non-correlated ni muhimu kwa utendaji (correlated subqueries huendeshwa mara moja kwa kila safu ya nje na zinaweza kuwa polepole).
Ni cha thamani vivyo hivyo kujua uhusiano na JOINs na CTEs: subqueries nyingi zinaweza kuandikwa upya kama JOINs (mara nyingi zenye ufanisi na zinazosomeka zaidi) au CTEs (safi zaidi kwa kesi changamano), hivyo kuelewa lini utumie kila moja — subqueries kwa uwazi wa uchujaji/aggregation, JOINs kwa kuunganisha data — ni ujuzi wa kivitendo.
Kwa kuwa maswali ya ulimwengu halisi mara nyingi yanahitaji ufungaji (kuchuja kwa matokeo ya aggregate, ukaguzi wa uwepo, kulinganisha na thamani zilizokokotolewa), na kwa kuwa kuelewa subqueries, sifa zao za utendaji (hasa za correlated), na uhusiano wao na JOINs/CTEs ni muhimu kwa kuandika maswali changamano yenye ufanisi, kufahamu vyema subqueries ni maarifa yenye thamani, yanayotumika mara kwa mara kwa SQL zaidi ya uulizaji wa msingi na mada ya kawaida ya mahojiano inayodhihirisha uwezo wa kueleza mahitaji changamano ya data.