Question

Using co-related queries in SQL

SQL, Query, SQL Query, Co-related queries

How do you use co-related queries in SQL and why would they be useful? Is there any effect on performance when executing a co-related query?

here's one example:

SELECT team
      , player
      , goals
   FROM stats AS t
  WHERE goals =
 ( SELECT MAX(goals) FROM stats WHERE team = t.team )

gets the team and player who scored the most goals for his team

why would this be useful? bcoz it just is!!! ;o)

effect on performance? yes, usually a join to a derived table is faster

SELECT t.team
      , t.player
      , t.goals
   FROM stats AS t
 INNER
   JOIN ( SELECT team
               , MAX(goals) AS maxgoals
            FROM stats 
          GROUP BY team ) AS m
     ON m.team = t.team
    AND m.maxgoals = t.goals