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

Question

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.

Are Recipe1 and Recipe2 tables? – forpas Dec 28 '20 at 13:54 — forpas, Dec 28 '20 at 13:54

score 2 · Answer 1 · answered Dec 28 '20 at 14:15

2

Left join should work as well

Select count(*) ct 
from Recipe2 r2 
left join Recipe1 r1 on r2.Ingredient = r1.Ingredient
where r1.Ingredient is null

answered Dec 28 '20 at 14:15

JamieD77

13,796
1
17
27

Olivier Jacot-Descombes · Answer 2 · 2020-12-28T13:58:41.490

1

Assuming that the recipe above are tables, then the following query will yield a count of 0 if Recipe2 is a subset of Recipe1.

SELECT COUNT(*) AS cnt
FROM
(SELECT * FROM Recipe2
 EXCEPT
 SELECT * FROM Recipe1) AS x

See: Set Operators - EXCEPT and INTERSECT (Transact-SQL)

edited Dec 28 '20 at 13:58

answered Dec 28 '20 at 13:53

Olivier Jacot-Descombes

104,806
13
138
188

It gives me a syntax error on the last parenthesis, I don't understand why. – Shayan Dec 28 '20 at 13:58
Ohh I see now, it must have a name otherwise returns an error (referring to `AS x`). – Shayan Dec 28 '20 at 14:03
1

This is SQL-Server syntax (TSQL). I see that you also have a MySQL tag. MYSQL does not have an equivalent. See: [How to do MINUS/EXCEPT and INTERSECT in MySQL](http://www.geeksengine.com/database/multiple-table-select/minus-except.php) for an alternative solution for MySQL. – Olivier Jacot-Descombes Dec 28 '20 at 14:04

score 1 · Accepted Answer · answered Dec 28 '20 at 15:02

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

forpas · Answer 4 · 2020-12-28T14:10:10.663

0

In MySql you can use EXISTS and aggregation:

SELECT MIN(EXISTS (SELECT 1 FROM Recipe1 r1 WHERE r1.col1 = r2.col2)) AS is_subset
FROM Recipe2 r2

Or with NOT EXISTS:

SELECT NOT EXISTS (
         SELECT 1 FROM Recipe2 r2 
         WHERE NOT EXISTS (SELECT 1 FROM Recipe1 r1 WHERE r1.col1 = r2.col2)
       ) is_subset

See the demo.

edited Dec 28 '20 at 14:10

answered Dec 28 '20 at 14:03

forpas

160,666
10
38
76

score 0 · Answer 5 · answered Dec 28 '20 at 14:34

MS SQL Server:

You can use Join and Case when like the below.

SELECT CASE WHEN COUNT(R1.C1)=COUNT(R2.C1) THEN '1' 
ELSE '0' END AS IS_SUBSET
FROM (VALUES ('Flour'),('Egg'),('Sugar'))R1(C1)
RIGHT JOIN (VALUES ('Egg'),('Sugar'))R2(C1)
ON R1.C1=R2.C1

SQL Fiddle demo link:

http://sqlfiddle.com/#!18/9eecb/107092/0

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

5 Answers5