TSQL Comparing two Sets

问题:

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

回答1:

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 

回答2:

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.

回答3:

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

回答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'

回答5:

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

回答6:

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