Applying joins conditionally in SQL Server

Question

I have some set of records, but now i have to select only those records from this set which have theeir Id in either of the two tables.

Suppose I have table1 which contains

Id  Name
----------
1   Name1
2   Name2

Now I need to select only those records from table one which have either their id in table2 or in table3

I was trying to apply or operator witin inner join like:

select * 
from table1 
inner join table2 on table2.id = table1.id or
inner join table3 on table3.id = table1.id.

Is it possible? What is the best method to approach this? Actually I am also not able to use

if exist(select 1 from table2 where id=table1.id) then select from table1

Could someone help me to get over this?

You are half way through :select * from table1 inner join table2 on table2.id =table1.id inner join table3 on table3.id=table1.id. — TheGameiswar, Jul 11 '16 at 10:55
read about joins here :http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins/27458534 — TheGameiswar, Jul 11 '16 at 10:56

score 2 · Answer 1 · answered Jul 11 '16 at 10:55

2

Use left join and then check if at least one of the joins has found a relation

select t1.* 
from table1 t1 
left join table2 t2 on t2.id = t1.id 
left join table3 t3 on t3.id = t1.id
where t2.id is not null 
   or t3.is is not null

answered Jul 11 '16 at 10:55

juergen d

201,996
37
293
362

score 1 · Answer 2 · answered Jul 11 '16 at 10:58

1

I think the most efficient way is to use UNION on table2 and table3 and join to it :

SELECT t1.*
FROM table1 t1
INNER JOIN(SELECT id FROM Table2
           UNION
           SELECT id FROM Table3) s
 ON(t.id = s.id)

answered Jul 11 '16 at 10:58

sagi

40,026
6
59
84

score 1 · Accepted Answer · answered Jul 11 '16 at 10:58

I would be inclined to use exists:

select t1.*
from table1 t1
where exists (select 1 from table2 t2 where t2.id = t1.id) or
      exists (select 1 from table3 t3 where t3.id = t1.id) ;

The advantage to using exists (or in) over a join involves duplicate rows. If table2 or table3 have multiple rows for a given id, then a version using join will produce multiple rows in the result set.

score 1 · Answer 4 · answered Jul 12 '16 at 04:49

1

Alternatively, you can use below SQL as well:

SELECT  *
FROM    dbo.Table1
WHERE   id Table1.IN ( SELECT  table2.id
                FROM    dbo.table2 )
        OR Table1.id IN ( SELECT   table3.id
                   FROM     Table3 )

answered Jul 12 '16 at 04:49

varun kumar dutta

202
1
4
10

Applying joins conditionally in SQL Server

4 Answers4