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 才能使其正常工作 .
希望这有帮助 .
8
在 INNER JOIN 上它们是可互换的,优化器会随意重新排列它们 .
在 OUTER JOIN 上,它们不一定是可互换的,这取决于它们所依赖的连接的哪一侧 .
我根据可读性将它们放在任何一个地方 .
0
对于内连接, WHERE 和 ON 可以互换使用 . 实际上,可以在相关子查询中使用 ON . 例如:
update mytable
set myscore=100
where exists (
select 1 from table1
inner join table2
on (table2.key = mytable.key)
inner join table3
on (table3.key = table2.key and table3.key = table1.key)
...
)
SELECT documents.name, downloads.id
FROM documents
LEFT OUTER JOIN downloads
ON documents.id = downloads.document_id
WHERE username = 'sandeep'
For above query the intermediate join table will look like this.
| id(from documents) | name | id (from downloads) | document_id | username |
|--------------------|--------------|---------------------|-------------|----------|
| 1 | Document1 | 1 | 1 | sandeep |
| 1 | Document1 | 2 | 1 | simi |
| 2 | Document2 | 3 | 2 | sandeep |
| 2 | Document2 | 4 | 2 | reya |
| 3 | Document3 | 5 | 3 | simi |
| 4 | Document4 | NULL | NULL | NULL |
| 5 | Document5 | NULL | NULL | NULL |
After applying the `WHERE` clause and selecting the listed attributes, the result will be:
| name | id |
|--------------|----|
| Document1 | 1 |
| Document2 | 3 |
b)内部 JOIN 条款
SELECT documents.name, downloads.id
FROM documents
LEFT OUTER JOIN downloads
ON documents.id = downloads.document_id
AND username = 'sandeep'
For above query the intermediate join table will look like this.
| id(from documents) | name | id (from downloads) | document_id | username |
|--------------------|--------------|---------------------|-------------|----------|
| 1 | Document1 | 1 | 1 | sandeep |
| 2 | Document2 | 3 | 2 | sandeep |
| 3 | Document3 | NULL | NULL | NULL |
| 4 | Document4 | NULL | NULL | NULL |
| 5 | Document5 | NULL | NULL | NULL |
Notice how the rows in `documents` that did not match both the conditions are populated with `NULL` values.
After Selecting the listed attributes, the result will be:
| name | id |
|------------|------|
| Document1 | 1 |
| Document2 | 3 |
| Document3 | NULL |
| Document4 | NULL |
| Document5 | NULL |
1
当涉及左连接时, where clause 与 on clause 之间有很大差异 .
这是一个例子:
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)
Query on "on clause" :
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)
Query on "where clause":
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)
14 回答
就优化器而言,无论是使用ON还是WHERE定义join子句,都不应该有所区别 .
但是,恕我直言,我认为在执行连接时使用ON子句要清楚得多 . 这样,您有一个特定的查询部分,它指示如何处理连接而不是与其余的WHERE子句混合 .
在SQL中,'WHERE'和'ON'子句是一种条件状态,但它们之间的主要区别在于,'Where'子句在Select / Update Statements中用于指定条件,而'ON'子句用于连接,在连接表之前验证或检查记录是否在目标和源表中匹配
For Example: - 'WHERE'
For Example: - 'ON'
*有两个表employee和employee_details,匹配列是employee_id . *
希望我已回答你的问题 . 回复澄清 .
通常,一旦两个表已经连接,就会在WHERE子句中处理过滤 . 虽然您可能希望在加入表之前过滤其中的一个或两个表,但这是可能的 . 即,where子句适用于整个结果集,而on子句仅适用于有问题的连接 .
为了获得更好的性能,表格应该有一个特殊的索引列用于JOINS .
因此,如果您所关注的列不是那些索引列之一,那么我怀疑最好将它保存在WHERE中 .
所以你使用索引列加入,然后在JOIN之后你在无索引列上运行条件 .
在内连接上,它们的意思相同 . 但是,根据是否将连接条件放在WHERE与ON子句中,您将在外连接中获得不同的结果 . 看看this related question和this answer(由我) .
我认为最常见的习惯是始终将连接条件放在ON子句中(除非它是外部连接,并且实际上确实需要在where子句中),因为它使任何读取查询的人都更清楚表连接的条件是什么,它还有助于防止WHERE子句长达数十行 .
它们不是同一件事 .
考虑这些查询:
和
第一个将返回订单及其行(如果有),订单号为
12345
. 第二个将返回所有订单,但只有订单12345
将有任何与之关联的行 .使用
INNER JOIN
时,子句实际上是等效的 . 然而,仅仅因为它们在功能上是相同的,因为它们产生相同的结果,并不意味着这两种子句具有相同的语义含义 .这是我的解决方案 .
您必须拥有
GROUP BY
才能使其正常工作 .希望这有帮助 .
在
INNER JOIN
上它们是可互换的,优化器会随意重新排列它们 .在
OUTER JOIN
上,它们不一定是可互换的,这取决于它们所依赖的连接的哪一侧 .我根据可读性将它们放在任何一个地方 .
对于内连接,
WHERE
和ON
可以互换使用 . 实际上,可以在相关子查询中使用ON
. 例如:这是(恕我直言)对人类完全混淆,很容易忘记将
table1
链接到任何东西(因为"driver"表不合法 .对于内连接无关紧要
外连接的事项
一个 .
WHERE
条款: After 加入 . 加入后,记录将被过滤 .湾
ON
clause - Before 加入 . 在加入之前将过滤记录(来自右表) . 这可能最终在结果中为null(因为OUTER join) .Example :请考虑以下表格:
a)内部
WHERE
条款:b)内部
JOIN
条款当涉及左连接时, where clause 与 on clause 之间有很大差异 .
这是一个例子:
fid是表t2的id .
Query on "on clause" :
Query on "where clause":
很明显,对于行t1.v ='K',第一个查询从t1返回记录,并从t2返回其依赖行(如果有的话) .
第二个查询从t1返回行,但仅对于t1.v ='K'将具有任何关联的行 .
我这样做的方式是:
始终将连接条件放在on子句中如果要进行内连接,那么不要在on子句中添加任何where条件,将它们放在where子句中
如果你正在进行左连接,将任何where条件添加到连接右侧的表的on子句中 . 这是必须的,因为添加引用连接右侧的where子句会将连接转换为内连接(下面描述了一个例外) .
例外情况是,当您查找不在特定表中的记录时,您将在右连接表中将引用添加到唯一标识符(不是永久为空),以这种方式将其添加到where子句“where t2 . idfield为null“ . 因此,您应该在连接右侧引用表的唯一时间是查找不在表中的那些记录 .
This article清楚地解释了差异 . 它还解释了"ON joined_condition vs WHERE joined_condition or joined_alias is null" .
WHERE子句过滤JOIN的左侧和右侧,而ON子句将始终仅过滤右侧 .
如果你总是想要获取左侧行而只是在某些条件匹配时才加入,那么你应该使用ON子句 .
如果要过滤连接双方的产品,则应使用WHERE子句 .
我认为这是连接序列效应 . 在左上角连接的情况下,SQL首先执行左连接,然后执行筛选 . 在downer的情况下,首先找到Orders.ID = 12345,然后再加入 .