CODEWITHSUNDEEP
sqlpostgresqljoinsubquery
Mark Gurevich
Mark Gurevich

Reputation: 238

How can I rewrite subquery inside JOIN?

I'm not very fluent in SQL and my question is how can I rewrite the following statement to make it look more natural. The select I'm trying to write joins two tables -- "users" and "stats" -- and I know id's of users in advance. It's probably something very basic but I'm not an SQL ninja yet.

select
    u.id,
    sum(s.xxx)
from
    (
        select id from users where id in (100, 200, 300)
    ) u
    left join
    stats s
        on u.id = s.user_id
group by
    u.id
;

The part that looks strange is

    (
        select id from users where id in (100, 200, 300)
    ) u

Suggest me the right way. Thanks

Upvotes: 0

Views: 410

Answers (1)

JNK
JNK

Reputation: 65147

That's a complicated way of saying

....WHERE id in (100,200,300)

In your WHERE clause.

The whole thing could be rewritten as:

select
    u.id,
    sum(s.xxx)
from
    users u
left join stats s
    on s.user_id = u.id
where u.id in (100, 200, 300)
group by
    u.id

Upvotes: 3

Related Questions