CODEWITHSUNDEEP
sql
NewSelf
NewSelf

Reputation: 247

Why sometimes a subquery can work like using 'group by'

I'm new to sql and can't understand why sometimes a subquery can work like using 'group by'.

Say, there are two tables in a data base. 'food' is a table crated by:

CREATE TABLE foods (
  id integer PRIMARY KEY,
  type_id integer,
  name text
);

'foods_episodes' is a table created by:

CREATE TABLE foods_episodes (
  food_id integer,
  episode_id integer
);

Now I'm using the following two sqls and generating the same result.

SELECT name, (SELECT count(*) FROM foods_episodes WHERE food_id=f.id) AS frequency
FROM foods AS f
ORDER BY name;

SELECT name, count(*) AS frequency
FROM foods_episodes,
     foods AS f
WHERE food_id=f.id
GROUP BY name;

So why the subquery in the first sql works like it group the result by name? When I run the subquery alone:

SELECT count(*)
FROM foods_episodes,
     foods f
WHERE food_id=f.id

the result is just one row. Why using this sql as a subquery can generate multi-rows result?

Upvotes: 1

Views: 45

Answers (1)

EzLo
EzLo

Reputation: 14199

The first query isn't actually grouping by name. If you have more than 1 record with the same name (different ID), you will see it being displayed twice (hence, not grouped by).

The first query uses what is called a correlated subquery, it calculates the subquery (the inner SELECT) once for each row of the outmost select. Because the FROM in this outmost SELECT is just from the table foods, you will get one record for each food + the results of the subquery, thus no need to group.

Upvotes: 2

Related Questions