Misbehaving Subqueries!!! What I Do?

Misbehaving Subqueries!!!  Then why it is not seize up by compiler??? Then what should I do?

There are many tricky programming error involving subqueries  which even compiler parse it correctly. We will be describing those bugs then make the recommendation, what we can do to avoid such bugs.

Let’s take an example to see where we are making mistakes in subquery.

Here in this example, I have taken two table Department and Employee.

create table Department

(dept_id int identity(1,1) primary key,

dept_name varchar(10))

create table Employee

(emp_id int identity(1,1),

emp_name varchar(10),

designation varchar(10),

d_id int references Department(dept_id) )

The dept_id in Department table is primary key, and d_id in Employee table is corresponding reference key to the dept_id of Department table.

Now I am inserting few data to both the tables

insert into Department values('HR')

insert into Department values('Operation')

insert into Department values('Marketing')

insert into Department values('Software')

insert into Employee values('Abhilash','HR Executive',1)

insert into Employee values('Vidisha ','Marketing Manager',2)

insert into Employee values('Vibhuti  ','Operation Incharge',3)

Here you can see there is no employee belonging to the Software Department.

Now suppose you are asked to return the Department name where there are no employee belonging to it, you will write a subquery to return the desired result like this below:

select dept_id, dept_name from Department

where dept_id not in (select dept_id from Employee)

The above query is supposed to return the desired result that is Software department. But to your surprise, this query returns an empty set. Can you tell why? Can you identify bug in the code?

Well, the matter is, the reference column in Employee table referencing to dept_id of Department table is not dept_id instead it is defined as d_id.

Realizing this you did probably expect the query to have failed because of invalid column name, sure enough if you run that part of subquery it will fail like this:

select dept_id from Employee

Msg 207, Level 16, State 1, Line 1

Invalid column name 'dept_id'.

However in the context of outer query, apparently the subquery is valid, though we don’t get desired result!!!

select dept_id, dept_name from Department

where dept_id not in (select dept_id from Employee)

dept_id     dept_name

----------- ----------

(0 row(s) affected)

Why So?? The name resolution process works from inner nesting level outward. The query processor first looked for dept_id in Employee Table, not having found such column name, it looked for one in Department table ( the outer level) and found it. It is interpreted as

select dept_id, dept_name from Department d

where dept_id not in (select d.dept_id from Employee e)

Logically, the query doesn’t make any sense.

To fix the problem, of course, you need to use the correct column name from Employee table.

select dept_id, dept_name from Department

where dept_id not in (select d_id from Employee)

dept_id     dept_name

----------- ----------

4           Software

(1 row(s) affected)

Recommendation!!! To avoid such bugs in future, it is good practice to always include the table name or table alias for all attribute in subquery. Like this

select d.dept_id, d.dept_name from Department d

where d.dept_id not in (select e.d_id from Employee e)

Get pleasure from coding

Post Reference: Vikram Aristocratic Elfin Share