how understand one result set is subset of another in twomysql select result set ?

Question

Hi I've 2 mysql select statements, suppose these are my queries & their result :

Statement 1 :

select id from a

which returns this result set : { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }

Statement 2 :

select id from b

which returns this result set : { 3, 5, 10 }

I want to write a query which determines whether resultset1 contains all records of resultset2 or not. I think needs a simple query, any suggestion ???

Answer 1

You could use the in ( <subselect> ) and not in ( <subselect> ) clauses to do this.

select 'inset' , a.id as IN_SET 
  from a 
 where a.id in (select b.id from b) 
union
select 'not inset' , a.id as IN_SET 
  from a 
 where a.id not in (select b.id from b) 

Answer 2

use this:

select     
(
    (select count(id) 
    from b  
    where id in (select id from a))

     =

    (select count(id) from b)

 ) as is_a_containing_all_b

or put distinct if your tables have duplicate values:

select     
(
    (select count(distinct id) 
    from b  
    where id in (select id from a))

     =

    (select count(distinct id) from b)

 ) as is_a_containing_all_b

Answer 3

A simple way to do this is to create b LEFT JOIN a and check if any row exists where the join fails:

SELECT NOT EXISTS
(
   SELECT b.id
   FROM b
   LEFT JOIN a
   ON a.id = b.id
   WHERE a.id IS NULL
) AS T1

Result:

1

Test data:

CREATE TABLE a (id INT NOT NULL);
INSERT INTO a (id) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

CREATE TABLE b (id INT NOT NULL);
INSERT INTO b (id) VALUES (3),(5),(10);

Answer 4

use this:

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