best approach

I have two tables with same datatypes. I need to view all the rows
from both tables and check to see if they exist in a third table while
viewing the data. My plan thus far is to use a union query and a
function that returns a bit showing if they are in third table. Should
I use this approach (shown below) or should this be a join query
between the union portion and third table with consideration to speed
and viability?
select chemical, fncExist(cid) as exist
from table1
union all
select chemical, fncExist(cid) as exist
from table2
where:
CREATE FUNCTION dbo.fncExist
(
@.cid int
)
RETURNS bit
AS
BEGIN
declare @.exist int
declare @.bln bit
select @.exist =Count(id) from table3 where vid = @.cid
if @.exist>0
begin
@.bln=1
end
else
begin
@.bln=0
end
RETURN @.bln
ENDUsing a scalar function like this can really bring performance down to
snail-on-glue speed.
Try something like this:
select <column list>
,exist
= case when <third table>.<unique key> is null
then 0
else 1
end
from (
select chemical
,cid
from table1
union all
select chemical
,cid
from table2
) Both
left join <third table>
on <third table>.cid = Both.cid
ML
http://milambda.blogspot.com/|||Thanks thats what I needed to know.