Weird Thing With Inner Queries in Sql Server

Virendra Dugar
2y
11.7k
0
1

Article

Introduction

A few days ago, I found an extraordinary thing with SQL SERVER 2005, and I strongly feel that this is a bug in SQL Server that can create lots of data discrepancies.

Let's go through the script.

Create a new fresh database.
```
CREATE DATABASE DB_TEST
```
SQL
Use this Database.
```
USE DB_TEST
```
SQL

Create a table name 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 that data is entered or not.
```
SELECT * FROM tblCategory
SELECT * FROM tblProduct
```
SQL
Here is a select query that is incorrect. The query tells us to 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, which is the expected behavior.
```
SELECT ProductID FROM tblCategory WHERE CategoryID = 1
```
SQL
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 incorrect. But execute this query, and you will be shocked.
```
UPDATE tblProduct SET IsDamaged = 1
WHERE ProductID IN
(SELECT ProductID FROM tblCategory WHERE CategoryID = 1)
```
SQL
Oops!!!! All the data in IsDamaged is set to 1, but my inner select query(SELECT ProductID FROM tblCategory WHERE CategoryID = 1) is wrong. 10 rows were affected.

Initially, I thought this was a bug, but it's not. The inner query first tries to find the column in the current table (inner query's table), and if it is not found, 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.