TSQL Comparing Two Sets

Question

When two sets are specified

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

(those.)

TableA Item a b c d TableB Item b c d a 

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

Exit

 Elements in TableA and TableB contains identical sets 

Answer 1

Using:

 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 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 force of a short circuit in the first mismatch, unlike other solutions that require 2 full scans of each table (once for COUNT (*), once for JOIN / INTERSECT).

Estimated cost is much less than other solutions.

Answer 3

My monster:

 ;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 4

Beware, I'm going to use 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

You can do this with EXCEPT and 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 6

Since this thread was very useful for me, I decided to share my solution.

I had a similar problem, perhaps more generally applicable than this specific comparison with one set. I tried to find the identifier of an element that had a set of multi-element child elements that matched a set of queries from elements with multiple elements.

Relevant schema information:

 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 the event with ID 110, which has a set of solution_sources resources that correspond to the set (unitsourceid, alpha) in ss_tmp. (This can also be done without the tmp table, I reckon.)

Decision:

 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 yx = ( 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 request of 4 elements and a test db with the corresponding solution (the same number of children, each of which corresponds), several completely inappropriate solutions and 1 solution having 3 corresponding children, 1 solution in which there were all 4 corresponding children , plus an extra child, and 1 decision in which there were 4 children, of which 3 out of 4 matched the request. Only the true match identifier returned.

thanks a lot - linus