利用Exists的语句:
USE Northwind
GO
SELECT CategoryName
FROM Categories
WHERE Exists
(SELECT *
from Categories
where CategoryName=Categories.CategoryName ----这里你忘写了!!! and CategoryID=2
)
ORDER BY CategoryName ASC
GO
USE Northwind
GO
SELECT CategoryName
FROM Categories
WHERE Exists
(SELECT *
from Categories
where CategoryName=Categories.CategoryName ----这里你忘写了!!! and CategoryID=2
)
ORDER BY CategoryName ASC
GO
第一句:把CategoryID=2的CategoryName SELECT出来;
第二句:如果有CategoryID=2的记录存在,那么就把所有的CategoryName SELECT出来。如果要把它改到跟第一句的效果一样,就要这样了:
USE Northwind
GO
SELECT CategoryName
FROM Categories a
WHERE Exists
(SELECT b.*
from Categories b
where a.CategoryID=2
)
ORDER BY CategoryName ASC
GO
我的理解:
关于
WHERE Exists
(SELECT *
from Categories
where CategoryID=2
)
如果有CategoryID=2的记录存在,那么就把所有的CategoryName 中CategoryID=2的出来
为什么是所有的呢,请指教
SELECT *
from Categories
where CategoryID=2
这句返回的结果不是空的话,那么就
Exists
(SELECT *
from Categories
where CategoryID=2
)返回的就是TRUE,也就是说整句变成了SELECT * FROM TABLE1 WHERE TRUE了
SELECT CategoryName
FROM Categories a
WHERE Exists
(SELECT 1
from Categories b
where b.CategoryName=a.CategoryName and b.CategoryID=2
)
ORDER BY CategoryName ASC
GO
GO
SELECT CategoryName
FROM Categories a
WHERE Exists
(SELECT *
from Categories
where CategoryName=a.CategoryName ----这里你忘写了!!! and CategoryID=2
)
ORDER BY CategoryName ASC
GO
SELECT CategoryName
FROM Categories a ----这里加一个别名,为了区分子查询的表名
WHERE Exists
(SELECT *
from Categories
where CategoryName=a.CategoryName ----加一个别名!!!!!!!! and CategoryID=2
)
ORDER BY CategoryName ASC
GO
(SELECT *
from Categories b
where b.CategoryName=a.CategoryName and b.CategoryID=2
)
和
WHERE Exists
(SELECT *
from Categories
where CategoryID=2
)
的返回值不是都是true吗,有什么区别呢?
偶实在想不通...谢谢了
(SELECT *
from Categories b
where b.CategoryName=a.CategoryName and b.CategoryID=2
)
除了返回true还有其它的返回值吗?
sql的帮助好像也没说清楚请指教!!!
在你原来的语句中,因为判断的条件时固定的,所以返回的值跟取得的值没有联系,所以要么返回全部的结果集,要么就没有
--利用In的语句:
---------------------------------------------
USE Northwind
GO
SELECT CategoryName
FROM Categories
WHERE CategoryName in
(SELECT CategoryName
from Categories
where CategoryID=2
)
ORDER BY CategoryName ASC
GO
-----------------------------------------------------------------
--利用Exists的语句:
USE Northwind
GO
SELECT CategoryName
FROM Categories a
WHERE Exists
(SELECT *
from Categories b
where CategoryID=2
and CategoryName = a.CategoryName
)
ORDER BY CategoryName ASC
GO
这个样子就好了
如果要让它与In语句等效的话,要将外部一层查询的CategoryName与内一层的(即CategoryID=2所查询出来的结果的CategoryName进行关联),这样子的话,除了返回真值外,还另外做了一个关联的动作,所以可以将查询出来的结果与In语句的结果达成一致。