SQL join: where clause vs. on clause

on_where_clause

Consider these queries:
 
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
 
The first will return an order and it's lines (if any) for order number 12345. 
The second will return all orders, but only order 12345 will have any lines associated with it.
运行效果如下：
test> select * from orders;
+----+------+
| id | foo  |
+----+------+
|  1 |    2 |
|  2 |    5 |
|  3 |    6 |
+----+------+
3 rows in set (0.03 sec)
 
test> select * from order_lines;
+----+----------+------+
| id | order_id | bar  |
+----+----------+------+
|  1 |        1 |    8 |
|  2 |        5 |   11 |
+----+----------+------+
2 rows in set (0.00 sec)
 
test> select * from orders left join order_lines on orders.id = order_lines.order_id and orders.id = 1;
+----+------+------+----------+------+
| id | foo  | id   | order_id | bar  |
+----+------+------+----------+------+
|  1 |    2 |    1 |        1 |    8 |
|  2 |    5 | NULL |     NULL | NULL |
|  3 |    6 | NULL |     NULL | NULL |
+----+------+------+----------+------+
3 rows in set (0.06 sec)
 
test> select * from orders left join order_lines on orders.id = order_lines.order_id where orders.id = 1;
+----+------+------+----------+------+
| id | foo  | id   | order_id | bar  |
+----+------+------+----------+------+
|  1 |    2 |    1 |        1 |    8 |
+----+------+------+----------+------+
1 row in set (0.01 sec)

描述：有些时候我们会把where条件写在on语句中，这和正常情况下放在where语句中还是有区别的，取决于join的类型： 
1. inner join：这两种写法是一样的 
2. outer join：这两种写法产生的结果完全不一样，具体看代码，取自stack overflow。 
If dealing with an OUTER JOIN (IE: LEFT, RIGHT), there is a huge difference between the two versions because the WHERE criteria is 
applied after the JOIN is made. If the criteria is specified in the ON clause, the criteria is applied before the JOIN is made which 
can made a considerable difference between the result sets.