Use IN clause with care

IN clause can be used as a JOIN to get data by matching the column values from other table. Although this is much similar to INNER JOIN, be careful about the usage of IN

Let us create the following data sets

use tempdb 

create table customer_master
(
cust_id int,
cust_name varchar(100)
);

insert into customer_master(cust_id,cust_name)
select 1,'Sankar' union all
select 2,'Madhivanan' union all
select 3,'Gopal';

create table premium_customers
(
cust_id int
);


insert into premium_customers(cust_id)
select 1 union all
select 2

Now to get all customers who are in premium_customers table, you can use this

select 
	* 
from 
	customer_master 
where 
	cust_id in 
		(
		select cust_id from premium_customers
		)

which returns the following result

cust_id     cust_name
----------- -----------
1           Sankar
2           Madhivanan

Now, let us re name cust_id of premium_customers into customer_id

exec sp_rename 'premium_customers.cust_id','customer_id','COLUMN'

Now execute the same query

select 
	* 
from 
	customer_master 
where 
	cust_id in 
		(
		select cust_id from premium_customers
		)

The result is

cust_id     cust_name
----------- -------------
1           Sankar
2           Madhivanan
3           Gopal

It returns ALL ROWS from customer_master table although there is no column named cust_id in premium_customers. Because it is a column in customer_master table, it is matched with customer_master table and gives all rows

How do we avoid this?

Always use table alias and qualify the columns using table alias

Executing the following code

select 
	t1.* 
from 
	customer_master as t1
where 
	t1.cust_id in 
		(
		select t2.cust_id from premium_customers as t2
		)

now throws an error

Msg 207, Level 16, State 1, Line 39
Invalid column name 'cust_id'.

So it is always a better practice to use table alias and qualify the columns with it.

Advertisement

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 )

Connecting to %s