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.

Apr 16, 2025 - 03:43
 0
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 table

bag_item

bag_item table

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

expected results

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.