I have a PostgreSQL table:
create table test(type_id, m_id)as values
(1, 123)
,(2, 456)
,(3, 123)
,(4, 123)
,(2, 456)
,(1, 789)
;
Basically, one m_id
could have multiple rows in this table associated with different type_id
s.
How can I find m_id
s that have type_id
s only of value 2
? In this example, it's the m_id
of 456
only.
I tried to group by m_id
, but didn't really work out well. Any ideas please?
Thanks!
You're on the right track with the group by
idea. Just add having
clause to that:
demo at db<>fiddle
select m_id from test
group by m_id having every(type_id=2);
m_id |
---|
456 |
The every()
/ bool_and()
function checks a condition for all rows in the group so it makes sure all type_id
's appearing with a given m_id
are a 2
. Or, that there doesn't exist a row in that group for which that expression evaluates to false
.
You can also use an exists
:
select distinct m_id from test t1
where type_id=2
and not exists(select from test t2
where t1.m_id=t2.m_id
and t2.type_id<>2);
Or an anti-join:
select distinct t1.m_id
from test t1
left join test t2
on t1.type_id=2
and t2.type_id<>2
and t1.m_id=t2.m_id
--that's the `anti-` part:
--it's a `left join`, but you then ask only for unmatched rows
--marked by a null on the right
where t2.m_id is null
and t1.type_id=2;
Or an except
:
select m_id from test where type_id=2
except
select m_id from test where type_id<>2;
Here's a test where there's 13 such m_id
's buried among 200k other in a heap of 900k randomised rows. It shows that both exists
and the explicit anti-join
examples result in a nested loop anti-join plan that outperforms the rest, at just 3ms
. That's thanks to these covering indexes:
create index on test(type_id,m_id);
create index on test(m_id)include(type_id);
Having
needed 253ms
and except
took 530ms
, even though they had also been given adequate indexes.