Is this the correct way to use join instead of in operator

Question

i have a small doubt, this is my query

declare @table table(id int, name varchar(100))

insert into @table values (1,'a')
insert into @table values (2,'b')
insert into @table values (3,'c')

select * from @table where id in (1,2,5)

as you can see, in the above query i hard coded 1,2,5 in the select statement. but now i want to read them from another table, so i tried as:

declare @table2 table(id int)

insert into @table2 values (1)
insert into @table2 values (2)
insert into @table2 values (5)

select t1.* from @table t1 join @table2 t2 on t1.id=t2.id

i used join here. for both the queries im getting the same result. is this the proper way. i mean instead of hard coding, im taking the values from a table.

Answer 1

Yes, that is the correct way.

You can also use:

select * from @table where id in (select id from @table2)

Or:

select * from @table t1 where exists (select * from @table2 t2 where t1.id = t2.id)

If the query optimiser manages it right, those will be executed exactly the same way as the join.

Answer 2

Additionally you could also write:

SELECT * FROM @table t1 JOIN @table2 t2 USING(id);

if the columns in both tables are named identically.