SQL Server 2012 How to determine if all the values in table A are in table B

user_6494738 0
Viewed 84

I have two tables (A & B) that share a common value (Color), both tables can have any number of rows. I am trying to find a way to determine if all the distinct 'Colors' in table A exist in table B:

I have tried using EXCEPT, which almost works, unfortunately it returns false when table B has more Colors than table A, which is irrelevant all i care about is if every distinct Color from table A is in table B. I have been fiddling with both EXISTS and IN but can't see to get the correct results

declare @TableA table (Color varchar(10))
declare @TableB table (Color varchar(10))

insert into @TableA(Color) values ('red')
insert into @TableA(Color) values ('blue')
insert into @TableA(Color) values ('green')
--insert into @TableA(Color) values ('orange')


insert into @TableB(Color) values ('red')
insert into @TableB(Color) values ('blue')
insert into @TableB(Color) values ('green')
insert into @TableB(Color) values ('yellow')
insert into @TableB(Color) values ('purple')

IF NOT EXISTS (
    SELECT Color FROM @TableA
    EXCEPT
    SELECT Color FROM @TableB
)
SELECT 'true'
ELSE SELECT 'false'

I would like the above code to yield 'true'. 

IF table A Colors > table B Colors THEN false
IF table A Colors <= table B Colors THEN true.
sql-server sql
2 Answers

There are many ways to accomplish this. You could use a left join for this pretty easily. 

if exists
(
    SELECT a.Color 
    FROM @TableA a
    left join @TableB b on b.Color = a.Color
    where b.Color is null
) 
    select 'Some Colors in A are not in B'
else
    select 'ALL Colors in A exist in B'
user_38131160

you could also just use your existing query and add DISTINCT:

IF NOT EXISTS (
    SELECT DISTINCT Color FROM @TableA
    EXCEPT
    SELECT DISTINCT Color FROM @TableB
)
SELECT 'true'
ELSE SELECT 'false'
user_35641100
Related