我的理解是,IN 就像一个布尔函数,在使用 IN 时,外部查询中的行返回的每个实例都有一个值。因此,结果集不应该只有与外部查询一样多的行吗?
表 1:
DNUMBER DLOCATION
---------- ---------------
1 Houston
4 Stafford
5 Bellaire
5 Houston
5 Sugarland
表 2:
DNAME DNUMBER
-------------------- ----------
Research 5
Administration 4
Headquarters 1
那么这个查询不应该只返回 3 行吗?
SELECT D.dname, DL.dlocation
FROM department D, dept_locations DL
WHERE D.dnumber IN
(SELECT dnumber FROM dept_locations)
结果:
DNAME DLOCATION
-------------------- -------------
Headquarters Houston
Administration Houston
Research Houston
Headquarters Stafford
Administration Stafford
Research Stafford
Headquarters Bellaire
Administration Bellaire
Research Bellaire
Headquarters Houston
Administration Houston
Research Houston
Headquarters Sugarland
Administration Sugarland
Research Sugarland
SELECT D.dname, DL.dlocation
FROM department D, dept_locations DL
WHERE D.dnumber IN
(SELECT dnumber FROM dept_locations)
没有连接条件(显式或隐式)来限制要以任何方式匹配的行,因此执行交叉连接(左侧的所有行组合与右侧的行)。
你对IN的理解是正确的。您获得更多行的原因是您的主查询是从 2 个表中进行选择,而第二个表没有限制。此查询将仅返回 3 行:
SELECT D.dname
FROM department D
WHERE D.dnumber IN
(SELECT dnumber FROM dept_locations)
但是,您当然会失去自己的位置。通过将dept_locations
添加到 FROM
子句,您基本上是交叉联接表,并且您不会使用 WHERE
子句限制从第二个表返回的行,您只是限制来自department
的行
若要正确限制使用当前查询格式返回的行,可以执行以下操作:
SELECT D.dname, DL.dlocation
FROM department D, dept_locations DL
WHERE D.dnumber IN
(SELECT dnumber FROM dept_locations)
AND DL.dnumber = D.dnumber
但是获取所需数据的最佳方法可能是在此处使用JOIN
:
SELECT D.dname, DL.dlocation
FROM department D
JOIN dept_locations DL on DL.dnumber = D.dnumber
SELECT D.dname
, GROUP_CONCAT(DL.dlocation)
FROM department AS D
INNER
JOIN dept_locations AS DL
ON D.dnumber = DL.dnumber
GROUP
BY D.dname;
我做了一些假设,但我认为这将返回您尝试获取的数据。它将抓取每种类型(研究等)以及逗号分隔的位置列表。
您的查询根本不需要子查询,只需要一个适当的连接条件。