我的观点是存在即合理,我认为只有在IN里面是固定值的时候才可以用IN和NOT IN,
正确写法:
SELECT [FirstName] ,[MiddleName] ,[LastName] FROM [AdventureWorks].[Person].[Contact] WHERE ContactID IN (25,33)
in()条件里是固定数值。
错误写法:SELECT [FirstName] ,[MiddleName] ,[LastName] FROM [AdventureWorks].[Person].[Contact] WHERE ContactID IN (SELECT EmployeeID FROM [AdventureWorks].[HumanResources].[Employee] WHERE SickLeaveHours>68)相关子查询和EXISTS关键字
前面所说的查询都是无关子查询(Uncorrelated subquery),子查询中还有一类很重要的查询是相关子查询(Correlated subquery),也叫重复子查询比如,还是上面那个查询,用相关子查询来写:
我想取得总共请病假天数大于68天的员工:
SELECT [FirstName] ,[MiddleName] ,[LastName] FROM [AdventureWorks].[Person].[Contact] c WHERE EXISTS (SELECT * FROM [AdventureWorks].[HumanResources].[Employee] e WHERE c.ContactID=e.ContactID AND e.SickLeaveHours>68)此果和使用IN关键字的查询结果相同:
如何区别相关子查询和无关子查询呢?最简单的办法就是直接看子查询本身能否执行,比如上面的例子中的子查询:
(SELECT * FROM [AdventureWorks].[HumanResources].[Employee] e WHERE c.ContactID=e.ContactID AND e.SickLeaveHours>68)总结:
select * from Table where ID in (33,25) 正确
select * from Table where ID in(select * from .....) 错误 in 后面最好不要有查询
select * from Table where Exists(select * from ....) 正确
select * from TableIn where exists(select BID from TableEx where BNAME=TableIn.ANAME)
select * from TableIn where ANAME in(select BNAME from TableEx)