title: SQL comparing sets, part II: How to join sets of sets
body: <p>This <a href="https://stackoverflow.com/questions/3661766/tsql-comparing-two-sets/3661993#3661993">question</a> reminded me of a couple related problems with whole-set comparison. Given:</p>
  
  <ol>
  <li>a <code>collection</code> of sets, and </li>
  <li>a <code>probe</code> set</li>
  </ol>
  
  <p>Three questions:</p>
  
  <ol>
  <li>How do you find all sets in <code>collection</code> that match <code>probe</code>, element for element?</li>
  <li>How do you find all sets in <code>collection</code> that match a collection of <code>probe</code>s, without the use of explicit looping constructs? How do you join sets of sets?</li>
  <li>Is this relational division? If not, what is it?</li>
  </ol>
  
  <p>I have a decent solution to question 1 (see below). </p>
  
  <p>I don't have a decent relational solution to question 2. Any takers?</p>
  
  <p>Test data:</p>
  
  <pre><code>IF OBJECT_ID('tempdb..#elements') IS NOT NULL DROP TABLE #elements
  IF OBJECT_ID('tempdb..#sets') IS NOT NULL DROP TABLE #sets
  
  CREATE TABLE #sets (set_no INT, PRIMARY KEY (set_no))
  CREATE TABLE #elements (set_no INT, elem CHAR(1), PRIMARY KEY (set_no, elem))
  
  INSERT #elements VALUES (1, 'A')
  INSERT #elements VALUES (1, 'B')
  INSERT #elements VALUES (1, 'C')
  INSERT #elements VALUES (1, 'D')
  INSERT #elements VALUES (1, 'E')
  INSERT #elements VALUES (1, 'F')
  INSERT #elements VALUES (2, 'A')
  INSERT #elements VALUES (2, 'B')
  INSERT #elements VALUES (2, 'C')
  INSERT #elements VALUES (3, 'D')
  INSERT #elements VALUES (3, 'E')
  INSERT #elements VALUES (3, 'F')
  INSERT #elements VALUES (4, 'B')
  INSERT #elements VALUES (4, 'C')
  INSERT #elements VALUES (4, 'F')
  INSERT #elements VALUES (5, 'F')
  
  INSERT #sets SELECT DISTINCT set_no FROM #elements
  </code></pre>
  
  <p>Setup and solution for question 1, set lookup:</p>
  
  <pre><code>IF OBJECT_ID('tempdb..#probe') IS NOT NULL DROP TABLE #probe
  CREATE TABLE #probe (elem CHAR(1) PRIMARY KEY (elem))
  INSERT #probe VALUES ('B')
  INSERT #probe VALUES ('C')
  INSERT #probe VALUES ('F')
  
  -- I think this works.....upvotes for anyone who can demonstrate otherwise
  SELECT set_no FROM #sets s
  WHERE NOT EXISTS (
    SELECT * FROM #elements i WHERE i.set_no = s.set_no AND NOT EXISTS (
      SELECT * FROM #probe p WHERE p.elem = i.elem))
  AND NOT EXISTS (
    SELECT * FROM #probe p WHERE NOT EXISTS (
      SELECT * FROM #elements i WHERE i.set_no = s.set_no AND i.elem = p.elem))
  </code></pre>
  
  <p>Setup for question 2, no solution:</p>
  
  <pre><code>IF OBJECT_ID('tempdb..#multi_probe') IS NOT NULL DROP TABLE #multi_probe
  CREATE TABLE #multi_probe (probe_no INT, elem CHAR(1) PRIMARY KEY (probe_no, elem))
  INSERT #multi_probe VALUES (1, 'B')
  INSERT #multi_probe VALUES (1, 'C')
  INSERT #multi_probe VALUES (1, 'F')
  INSERT #multi_probe VALUES (2, 'C')
  INSERT #multi_probe VALUES (2, 'F')
  INSERT #multi_probe VALUES (3, 'A')
  INSERT #multi_probe VALUES (3, 'B')
  INSERT #multi_probe VALUES (3, 'C')
  
  -- some magic here.....
  
  -- result set:
  -- probe_no | set_no
  ------------|--------
  -- 1        | 4
  -- 3        | 2
  </code></pre>