在阅读它之后,这不是显式与隐式SQL连接的副本。 答案可能相关(甚至相同),但问题是不同的。


它们之间有什么不同?每一种都应该有什么不同?

如果我正确地理解了这个理论,那么查询优化器应该能够互换地使用这两种方法。


当前回答

这就是我的解。

SELECT song_ID,songs.fullname, singers.fullname
FROM music JOIN songs ON songs.ID = music.song_ID  
JOIN singers ON singers.ID = music.singer_ID
GROUP BY songs.fullname

你必须有GROUP BY才能让它工作。

希望这对你有所帮助。

其他回答

我认为这种区别可以通过SQL中操作的逻辑顺序来最好地解释,这是简化的:

FROM(包括连接) 在哪里 集团 聚合 有 窗口 选择 截然不同的 并,相交,除 命令 抵消 获取

联接不是select语句的子句,而是FROM语句中的操作符。因此,当逻辑处理到达WHERE子句时,属于相应JOIN运算符的所有ON子句在逻辑上“已经发生”。这意味着在LEFT JOIN的情况下,例如,在应用WHERE子句时,外部连接的语义已经发生。

我已经在这篇博文中更深入地解释了下面的例子。运行此查询时:

SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id)
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE film_id < 10
GROUP BY a.actor_id, a.first_name, a.last_name
ORDER BY count(fa.film_id) ASC;

LEFT JOIN实际上没有任何有用的效果,因为即使演员没有在电影中演出,演员也会被过滤,因为它的FILM_ID将为NULL, WHERE子句将过滤这样的一行。结果是这样的:

ACTOR_ID  FIRST_NAME  LAST_NAME  COUNT
--------------------------------------
194       MERYL       ALLEN      1
198       MARY        KEITEL     1
30        SANDRA      PECK       1
85        MINNIE      ZELLWEGER  1
123       JULIANNE    DENCH      1

也就是说,就好像我们内部连接了两个表。如果我们移动ON子句中的筛选谓词,它现在成为外部连接的标准:

SELECT a.actor_id, a.first_name, a.last_name, count(fa.film_id)
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id
  AND film_id < 10
GROUP BY a.actor_id, a.first_name, a.last_name
ORDER BY count(fa.film_id) ASC;

这意味着结果将包含没有任何影片的演员,或者没有任何影片且FILM_ID < 10的演员

ACTOR_ID  FIRST_NAME  LAST_NAME     COUNT
-----------------------------------------
3         ED          CHASE         0
4         JENNIFER    DAVIS         0
5         JOHNNY      LOLLOBRIGIDA  0
6         BETTE       NICHOLSON     0
...
1         PENELOPE    GUINESS       1
200       THORA       TEMPLE        1
2         NICK        WAHLBERG      1
198       MARY        KEITEL        1

简而言之

总是把谓词放在逻辑上最有意义的地方。

就优化器而言,使用ON或WHERE定义连接子句应该没有区别。

然而,恕我直言,我认为在执行连接时使用ON子句更清楚。这样,您就有了查询的特定部分,该部分规定了如何处理连接,而不是与其余WHERE子句混合。

通常,一旦两个表已经连接,就在WHERE子句中处理筛选。这是有可能的,不过您可能希望在连接它们之前过滤一个或两个表。 也就是说,where子句适用于整个结果集,而on子句只适用于相关的连接。

当涉及到左连接时,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'将有任何与它相关的行。

从字面上看,它们是等价的。

在大多数开源数据库(最著名的例子是MySql和postgresql)中,查询计划是出现在关系数据库管理系统中的访问路径选择(Selinger et al, 1979)中的经典算法的变体。在这种方法中,条件有两种类型

引用单个表的条件(用于筛选) 引用两个表的条件(不管它们出现在哪里,都被视为连接条件)

特别是在MySql中,通过跟踪优化器,您可以看到自己的join ..On条件在解析过程中被等价的where条件替换。类似的事情也发生在postgresql中(虽然没有办法通过日志看到它,你必须阅读源代码描述)。

无论如何,主要的一点是,这两种语法变体之间的差异在解析/查询-重写阶段就消失了,甚至没有达到查询计划和执行阶段。因此,就性能而言,它们是否相同是毫无疑问的,它们在进入执行阶段之前就已经完全相同了。

你可以用explain来验证他们的计划是否相同。例如,在postgres中,计划将包含一个join子句,即使你没有在语法中使用join..on。

Oracle和SQL server不是开源的,但是,据我所知,它们基于等价规则(类似于关系代数中的规则),并且它们在这两种情况下也产生相同的执行计划。

显然,对于外部连接,这两种语法风格是不相等的,对于那些你必须使用join…在语法