子查询 in 潜在的问题 - 建议最好别用

转至：http://wiki.lessthandot.com/index.php/Subquery_typo_with_using_in

Subquery typo with using in

From Wiki

 

Jump to: navigation, search

Do you use the following syntax?

tsqlLine number Off | Hide | Select all

1. SELECT *
2.     FROM TABLE
3.     WHERE COLUMN IN (SELECT COLUMN FROM TABLE)

 

OR this?

tsqlLine number Off | Hide | Select all

1. SELECT *
2.     FROM TABLE
3.     WHERE COLUMN NOT IN (SELECT COLUMN FROM TABLE)

 

Do NOT use that, it will cause problems sooner or later. Don't believe me? Let's take a look

First create these 2 tables and populate them with some sample data

tsqlLine number Off | Hide | Select all

1. CREATE TABLE TestTable1 (id1 INT)
2.     CREATE TABLE TestTable2 (id2 INT)
3.      
4.     INSERT TestTable1 VALUES(1)
5.     INSERT TestTable1 VALUES(2)
6.     INSERT TestTable1 VALUES(3)
7.      
8.      
9.     INSERT TestTable2 VALUES(1)
10.     INSERT TestTable2 VALUES(2)

 

Now let's run the IN query

tsqlLine number Off | Hide | Select all

1. SELECT *
2.     FROM TestTable1
3.     WHERE id1 IN (SELECT id2 FROM TestTable2)

 

 

id1

1

2

No problems here right?

What if by mistake you wrote id1 instead of id2?

tsqlLine number Off | Hide | Select all

1. SELECT *
2.     FROM TestTable1
3.     WHERE id1 IN (SELECT id1 FROM TestTable2)

 

 

id1

1

2

3

Oops all 3 rows are returned, if you just run this SELECT id1 FROM TestTable2 you will get this error Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'id1'.

So what happens? SQL Server sees column id1 and says "yes I have that it is in the TestTable1 table, I can use that" What can we do? Use EXISTS because you will get an error instead of a wrong resultset

tsqlLine number Off | Hide | Select all

1. SELECT *
2.     FROM t1
3.     WHERE EXISTS (SELECT * FROM TestTable2 t2 WHERE t2.id2 = t1.id1 )

 

 

id1

1

2

A JOIN will do the same as EXISTS

tsqlLine number Off | Hide | Select all

1. SELECT t1.*
2.     FROM TestTable1 t1
3.     JOIN TestTable2 t2 ON t2.id2 = t1.id1

 

 

id1

1

2

 

Now let's try NOT IN

 

tsqlLine number Off | Hide | Select all

1. SELECT *
2.     FROM TestTable1
3.     WHERE id1 NOT IN (SELECT id2 FROM TestTable2)

 

 

id1

3

No problem right?

Add a NULL value to the TestTable2 table

tsqlLine number Off | Hide | Select all

1. INSERT TestTable2 VALUES(NULL)

 

 

Let's try running it again

tsqlLine number Off | Hide | Select all

1. SELECT *
2.     FROM TestTable1
3.     WHERE id1 NOT IN (SELECT id2 FROM TestTable2)

 

Where are my rows? Nowhere, since NULL is not equal to anything including another NULL SQL just returns nothing

What happens when you use NOT EXISTS?

tsqlLine number Off | Hide | Select all

1. SELECT *
2.     FROM TestTable1 t1
3.     WHERE NOT EXISTS (SELECT * FROM TestTable2 t2 WHERE t2.id2 = t1.id1 )

 

 

id1

3

That works without a problem

 

What about a LEFT JOIN?

tsqlLine number Off | Hide | Select all

1. SELECT t1.*
2.     FROM TestTable1 t1
3.     LEFT JOIN TestTable2 t2 ON t2.id2 = t1.id1
4.     WHERE t2.id2 IS NULL

 

 

id1

3

That works without a problem also

 

So from now on use EXISTS, NOT EXISTS, JOIN and LEFT JOIN

DO NOT use IN or NOT IN ever again!