在阅读它之后,这不是显式与隐式SQL连接的副本。 答案可能相关(甚至相同),但问题是不同的。
它们之间有什么不同?每一种都应该有什么不同?
如果我正确地理解了这个理论,那么查询优化器应该能够互换地使用这两种方法。
在阅读它之后,这不是显式与隐式SQL连接的副本。 答案可能相关(甚至相同),但问题是不同的。
它们之间有什么不同?每一种都应该有什么不同?
如果我正确地理解了这个理论,那么查询优化器应该能够互换地使用这两种方法。
当前回答
为了补充Joel Coehoorn的回答,我将添加一些特定于sqlite的优化信息(其他SQL风格的行为可能不同)。在原来的例子中,LEFT JOIN有不同的结果,这取决于你是否使用JOIN on…在哪里或加入…和。下面是一个稍微修改过的例子:
SELECT *
FROM Orders
LEFT JOIN OrderLines ON Orders.ID = OrderLines.OrderID
WHERE Orders.Username = OrderLines.Username
与
SELECT *
FROM Orders
LEFT JOIN OrderLines ON Orders.ID = OrderLines.OrderID
AND Orders.Username = OrderLines.Username
现在,最初的答案指出,如果使用普通的内部连接而不是左连接,两个查询的结果将是相同的,但执行计划将有所不同。我最近意识到,两者之间的语义差异是,前者强制查询优化器使用与ON子句关联的索引,而后者允许优化器选择ON子句中的任何索引……AND子句,这取决于它认为最有效的方式。
有时,优化器会猜错,您会想强制执行某个执行计划。在本例中,假设SQLite优化器错误地得出结论,认为执行此连接的最快方法是使用Orders上的索引。用户名,当您从经验测试中知道,对订单的索引。ID可以更快地传递查询。
在这种情况下,前者JOIN ON…WHERE语法实际上允许您强制对ID参数执行主连接操作,只有在主连接完成之后才对Username执行辅助过滤。相反,JOIN ON…AND语法允许优化器选择是否在Orders上使用索引。ID或订单。用户名,理论上有可能它会选择一个速度较慢的。
其他回答
a. WHERE子句:加入后,记录将被过滤。
b. 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'将有任何与它相关的行。
我的做法是:
Always put the join conditions in the ON clause if you are doing an INNER JOIN. So, do not add any WHERE conditions to the ON clause, put them in the WHERE clause. If you are doing a LEFT JOIN, add any WHERE conditions to the ON clause for the table in the right side of the join. This is a must, because adding a WHERE clause that references the right side of the join will convert the join to an INNER JOIN. The exception is when you are looking for the records that are not in a particular table. You would add the reference to a unique identifier (that is not ever NULL) in the RIGHT JOIN table to the WHERE clause this way: WHERE t2.idfield IS NULL. So, the only time you should reference a table on the right side of the join is to find those records which are not in the table.
在INNER join中,它们是可互换的,优化器会随意重新排列它们。
在OUTER连接上,它们不一定是可互换的,这取决于它们所依赖的连接的哪一侧。
我把它们放在任意一个位置,取决于可读性。
它们不是一回事。
考虑一下这些问题:
SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345
and
SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
AND Orders.ID = 12345
第一个将返回订单及其行(如果有的话),订单号为12345。 第二个将返回所有订单,但只有订单12345有与之关联的行。
使用INNER JOIN,这两个子句实际上是等效的。然而,仅仅因为它们在功能上相同,产生相同的结果,并不意味着这两种子句具有相同的语义。