dev.to

Find the superset from the relationship table:SQL VS SPL#13

The MySQL database has two tables: bag table and bag item relationship table. Some bags are supersets of other bags. bag bag_item Now we need to find the superset for each bag: 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 can easily implement it using two-layer loops combined with intuitive set operations. Try.DEMO 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 download

Mar 17, 2025 - 10:48

Find the superset from the relationship table:SQL VS SPL#13

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

bag

bag_item

Now we need to find the superset for each bag:

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 can easily implement it using two-layer loops combined with intuitive set operations.
Try.DEMO

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 download