.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Jean Paul
ASPEvil
SP
satyapriyanayak
Ashutosh Jha

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Weird Thing with Inner Queries in SQL SERVER 2005

Posted By:Virendra Dugar       Posted Date: October 19, 2009    Points: 25    Category: DataBase    URL: http://jquerybyexample.blogspot.com/  
 

Weird Thing with Inner Queries in SQL SERVER 2005.

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.

Understand the script

Few days ago, I found a very strange thing with SQL SERVER 2005 and I strongly feet that THIS IS A BUG in SQL SERVER (But actually it's not) 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.


Conclusion

So guys, whenever you use inner query, make sure you always use the tableName.Columnname.

Enjoy...


 Subscribe to Articles

     

Further Readings:

Responses
Author: Santosh         Company URL: http://santoshdotnetarena.blogspot.com
Posted Date: October 20, 2009

HI Virendra Dugar

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

here the condition u r giving (WHERE CategoryID = 1) but it's updating all the record,
so that's is wrong ?? it's not checking the condition.

u check the condition(WHERE 1 = 1) in sql server what it does, it will do same work like ur condition.

can u plz tell me the diff between these two.

Regards
Santosh
Author: Virendra Dugar         Company URL: http://jquerybyexample.blogspot.com/
Posted Date: October 20, 2009

Hi Santosh,

The condition is not wrong. Infact the query is wrong.

(SELECT ProductID FROM tblCategory WHERE CategoryID = 1). This query is wrong as tblCategory table is not having a column named "ProductID".

But this query is used as inner query, it's working. Instead of giving any error.

Let me know in case of any concern.

Thanks,
Virenda Dugar
Author: Santosh         Company URL: http://santoshdotnetarena.blogspot.com
Posted Date: October 21, 2009

hi Virendra Dugar
this is working nor throwing any run-time error but it''s not updating according to condition u given.so that''s is user less inner query.
bacaz if it won''t check the condition.


Let me know the use of this inner query.

Regards
Santosh
Author: Virendra Dugar         Company URL: http://jquerybyexample.blogspot.com/
Posted Date: October 21, 2009

Hi Santosh,

This is what I am trying to say. This query is wrong and it's should throw an error but it is not throwing any error.

How do you expect to update the records according to the condition when query is wrong?

Regards,
Virendra Dugar

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend