Search code examples
mysqlsqlsql-servercountrelational-division

How to check if a set is a subset of another set in sql

enter image description here

Text to DDL:

CREATE TABLE Recipe1(
    Ingredient varchar(10)
);
INSERT INTO Recipe1(Ingredient) VALUES('Flour'),('Egg'),('Sugar');
CREATE TABLE Recipe2(
    Ingredient varchar(10)
);
INSERT INTO Recipe2(Ingredient) VALUES('Egg'),('Sugar');

I want to check if Recipe 2 is a subset of Recipe 1, and I want SQL to return a boolean value, how can I do that? I found out that ORACLE supports a function called SUBSET:

https://docs.oracle.com/cloud/latest/big-data-discovery-cloud/BDDEQ/reql_sets_subset.htm

But I can't find an equivalent for SQL Server or even MySQL. And I hope to get an answer for SQL Server.

Our instructor used this code: "Recipe 1" CONTAINS("Recipe 2") but it doesn't work. Also, I'm not using or planning to use Full-text search so I need a workaround.

Solution

  • A simple approach uses a left join and count()s:

    select (count(*) = count(r2.id)) as is_subset
from recipe1 r1
left join recipe2 r2 on t1.id = t.id

    That would work in databases that somehow support booleans (such as MySQL). A more portable approach uses case in the outer query:

    select case when count(*) = count(r2.id) then 1 else 0 end as is_subset
from recipe1 r1
left join recipe2 r2 on t1.id = t.id