SQL query that returns columns form table A plus a bit column that specifies with the PK of table A exists in table B

Question

How can I write an efficient SQL query that returns columns form table A plus a bit column that specifies whether the PK of table A exists in table B? I am using MS SQLServer 2005. Thanks.

Answer 1

I'm assuming that the primary key in table A is called PK and that the column in B that would contain this key is also called PK.

SELECT A.*, CASE WHEN EXISTS (SELECT 1 FROM B WHERE B.PK = A.PK) THEN 1 ELSE 0 END
FROM A

Efficiency of this query will depend on the indexing in table B -- specifically, whether column PK is indexed.

I dislike SELECT * in general, but for this example, it serves the purpose.

Answer 2

The other answers specify a correlated subquery; a (left outer) join is likely more efficient.

I'll assume that b.fk is the foreign key in b to a's pk.

select 
   a.*, 
   case when b.fk is not null then 1 else 0 end as exists_in_b
from 
   a 
   left outer join b on (a.id = b.fk ) 
;

Answer 3

It depends if the foreign table relationship is 1:1 or 1:Many. If is 1:1 the you can use a normal join:

select A.*, case when b.id is null then 0 else 1 end
from A 
left join B on A.id = B.id;

If the relation is 1:Many then the join would multiply the result, so you have to restrict it, and there are several ways. A simple way is using outer apply and top:

select A.*, case when b.id is null then 1 else 0 end
from A
outer apply (
 select top (1) id from B where A.id = B.id) as b;

As for performance, almost all solution proposed will perform the same, provided there is an appropriate index on B(id) in place.