Weird Thing with Inner Queries in SQL SERVER 2005

• Download source - 893 B

Introduction

This article helps you to understand how inner queries work in relation to outer queries and what problems they can create if not used in the proper manner.

Using the Code

Few days ago, I found a very strange thing with SQL SERVER 2005 and I strongly feel that THIS IS A BUG in SQL SERVER which actually can create lots of data discrepancies.

To prove my point, I have created a sample SQL Script. I want you to run the script in SQL SERVER.

Let's go through the script.

• Create a new fresh database.

  CREATE DATABASE DB_TEST

• Use this database.

  USE DB_TEST

• Create a table named tblCategory.

  CREATE TABLE tblCategory
  (
      CategoryID INT PRIMARY KEY,
      CategoryNAME VARCHAR(50)
  )

• Create another table named tblProduct.

  CREATE TABLE tblProduct
  (
      ProductID INT PRIMARY KEY,
      CategoryID INT FOREIGN KEY REFERENCES tblCategory(CategoryID),
      IsDamaged BIT
  )

• Insert 5 rows in tblCategory.

  INSERT INTO tblCategory VALUES (1,'Category1')
  INSERT INTO tblCategory VALUES (2,'Category2')
  INSERT INTO tblCategory VALUES (3,'Category3')
  INSERT INTO tblCategory VALUES (4,'Category4')
  INSERT INTO tblCategory VALUES (5,'Category5') 

• Insert 10 rows in tblProduct.

  INSERT INTO tblProduct VALUES (1,1,0)
  INSERT INTO tblProduct VALUES (2,1,0)
  INSERT INTO tblProduct VALUES (3,2,0)
  INSERT INTO tblProduct VALUES (4,2,0)
  INSERT INTO tblProduct VALUES (5,3,0)
  INSERT INTO tblProduct VALUES (6,3,0)
  INSERT INTO tblProduct VALUES (7,4,0)
  INSERT INTO tblProduct VALUES (8,4,0)
  INSERT INTO tblProduct VALUES (9,4,0)
  INSERT INTO tblProduct VALUES (10,5,0)

• Select statements to confirm whether data is entered or not.

  SELECT * FROM tblCategory
  SELECT * FROM tblProduct 

• Here is a select query which is incorrect. The query tells that select ProductID from tblCategory where categoryId = 1, but the tblCategory table does not have a column named ProductID. So when we execute this query, it throws an error and that is the expected behaviour.

  SELECT ProductID FROM tblCategory WHERE CategoryID = 1

• Here is the magic. I have used the above incorrect select query with an update statement as an inner query. What do you think, what should happen when you execute this query? This query should throw an error as my inner select query is not correct. But just execute this query and you will be shocked.

  UPDATE tblProduct SET IsDamaged = 1 
  WHERE ProductID IN 
  (SELECT ProductID FROM tblCategory WHERE CategoryID = 1)

• Oops!!!! 10 rows affected. All the data in IsDamaged is set to 1 but my inner select query (SELECT ProductID FROM tblCategory WHERE CategoryID = 1) is wrong.

Initially, I thought that this is a bug but actually it’s not. The inner query first tries to find the column in the current table (inner query’s table) and if it does not find one, then it will look for the outer query table. It is the best practice to use the tableName.ColumnName in the inner query.

UPDATE tblProduct SET IsDamaged = 1 
WHERE ProductID IN 
(SELECT tblCategory.ProductID FROM tblCategory WHERE CategoryID = 1)

Now this inner query will throw an error. So next time be careful whenever you are working with inner queries.

History

• 17^th February, 2009: Initial post