引子
我们都知道多表查询有 JOIN
和 UNION
。
JOIN
有分为 INNER JOIN
(内连接)、OUTER JOIN
(外连接,外连接又分为左连接LEFT JOIN
、右连接RIGHT JOIN
)、CROSS JOIN
(交叉连接,也就是笛卡尔积)。
UNION
(称为联合查询,或者说 合并查询结果),利用UNION
关键字,可以给出多条SELECT
语句,并将它们的结果组合成单个结果集。合并
时,两个表对应的列数和数据类型必须相同,并且相互对应。各个SELECT
语句之间使用UNION
或UNION ALL
关键字分隔。
语法格式:
SELECT column,... FROM table1
UNION [ALL]
SELECT column,... FROM table2
UNION
操作符返回两个查询的结果集的并集,去除重复记录。
UNION ALL
操作符返回两个查询的结果集的并集。对于两个结果集的重复部分,不去重。
注意:执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据
不存在重复数据,或者不需要去除重复的数据,则尽量使用UNION ALL语句,以提高数据查询的效
率。
FROM table1,table2 不为人知的事儿
就拿下面这段语句举例吧
SELECT e1.name
FROM Employee e1, Employee e2
WHERE e1.id = e2.managerId
GROUP BY e1.id
HAVING COUNT(e1.id) >= 5
FROM Employee e1, Employee e2
这部分构成了一个多表查询,具体来说,它是一种笛卡尔积(Cartesian product)查询,但通常我们将其视为内连接(INNER JOIN
)的一个未命名形式,尽管语法上并没有明确使用JOIN
关键字。
在SQL中,当从多个表中选择数据时,如果不使用JOIN
关键字,而是直接在FROM
子句中列出多个表,那么默认的行为是执行笛卡尔积。这意味着如果没有其他条件限制,每一张表的每一行都会与所有其他表的每一行组合在一起,生成所有可能的行组合。
然而,在提供的查询中,WHERE
子句包含了e1.id = e2.managerId
条件,这实际上是在执行一个隐式的内连接(Inner Join),因为它限制了结果集到那些满足这个条件的行组合。所以,虽然表面上看起来是笛卡尔积,但实际上是一个内连接操作。
亦或者是另一种解释(但意思相同)
这段SQL语句中的 FROM Employee e1, Employee e2
构成了多表查询,更具体地讲,它是一种隐式连接(Implicit Join),也常被称为逗号连接(Comma Join)。在SQL中,当两个或多个表在FROM
子句中通过逗号分隔时,并且没有显式使用JOIN关键字,这种查询会先产生一个笛卡尔积(即所有可能的行对组合),然后WHERE子句中的条件被用来过滤这个结果集,从而实现类似内连接的效果。
SELECT e1.name
FROM Employee e1, Employee e2
WHERE e1.id = e2.managerId
GROUP BY e1.id
HAVING COUNT(e1.id) >= 5;
对上述SQL语句进行解释
FROM Employee e1, Employee e2
首先尝试将Employee
表的所有行与自身进行笛卡尔积。WHERE e1.id = e2.managerId
然后过滤掉那些不满足e1.id
和e2.managerId
匹配的行组合,这样就实现了只保留那些经理与员工关系的行。GROUP BY e1.id
将结果按照经理的ID进行分组。HAVING COUNT(e1.id) >= 5
最后,只保留那些至少有5个下属的经理。
正确的、现代的SQL写法会使用显式的JOIN
关键字来表达这样的连接,这样代码的可读性会更好。等价的显式内连接写法如下:
SELECT e1.name
FROM Employee e1
INNER JOIN Employee e2 ON e1.id = e2.managerId
GROUP BY e1.id
HAVING COUNT(e1.id) >= 5;