What is a Subquery?

Query inside the another query called Subquery. It always runs first and the result is passed to the outer query.

💡Info - It is the continuation of Basics of SQL

1. Example : Find all creatures more expensive than the average price of creatures.

   -- find average price first
   
   SELECT name, price FROM creatures WHERE price > (SELECT AVG(price) FROM creatures);
   

   • The inner query SELECT AVG(price) return the average price.
   • Outer query compares each creatures's price with this avg price

2. Example : Show all creatures that have been purchased.

   SELECT name FROM creatures
   WHERE id IN (
       SELECT creature_id FROM purchases
   );
   

3. Example : Show all creatures with their price and the average price in the same rows

   SELECT 
   name, price (SELECT AVG(price) FROM creatures) AS avg_price
   FROM creatures;
   

4. Example : Show max lifespan per spacies?

   SELECT species, MAX(lifespan_years) AS max_life
   FROM (
   SELECT species, lifespan_years
   FROM creatures
   ) AS species_life 
   GROUP BY species;
   

   • The Subquery selects species and lifespan
   • The outer query groups it and calculates max lifespan

5. Example : Corelated subquery - Show creatures whose price is more than the average price of their species.

   SELECT name, species, price FROM creatures c1 WHERE price > (
       SELECT AVG(price) FROM creatures c2
       WHERE c1.spacies = c2.species
   )
   

   • The correlated subquery runs once per row in the outer query. It compares each creature's price to the average for its species