TSQL Comparing two Sets

Question

When two sets are given

s1 ={ a,b,c,d} s2={b,c,d,a}

(i.e)

TableA

Item
a
b
c
d

TableB

Item
b
c
d
a

How to write Sql query to display "Elements in tableA and tableB are equal". [Without using SP or UDF]

Output

Elements in TableA and TableB contains identical sets

Answer 1

Use EXCEPT statement

When using the EXCEPT statement to test if two sets contain the same rows, you will need to do the EXCEPT in both directions (A EXCEPT B and B EXCEPT A). If either comparison returns any records, then the sets are different. If no records are returned by either, they are the same.

The nice thing about this is that you can do this comparison with any number of specific columns and NULL values are handled implicitly without having to jump through hoops to compare them.

A good use case for this is verifying that saving a set of records happened correctly, especially when affecting an existing set.

SELECT  IsMatching = (1 ^ convert(bit, count(*)))
FROM    (
        SELECT  Mismatched = 1     -- Can be any column name
        FROM    (
                    SELECT  Item   -- Can have additional columns
                    FROM    TableA
                    EXCEPT
                    SELECT  Item   -- Can have additional columns
                    FROM    TableB
                ) as A
        UNION
        SELECT  Mismatched = 1     -- Can be any column name
        FROM    (
                    SELECT  Item   -- Can have additional columns 
                    FROM    TableB
                    EXCEPT
                    SELECT  Item   -- Can have additional columns
                    FROM    TableA
                ) as A
        ) as A

Answer 2

Since this thread was very helpful to me, I thought I'd share my solution.

I had a similar problem, perhaps more generally applicable than this specific single-set comparison. I was trying to find the id of an element that had a set of multi-element child elements that matched a query set of multi-element items.

The relevant schema information is:

table events, pk id
table solutions, pk id, fk event_id -> events
table solution_sources, fk solutionid -> solutions
   columns unitsourceid, alpha

Query: find the solution for event with id 110 that has the set of solution_sources that match the set of (unitsourceid, alpha) in ss_tmp. (This can also be done without the tmp table, I believe.)

Solution:

with solutionids as (
  select y.solutionid from (
     select ss.solutionid, count(ss.solutionid) x 
        from solutions s, solution_sources ss 
        where s.event_id = 110 and ss.solutionid = s.id 
        group by ss.solutionid
  ) y where y.x = ( select count(*) from ss_tmp )
) 
select solutionids.solutionid  from solutionids where
(
select case
   when count(*) = ( select count(*) from ss_tmp ) then true
   else false
   end
    from 
       ( SELECT unitsourceid, alpha FROM solution_sources 
            where solutionid = solutionids.solutionid
          INTERSECT
         SELECT unitsourceid, alpha FROM ss_tmp ) x
)

Tested against a test query of 4 items and a test db that had a matching solution (same number of child elements, each that matched), several completely non-matching solutions, and 1 solution that had 3 matching child elements, 1 solution that had all 4 matching child elements, plus an additional child, and 1 solution that had 4 child elements of which 3 of the 4 matched the query. Only the id of the true match was returned.

thanks a lot -Linus

Answer 3

Something like this, using FULL JOIN:

SELECT
  CASE 
    WHEN EXISTS (
      SELECT * FROM s1 FULL JOIN s2 ON s1.Item = s2.Item
      WHERE s1.Item IS NULL OR s2.Item IS NULL
      )
    THEN 'Elements in tableA and tableB are not equal'
    ELSE 'Elements in tableA and tableB are equal'
  END

This has the virtue of short-circuiting on the first non-match, unlike other solutions that require 2 full scans of each table (once for the COUNT(*), once for the JOIN/INTERSECT).

Estimated cost is significantly less than other solutions.

Answer 4

My monstrocity:

;with SetA as
(select 'a' c union
select 'b' union
select 'c') 
, SetB as 
(select 'b' c union
select 'c' union
select 'a' union 
select 'd'
) 
select case (select count(*) from (
select * from SetA except select * from SetB
union 
select * from SetB except select * from SetA
)t)
when 0 then 'Equal' else 'NotEqual' end 'Equality'

Answer 5

Watch out, I'm gonna use a Cross Join.

Declare @t1 table(val varchar(20))
Declare @t2 table(val varchar(20))


insert into @t1 values ('a')
insert into @t1 values ('b')
insert into @t1 values ('c')
insert into @t1 values ('d')


insert into @t2 values ('c')
insert into @t2 values ('d')
insert into @t2 values ('b')
insert into @t2 values ('a')

select 
    case when 
    count(1) = 
    (((Select count(1) from @t1) 
    + (Select count(1) from @t2)) / 2.0) 
    then 1 else 0 end as SetsMatch  from 
@t1 t1 cross join @t2 t2 
where t1.val = t2.val

Answer 6

Use:

SELECT CASE 
         WHEN   COUNT(*) = (SELECT COUNT(*) FROM a) 
            AND COUNT(*) = (SELECT COUNT(*) FROM b) THEN 'Elements in TableA and TableB contains identical sets'
         ELSE 'TableA and TableB do NOT contain identical sets'
       END
  FROM (SELECT a.col
          FROM a
        INTERSECT
        SELECT b.col
          FROM b) x 

Test with:

WITH a AS (
  SELECT 'a' AS col
  UNION ALL
  SELECT 'b'
  UNION ALL
  SELECT 'c'
  UNION ALL
  SELECT 'd'),
     b AS (
  SELECT 'b' AS col
  UNION ALL
  SELECT 'c'
  UNION ALL
  SELECT 'd'
  UNION ALL
  SELECT 'a')
SELECT CASE 
         WHEN   COUNT(*) = (SELECT COUNT(*) FROM a) 
            AND COUNT(*) = (SELECT COUNT(*) FROM b) THEN 'yes'
         ELSE 'no'
       END
  FROM (SELECT a.col
          FROM a
        INTERSECT
        SELECT b.col
          FROM b) x 

Answer 7

Could do it with EXCEPT and a case

select 
   case 
     when count (1)=0 
        then 'Elements in TableA and TableB contains identical sets' 
     else 'Nope' end from (
       select item from s1
      EXCEPT 
       select item from s2
) b