---

Technique to test for subset and superset conditions

---

• The Superset (Contains) (⊇) and Subset (⊆) Tests
  • SQL does not support a subset/superset test operation

    It is not possible to write the following boolean condition in SQL:

    SELECT ... FROM ... WHERE set1 CONTAINS set2

  • As you will see, we will need to use:

    The superset (⊇) test in the set division query and The subset (⊆) test in some other useful queries

  ---

  ---

  ---

  ---

  ---

• Formulating the Superset ("contains") condition
  • Superset:

    A set set1 contains (is a superset of) another set set2 if: Every element of set2 is also an element of set1 Graphically:

    ---

    ---

  • We can perform a superset test operation as follows in SQL:

    You can see that the condition that "set1 constains set2" is equivalent to:

    set1 CONTAINS set2 <==> set2 - set1 = EMPTY

    ---

    Therefore:

    SELECT ... FROM ... WHERE set1 CONTAINS set2 // Pseudo code !!! Is equivalent to: SELECT ... FROM ... WHERE set2 - set1 = EMPTY // Pseudo code !!! Or: SELECT ... FROM ... WHERE NOT EXISTS {set2 - set1} // Almost SQL !!! Or: SELECT ... FROM ... WHERE NOT EXISTS (SELECT * FROM universe-relation R (with key x) WHERE x IN set2 AND x NOT IN set1) // SQL - but you still need to // write SQL command to find // set1 and set2

  ---

  ---

  ---

  ---

• Formulating the Subset condition
  • It should be clear from the above discussion that:

    SELECT ... FROM ... WHERE set1 is a SUBSET of set2 // Pseudo code !!! Is equivalent to: SELECT ... FROM ... WHERE set1 - set2 = EMPTY // Pseudo code !!! Or: SELECT ... FROM ... WHERE NOT EXISTS {set1 - set2} // Almost SQL !!! Or: SELECT ... FROM ... WHERE NOT EXISTS (SELECT * FROM universe-set WHERE x IN set1 AND x NOT IN set2) // SQL - but you need to // "fill in the blanks"