# Correlated Subquery __*Associated Subqueries*__ + Use subquery or use `JOIN` ? + Find the planet with the least mass in each category ```10 /* Correlated */ SELECT p.name, p.mass FROM universe.planets ~!@p@!~ WHERE p.mass = (SELECT MIN(mass) FROM universe.planets WHERE category_id = ~!@p.category_id@!~ ); /* Join */ SELECT p.name, p.mass FROM universe.planets p ~!@JOIN@!~ (SELECT category_id, MIN(mass) as min_mass FROM universe.planets GROUP BY category_id ) c ON p.category_id = c.category_id AND p.mass = c.min_mass; ``` ------------------------------------------------------------ You can try to see the difference between the two execution plans. It is common for correlated subquery to be converted to joins under the influence of the optimizer. ``` tidb> SELECT p.name, p.mass -> FROM universe.planets p -> WHERE p.mass = (SELECT MIN(mass) -> FROM universe.planets -> WHERE category_id = p.category_id -> ); +---------+-------+ | name | mass | +---------+-------+ | Mercury | 0.33 | | Uranus | 86.8 | | Pluto | 0.013 | +---------+-------+ 3 rows in set (0.00 sec) ``` ``` tidb> SELECT p.name, p.mass -> FROM universe.planets p -> JOIN (SELECT category_id, MIN(mass) as min_mass -> FROM universe.planets -> GROUP BY category_id -> ) c -> ON p.category_id = c.category_id -> AND p.mass = c.min_mass; +---------+-------+ | name | mass | +---------+-------+ | Mercury | 0.33 | | Uranus | 86.8 | | Pluto | 0.013 | +---------+-------+ 3 rows in set (0.00 sec) ``` ``` tidb> EXPLAIN SELECT p.name, p.mass -> FROM universe.planets p -> WHERE p.mass = (SELECT MIN(mass) -> FROM universe.planets -> WHERE category_id = p.category_id -> ); +----------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | HashJoin_12 | 8.00 | root | | inner join, equal:[eq(universe.planets.category_id, universe.planets.category_id) eq(universe.planets.mass, Column#49)] | | ├─Selection_16(Build) | 6.40 | root | | not(isnull(Column#49)) | | │ └─HashAgg_21 | 8.00 | root | | group by:universe.planets.category_id, funcs:min(Column#50)->Column#49, funcs:firstrow(universe.planets.category_id)->universe.planets.category_id | | │ └─TableReader_22 | 8.00 | root | | data:HashAgg_17 | | │ └─HashAgg_17 | 8.00 | cop[tikv] | | group by:universe.planets.category_id, funcs:min(universe.planets.mass)->Column#50 | | │ └─TableFullScan_20 | 10.00 | cop[tikv] | table:planets | keep order:false, stats:pseudo | | └─TableReader_15(Probe) | 10.00 | root | | data:TableFullScan_14 | | └─TableFullScan_14 | 10.00 | cop[tikv] | table:p | keep order:false, stats:pseudo | +----------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ 8 rows in set (0.00 sec) ``` ``` tidb> EXPLAIN SELECT p.name, p.mass -> FROM universe.planets p -> JOIN (SELECT category_id, MIN(mass) as min_mass -> FROM universe.planets -> GROUP BY category_id -> ) c -> ON p.category_id = c.category_id -> AND p.mass = c.min_mass; +----------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | id | estRows | task | access object | operator info | +----------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ | HashJoin_11 | 8.00 | root | | inner join, equal:[eq(universe.planets.category_id, universe.planets.category_id) eq(universe.planets.mass, Column#49)] | | ├─Selection_15(Build) | 6.40 | root | | not(isnull(Column#49)) | | │ └─HashAgg_20 | 8.00 | root | | group by:universe.planets.category_id, funcs:min(Column#50)->Column#49, funcs:firstrow(universe.planets.category_id)->universe.planets.category_id | | │ └─TableReader_21 | 8.00 | root | | data:HashAgg_16 | | │ └─HashAgg_16 | 8.00 | cop[tikv] | | group by:universe.planets.category_id, funcs:min(universe.planets.mass)->Column#50 | | │ └─TableFullScan_19 | 10.00 | cop[tikv] | table:planets | keep order:false, stats:pseudo | | └─TableReader_14(Probe) | 10.00 | root | | data:TableFullScan_13 | | └─TableFullScan_13 | 10.00 | cop[tikv] | table:p | keep order:false, stats:pseudo | +----------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------+ 8 rows in set (0.00 sec) ```