Find all sets that are a subset of a superset in SQL

Question

I'm reflecting on the design of an application where the main function revolves around the ability to find the set of all sets that are subsets of a given set.

For example, given the input set A = {1,2,3 ... 50} and the set of sets B = {B1 = {3,5,9,12}, B2 = {1,6,100,123, 45} ... B500 = {8,67,450}}, return all Bs that are a subset of A.

I suppose this looks like a search engine, except that I really don't have the luxury when the set A is small and B is large; in my case, Bs is usually less than A.

I found a similar question here , but wondered if there was something more efficient / standard.

Answer 1

Harper's answer is correct and elegant. Of course, the "standard" among experienced SQL encoders. The requirement, of course, must be normalized: the parent is not duplicated; Parent :: The child has two relationships; in the "Children" table there are two unique indexes (ParentKey, ChildKey) and (ChildKey, ParentKey), "otherwise all bets are disabled." It is impossible to get better performance than this (provided that the server is configured correctly for the equipment, etc.). The next step is 6NF, which greatly improves performance, but you don’t need to go there unless you need it. If your Bs is less than your As, it will be very fast.

. Db ( Bs As) . . Sybase , MS.