Click here to Skip to main content
11,645,087 members (66,935 online)
Click here to Skip to main content

Weird Thing with Inner Queries in SQL SERVER 2005

, 17 Feb 2009 CPOL 14.6K 41 10
Rate this:
Please Sign up or sign in to vote.
How inner queries work in SQL Server

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

  • 17th February, 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

jQuery By Example
Technical Lead
India India
I am an experienced Software Developer with 6+ years of hands-on experience working with Microsoft .NET technology (ASP.NET, C#, VB.NET,SQL Server).

Visit my blog and you can also follow me on Facebook

You may also be interested in...

Comments and Discussions

 
AnswerGood Article Pin
Steven.Pinto20004-Aug-11 18:29
groupSteven.Pinto20004-Aug-11 18:29 
GeneralGood One Pin
pinaldave24-Feb-09 19:16
memberpinaldave24-Feb-09 19:16 
GeneralRe: Good One Pin
Virendra Dugar24-Feb-09 21:52
memberVirendra Dugar24-Feb-09 21:52 
GeneralRe: Good One Pin
Steven.Pinto20004-Aug-11 18:32
groupSteven.Pinto20004-Aug-11 18:32 
GeneralNot a bug, your SQL is ambiguous Pin
IainWildman24-Feb-09 1:16
memberIainWildman24-Feb-09 1:16 
GeneralRe: Not a bug, your SQL is ambiguous Pin
Virendra Dugar24-Feb-09 17:02
memberVirendra Dugar24-Feb-09 17:02 
GeneralRe: Not a bug, your SQL is ambiguous Pin
Richard Deeming4-Mar-09 8:20
memberRichard Deeming4-Mar-09 8:20 
GeneralThanks!!! Pin
AD858518017-Feb-09 17:30
memberAD858518017-Feb-09 17:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150731.1 | Last Updated 17 Feb 2009
Article Copyright 2009 by jQuery By Example
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid