SQL子查询

 TAccounts

 

一、单值子查询
只有返回且仅返回一行、一列数据的子查询才能当成单值子查询。

示例:

SELECT * FROM TOrders
WHERE FUserId = ( SELECT FID FROM TAccounts WHERE FID = 43672 )

 

 

二、多值子查询
如果子查询是多行单列的子查询，这样的子查询的结果集其实是一个集合，那么可以使用in关键字代替=号。

示例：

SELECT * FROM TOrders
WHERE FUserId IN ( SELECT FID FROM TAccounts WHERE FCompanyID = 36052 )

 

 

 

Exists
EXISTS是用来判断是否存在的。
当EXISTS查询中的查询存在结果时则返回真，否则返回假。NOT EXISTS则相反。

 

优化查询：

--SELECT * FROM TOrders
--WHERE FUserId = ( SELECT FID FROM TAccounts WHERE FID = 43672 )
SELECT * FROM TOrders AS a
WHERE EXISTS ( 
SELECT FID FROM TAccounts AS b 
WHERE b.FID=a.FUserID 
AND b.FID = 43672 
)


--SELECT * FROM TOrders
--WHERE FUserId IN ( SELECT FID FROM TAccounts WHERE FCompanyID = 36052 )
SELECT * FROM TOrders AS a
WHERE EXISTS ( 
SELECT FID FROM TAccounts AS b 
WHERE b.FID=a.FUserID 
AND b.FCompanyID = 36052 
)

 

连接优化：

--SELECT * FROM TOrders
--WHERE FUserId = (SELECT FID FROM TAccounts WHERE FID = 43672)
SELECT a.* FROM TOrders a
INNER JOIN dbo.TAccounts b ON b.FID=a.FUserID AND b.FID=43672


--SELECT * FROM TOrders
--WHERE FUserId IN (SELECT FID FROM TAccounts WHERE FCompanyID = 36052)
SELECT a.* FROM TOrders a
INNER JOIN dbo.TAccounts b ON b.FID=a.FUserID AND b.FCompanyID=36052