Select group of rows that match all items in a list

In-depth query strategy

Certainly, the fast answer does the trick. But for versatility and profound understanding, let’s explore relational division - the craft of matching group records against a list.

Harnessing the power of joins

Ultron used the infinity stones, SQL developers use joins. Win matches across tables like this:

SELECT a.order_id
FROM orders a
INNER JOIN items b ON a.item_id = b.id
WHERE a.item_id IN (SELECT id FROM items WHERE criteria) -- Talk about being picky!
GROUP BY a.order_id
HAVING COUNT(DISTINCT a.item_id) = (SELECT COUNT(*) FROM items WHERE criteria);


With INNER JOIN, we only care about shared rows, and subqueries help to swing with any dynamic sets.

Hunting down missing matches

Feeling like Sherlock Holmes? Use a LEFT JOIN and track down **NULL**s:

SELECT a.order_id
FROM orders a
LEFT JOIN items b ON a.item_id = b.id AND b.criteria
GROUP BY a.order_id
HAVING COUNT(DISTINCT a.item_id) = (SELECT COUNT(*) FROM items WHERE criteria)
   AND COUNT(DISTINCT CASE WHEN b.id IS NULL THEN 1 ELSE NULL END) = 0;


No partial matches allowed

With NOT EXISTS, we play No Partial Matches Allowed. It’s an all-or-nothing game:

SELECT o.order_id
FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM items
  WHERE criteria
    AND NOT EXISTS (
      SELECT 1 FROM orders
      WHERE item_id = items.id
        AND order_id = o.order_id
    )
)
GROUP BY o.order_id;


This double-nested subquery assures every item catering to the criteria is in our order, showing the door to any half-baked matches.

Making it work

While crafting these queries, keep in mind these essential data tricks:

The art of uniquness

Use DISTINCT or unique constraints to dismiss any clone wars (duplicates)! They can lead to grievous conclusions otherwise.

Dynamics of lists

For lists that fluctuate more than an EKG, use subquery counts within your HAVING clause for adaptability.

Performance is key

Understand, as your data grows, some strategies might act like the "Flash" of DC - slowing down time (causing performance issues)! Always trial and test on large datasets.

SQL flavors

Some SQL dialects possess the power of Superman, introducing unique features. Getting accustomed to your SQL dialect paves way for tailored solutions.