Find the Superset from the Relationship Table — From SQL to SPL #19

Problem description & analysis:

The MySQL database has two tables: bag table and bag item relationship table. Some bags are supersets of other bags.

bag

bag_item

Task: Now we need to find the superset for each bag:

Code comparisons:

SQL：

SELECT
  base.id AS base_bag_id,
  s.id AS superset_bag_id
FROM bag base
JOIN bag s
   ON s.id <> base.id
  AND NOT EXiSTS (SELECT 1
    FROM bag_item bi
    WHERE bi.bag_id = base.id
      AND NOT EXISTS (SELECT 1
        FROM bag_item si
        WHERE si.item_id = bi.item_id
          AND si.bag_id = s.id
    )
);

Two layers of loop traversal are required here, and SQL requires three layers of nesting combined with JOIN and difficult to understand EXISTS to implement set operations, which is difficult to understand.

SPL: SPL can easily implement it using two-layer loops combined with intuitive set operations. Try the DEMO 👉🏻: https://try.esproc.com/splx?471

A2： Group by bag, but do not aggregate, each group is a set of items including a bag and its contents.

A3： Calculate the target result through a two-layer loop. \ means difference set operation.

Free to Try, Powerful to Use: esProc SPL FREE Download.