My knee-jerk reaction is usually to just look at the query plan; that is also what I would suggest in this case: Look at the query plan.

Another alternative might be something like:

table (id int, profileName varchar(12))

insert into @profiles

select 1, 'Joe Soap' union all

select 2, 'Jane Done' union all

select 3, 'Ben Missing'

declare @groups table

(id int, groupName varchar(12),...

Perhaps something like:

declare @tabB table (Col1 int, Col2 int)

insert into @tabB

select 1, 4 union all

select 2, 9 union all

select 3, 8 union all

select 4, 9

select * from @tabB

where col2 =...

Very good, thank you Adam

Yes, I think that specifically WHERE EXISTS will beat EXCEPT; but as you said, it is always best to run some timings and compare the results.

Keep in mind that the EXCEPT operator will frequently be slower than other potential options. Here is a thread at MSDN that I recall from about a year ago:

I thought that if you were not using the sysadmin privilege that the windows access was defined by the proxy account designated by the sp_xp_cmdshell_proxy_account stored procedure -- or is...