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

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 2

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

Answer 3

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 4

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 5

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.