在阅读它之后,这不是显式与隐式SQL连接的副本。 答案可能相关(甚至相同),但问题是不同的。
它们之间有什么不同?每一种都应该有什么不同?
如果我正确地理解了这个理论,那么查询优化器应该能够互换地使用这两种方法。
在阅读它之后,这不是显式与隐式SQL连接的副本。 答案可能相关(甚至相同),但问题是不同的。
它们之间有什么不同?每一种都应该有什么不同?
如果我正确地理解了这个理论,那么查询优化器应该能够互换地使用这两种方法。
当前回答
关于你的问题,
只要你的服务器能得到它,内部连接的'on'和'where'都是一样的:
select * from a inner join b on a.c = b.c
and
select * from a inner join b where a.c = b.c
并非所有口译员都知道“where”选项,所以可能应该避免使用。当然,“on”从句更清楚。
其他回答
从字面上看,它们是等价的。
在大多数开源数据库(最著名的例子是MySql和postgresql)中,查询计划是出现在关系数据库管理系统中的访问路径选择(Selinger et al, 1979)中的经典算法的变体。在这种方法中,条件有两种类型
引用单个表的条件(用于筛选) 引用两个表的条件(不管它们出现在哪里,都被视为连接条件)
特别是在MySql中,通过跟踪优化器,您可以看到自己的join ..On条件在解析过程中被等价的where条件替换。类似的事情也发生在postgresql中(虽然没有办法通过日志看到它,你必须阅读源代码描述)。
无论如何,主要的一点是,这两种语法变体之间的差异在解析/查询-重写阶段就消失了,甚至没有达到查询计划和执行阶段。因此,就性能而言,它们是否相同是毫无疑问的,它们在进入执行阶段之前就已经完全相同了。
你可以用explain来验证他们的计划是否相同。例如,在postgres中,计划将包含一个join子句,即使你没有在语法中使用join..on。
Oracle和SQL server不是开源的,但是,据我所知,它们基于等价规则(类似于关系代数中的规则),并且它们在这两种情况下也产生相同的执行计划。
显然,对于外部连接,这两种语法风格是不相等的,对于那些你必须使用join…在语法
当涉及到左连接时,where子句和on子句之间有很大的区别。
这里有一个例子:
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| fid | int(11) | NO | | NULL | |
| v | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
fid是表t2的id。
mysql> desc t2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| v | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
查询“on子句”:
mysql> SELECT * FROM `t1` left join t2 on fid = t2.id AND t1.v = 'K'
-> ;
+----+-----+---+------+------+
| id | fid | v | id | v |
+----+-----+---+------+------+
| 1 | 1 | H | NULL | NULL |
| 2 | 1 | B | NULL | NULL |
| 3 | 2 | H | NULL | NULL |
| 4 | 7 | K | NULL | NULL |
| 5 | 5 | L | NULL | NULL |
+----+-----+---+------+------+
5 rows in set (0.00 sec)
where子句查询:
mysql> SELECT * FROM `t1` left join t2 on fid = t2.id where t1.v = 'K';
+----+-----+---+------+------+
| id | fid | v | id | v |
+----+-----+---+------+------+
| 4 | 7 | K | NULL | NULL |
+----+-----+---+------+------+
1 row in set (0.00 sec)
很明显, 第一个查询从t1返回一条记录,从t2返回与t1相关的行(如果有的话)。v = 'K'。
第二个查询返回来自t1的行,但只针对t1。v = 'K'将有任何与它相关的行。
a. WHERE子句:加入后,记录将被过滤。
b. ON子句-在加入之前,记录(来自右边表格)将被过滤。
简短的回答
这取决于连接类型是INNER还是OUTER。
对于INNER JOIN,答案是肯定的,因为INNER JOIN语句可以重写为带有WHERE子句的CROSS JOIN,该子句与您在INNER JOIN查询的ON子句中使用的条件相同。
但是,这只适用于INNER JOIN,不适用于OUTER JOIN。
长回答
考虑到我们有如下的post和post_comment表:
该职位有以下记录:
| id | title |
|----|-----------|
| 1 | Java |
| 2 | Hibernate |
| 3 | JPA |
并且post_comment有以下三行:
| id | review | post_id |
|----|-----------|---------|
| 1 | Good | 1 |
| 2 | Excellent | 1 |
| 3 | Awesome | 2 |
SQL内部连接
SQL JOIN子句允许您关联属于不同表的行。例如,CROSS JOIN将创建一个笛卡尔积(Cartesian Product),其中包含两个连接表之间所有可能的行组合。
虽然CROSS JOIN在某些场景中很有用,但大多数情况下,您希望基于特定的条件来连接表。这就是INNER JOIN发挥作用的地方。
SQL INNER JOIN允许我们根据通过on子句指定的条件筛选连接两个表的笛卡尔积。
SQL内部连接-对“始终为真”的条件
如果您提供了一个“always true”条件,INNER JOIN将不会过滤连接的记录,结果集将包含两个连接表的笛卡尔积。
例如,如果我们执行下面的SQL INNER JOIN查询:
SELECT
p.id AS "p.id",
pc.id AS "pc.id"
FROM post p
INNER JOIN post_comment pc ON 1 = 1
我们将得到post和post_comment记录的所有组合:
| p.id | pc.id |
|---------|------------|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
因此,如果ON子句条件为"always true", INNER JOIN就相当于一个CROSS JOIN查询:
SELECT
p.id AS "p.id",
pc.id AS "pc.id"
FROM post p
CROSS JOIN post_comment
WHERE 1 = 1
ORDER BY p.id, pc.id
SQL INNER JOIN - ON "always false"条件
另一方面,如果On子句条件为“always false”,则所有连接的记录将被过滤掉,结果集将为空。
因此,如果我们执行下面的SQL INNER JOIN查询:
SELECT
p.id AS "p.id",
pc.id AS "pc.id"
FROM post p
INNER JOIN post_comment pc ON 1 = 0
ORDER BY p.id, pc.id
我们不会得到任何结果:
| p.id | pc.id |
|---------|------------|
这是因为上面的查询相当于下面的CROSS JOIN查询:
SELECT
p.id AS "p.id",
pc.id AS "pc.id"
FROM post p
CROSS JOIN post_comment
WHERE 1 = 0
ORDER BY p.id, pc.id
SQL INNER JOIN - ON子句使用外键和主键列
最常见的ON子句条件是子表中的外键列与父表中的主键列相匹配的条件,如下面的查询所示:
SELECT
p.id AS "p.id",
pc.post_id AS "pc.post_id",
pc.id AS "pc.id",
p.title AS "p.title",
pc.review AS "pc.review"
FROM post p
INNER JOIN post_comment pc ON pc.post_id = p.id
ORDER BY p.id, pc.id
当执行上面的SQL INNER JOIN查询时,我们得到以下结果集:
| p.id | pc.post_id | pc.id | p.title | pc.review |
|---------|------------|------------|------------|-----------|
| 1 | 1 | 1 | Java | Good |
| 1 | 1 | 2 | Java | Excellent |
| 2 | 2 | 3 | Hibernate | Awesome |
因此,只有匹配ON子句条件的记录才包含在查询结果集中。在我们的例子中,结果集包含了所有的帖子及其post_comment记录。没有关联post_comment的发布行将被排除,因为它们不能满足ON子句条件。
同样,上面的SQL INNER JOIN查询等价于下面的CROSS JOIN查询:
SELECT
p.id AS "p.id",
pc.post_id AS "pc.post_id",
pc.id AS "pc.id",
p.title AS "p.title",
pc.review AS "pc.review"
FROM post p, post_comment pc
WHERE pc.post_id = p.id
未取消的行是满足WHERE子句的行,结果集中只包括这些记录。这是可视化INNER JOIN子句如何工作的最好方法。
| p.id | pc.post_id | pc.id | p.title | pc.review | |------|------------|-------|-----------|-----------| | 1 | 1 | 1 | Java | Good | | 1 | 1 | 2 | Java | Excellent || 1 | 2 | 3 | Java | Awesome || 2 | 1 | 1 | Hibernate | Good || 2 | 1 | 2 | Hibernate | Excellent || 2 | 2 | 3 | Hibernate | Awesome || 3 | 1 | 1 | JPA | Good || 3 | 1 | 2 | JPA | Excellent || 3 | 2 | 3 | JPA | Awesome |
结论
INNER JOIN语句可以重写为带有WHERE子句的CROSS JOIN,该子句与在INNER JOIN查询的ON子句中使用的条件相同。
并不是说这只适用于INNER JOIN,而不适用于OUTER JOIN。
通常,一旦两个表已经连接,就在WHERE子句中处理筛选。这是有可能的,不过您可能希望在连接它们之前过滤一个或两个表。 也就是说,where子句适用于整个结果集,而on子句只适用于相关的连接。