Alias name – IN vs INNER JOIN

If you want to join two tables and get data from only one of the tables you can use either IN operator or INNER JOIN

Let us consider the following data

create table #item_master (id int, name varchar(100))
create table #sales_details (sales_id int identity(1,1), item_id int, amount decimal(12,2))


insert into #item_master(id,name)
select 1,'Helmet' union all
select 2,'Mobile' union all
select 3,'Cycle' union all
select 4,'Purse' union all
select 5,'Shirt'

 

insert into #sales_details(item_id,amount)
select 1,2000 union all
select 3,1240 union all
select 4,5660

Suppose you want to get the data from #item_master for the items that are available in #sales_details table, you can use IN operator or INNER JOIN as shown below

select * from #item_master as t1 where id in (select item_id from #sales_details)

select t1.* from #item_master as t1 inner join #sales_details as t2 on t1.id =t2.item_id


You get the following result for the above two queries

id name
----------- -------------
1 Helmet
3 Cycle
4 Purse

Now see what happens when you run the following code

select * from #item_master as t1 where id in (select id from #sales_details)

You get all data from #item_master although there is no column named id in #sales_details table it is because that if there is no alias name used and if the column is not available in #sales_details table, id is taken from #item_master table. If you look at the execution plan Predicate says it does take id from #item_master

execution_plan

So how do we solve this? Use table alias name as shown below

select * from #item_master as t1 where id in (select t2.id from #sales_details as t2 )

Now the error is

Msg 207, Level 16, State 1, Line 1
Invalid column name ‘id’.

So it is always better to use table alias when you use IN operator to avoid unexpected result

Note : This is not a problem in INNER JOIN as you can not avoid using Alias name unlike IN operator

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s