Monday, April 12, 2010

How to know if all of A is inside B


This is my answer on http://stackoverflow.com/questions/2577500/how-understand-one-result-set-is-subset-of-another-in-twomysql-select-result-set/


A's set: 3, 5, 10

B's set: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

I wanted to do this, count b when it equals a:

select count( b.id when b.id = a.id ) = count(a.id) 
    as is_a_subset_of_b
from a
left join b using(id)




But it's not the proper syntax, so we should do this:

select count( case when b.id = a.id then b.id end ) = count(a.id) 
    as is_a_subset_of_b
from a
left join b using(id)

Wished SQL languages have Python syntax's elegance, check their list comprehension's if clause


If there's a repeating elements in A and B, this is more fullproof:
select exists
(select *
from a
left join b using(id)
where b.id is null
) as is_a_subset_of_b

No comments:

Post a Comment