oracle SQL 表连接中 ON 和 WHERE 子句的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13132447/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Difference between ON and WHERE clauses in SQL table joins
提问by Jay
select e.last_name, e.hire_date
from employees e join employees m
on (m.last_name = 'Davies')
and (e.hire_date > m.hire_date);
select e.last_name, e.hire_date
from employees e join employees m
on (m.last_name = 'Davies')
where (e.hire_date > m.hire_date);
select e.last_name, e.hire_date
from employees e join employees m
on (e.hire_date > m.hire_date)
where (m.last_name = 'Davies');
These three statements have the same result. Apart from the fact that wherecannot be used exclusively, without using on, is there any particular reason to use whereat all in table joins?
这三个语句具有相同的结果。除了where不能独占使用，不使用on，还有什么特别的理由where在表连接中使用吗？
回答by Adriaan Stander
The main difference is when you are using different joins.
主要区别在于您何时使用不同的联接。
Typically you should see the same result if you were to use inner joins, but once you start using LEFT joins the results will change.
通常，如果要使用内部联接，您应该看到相同的结果，但是一旦开始使用 LEFT 联接，结果就会改变。
Have a look at the following example
看看下面的例子
SQL Fiddle DEMO
SQL小提琴演示
And have a look at the following article (very explanatory)
EDIT for @ShannonSeverance
编辑@ShannonSeverance
Schema and Test data
架构和测试数据
CREATE TABLE Table1 (
ID INT,
Val VARCHAR(20)
);
INSERT INTO Table1 VALUES (1,'a');
INSERT INTO Table1 VALUES (2,'a');
CREATE TABLE Table2 (
ID INT,
Val VARCHAR(20)
);
INSERT INTO Table2 VALUES (1,'a');
and Tests
和测试
SELECT t1.ID,
t1.Val,
t2.ID ID2,
t2.Val Val2
FROM Table1 t1 INNER JOIN
Table2 t2 ON t1.ID = t2.ID AND t1.Val = t2.Val;
SELECT t1.ID,
t1.Val,
t2.ID ID2,
t2.Val Val2
FROM Table1 t1,Table2 t2
WHERE t1.ID = t2.ID
AND t1.Val = t2.Val;
SELECT t1.ID,
t1.Val,
t2.ID ID2,
t2.Val Val2
FROM Table1 t1 LEFT JOIN
Table2 t2 ON t1.ID = t2.ID AND t1.Val = t2.Val;
SELECT t1.ID,
t1.Val,
t2.ID ID2,
t2.Val Val2
FROM Table1 t1 LEFT JOIN
Table2 t2 ON t1.ID = t2.ID
WHERE t1.Val = t2.Val;
回答by Matt Ball
whereis a filter which is applied after rows are selected using the join. It is not always the case that a join ... oncondition is sematically equivalent to a wherecondition. Therefore, yes, there is a particular reason to use a wherein table joins: when it does the right thing.
where是在使用连接选择行后应用的过滤器。join ... on条件在语义上并不总是等同于where条件的情况。因此，是的，使用where表内连接有一个特殊的原因：当它做正确的事情时。
...and by contrast, the
ONcondition executes as the join is being made.ONconditions for joins earlier in multi-table joins can cut off millions of unnecessary joins so are generally preferred if semantically correct
– Bohemian
...相比之下，
ON条件在进行连接时执行。ON多表连接中较早的连接条件可以切断数百万个不必要的连接，因此如果语义正确，通常是首选的
– Bohemian
回答by metamorph
Using onusually used for querying more than one table. When making that query, tables must have relationship each other, in general the same value in a specific fields.
usingon通常用于查询多个表。在进行该查询时，表之间必须具有关系，通常在特定字段中具有相同的值。
onwill connect that same value, for example:
on将连接相同的值，例如：
**table1**:
id_name id_position name
1 1 john
2 2 doe
3 2 tom
4 3 hawkins
**table2**
id_position position
1 system analyst
2 programmer
SELECT t1.id_name, t1.name, t2.position
FROM table1 t1 LEFT JOIN table2 t2
ON t1.id_position = t2.id_position
-- RESULT:
id_name name position
1 john system analyst
2 doe programmer
3 tom programmer
4 hawkins NULL -- NO MATCH IN table 2
as we can see onwill connect table1 and table2 that have same value id_position, so it is a little different from what you have been written above.
正如我们所看到的，on将连接具有相同值的 table1 和 table2 id_position，因此它与您上面写的有点不同。
While wherecan be used in every query and not depends how many tables in that query. In general whereis used for conditional thing that we want.
虽然where可以在每个查询中使用，而不取决于该查询中有多少表。一般where用于我们想要的有条件的东西。
回答by 151291
The ONclause defines the relationship between the tables.
该ON子句定义了表之间的关系。
ONClause supports all join types.
ON子句支持所有连接类型。
The WHEREclause describes which rows you are interested in.
该WHERE子句描述了您对哪些行感兴趣。
WHEREClause only supports for Inner join not for Outer joins likeLEFT JOINandRIGHT JOIN.
WHEREClause 只支持 Inner join 不支持 Outer joins likeLEFT JOINandRIGHT JOIN。
回答by Lugaid
The difference lies in when the engine performs the filtering. A "where" represents a filter on the computed product of both tables. The "on" keyword specifies how a join is performed. They are not semantically equivalent even if sometimes they both produce the same outcome.
不同之处在于引擎执行过滤的时间。“where”表示对两个表的计算乘积的过滤器。“on”关键字指定如何执行连接。即使有时它们都产生相同的结果，它们在语义上并不等效。
Cheers
干杯
