# EXPLAIN EXTENDED

How to create fast database queries

## Matching whole sets

From Stack Overflow:

Given the following:

```declare @a table
(
pkid int,
value int
)

declare @b table
(
otherID int,
value int
)

insert into @a values (1, 1000)
insert into @a values (1, 1001)
insert into @a values (2, 1000)
insert into @a values (2, 1001)
insert into @a values (2, 1002)

insert into @b values (-1, 1000)
insert into @b values (-1, 1001)
insert into @b values (-1, 1002)
```

, how do I query for all the values in `@a` that completely match up with `@b`?

`{@a.pkid = 1, @b.otherID = -1}` would not be returned (only 2 of 3 values match)

`{@a.pkid = 2, @b.otherID = -1}` would be returned (3 of 3 values match)

We need to compare each set of values from `@a` to each set of `@b` and return the `id`'s of non-matching sets.

Two sets are considered matching when for each row from the first set there is an equal row from the second set and vise versa. That is, both the values and the count of the rows should match in both sets.

First thing that comes to mind is to calculate `COUNT(*)`'s of both sets, then calculate the `COUNT(*)` for the result of both sets joined on `value` and make sure all three are equal.

But we don't actually need to `COUNT(*)` all values. We just need to find out the first row from either of the subsets that has no match in another subset. As soon as we find such a row, we can tell that the sets don't match; if we don't find one, then the sets do match.
Read the rest of this entry »

Written by Quassnoi

March 31st, 2009 at 11:00 pm

Posted in SQL Server