3 ms·
I'm self-taught when it comes to SQL so I'm probably doing stupid stuff, but I tend to use subqueries when I can. So in your case I'd do SELECT c.name
by magicalhippo 7y ago
I'm self-taught when it comes to SQL so I'm probably doing stupid stuff, but I tend to use subqueries when I can.
So in your case I'd do
SELECT c.name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE c.id = o.customer_id
AND DATE_TRUNC('year', o.ts) = '2019-01-01'::timestamptz
HAVING SUM(o.price) < 100
)
Again, might be dumb, but I usually find it easier to think in terms of subqueries rather than joins. Not that I find joining hard per se, just feels more natural and to the point to use subqueries. Database I use also seems to optimize it better most of the time.
- iimblack 7y agoDoesn't this produce different output? I could be wrong but I think your code is more equivalent to an inner join, not a left outer join.
- magicalhippo 7y agoOh yeah you're right, I read the comment above the query and assumed he wanted only customers which had orders. Could just modify it to exclude the orders above 100 ("where not exists"...), no?
- iimblack 7y agoYes, that should work.