SQL表毗连中ON和WHERE子句之间的区别

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');

这三个告诉具有沟通的功效.除了不能单独行使,不行使on的究竟,是否有任何非凡的来由在表毗连中行使那边？

办理要领

凡是,假如要行使内部联接,则应该看到沟通的功效,可是一旦开始行使LEFT联接,功效将会变动.

看看下面的例子

SQL Fiddle DEMO

And have a look at the following article (very explanatory)

编辑@ShannonSeverance

架构和测试数据

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');

和测试

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,t2.Val Val2
FROM Table1 t1,Table2 t2 
WHERE t1.ID = t2.ID
 AND t1.Val = t2.Val;

SELECT  t1.ID,t2.Val Val2
FROM Table1 t1 LEFT JOIN
Table2 t2 ON t1.ID = t2.ID  AND t1.Val = t2.Val;

SELECT  t1.ID,t2.Val Val2
FROM Table1 t1 LEFT JOIN
Table2 t2 ON t1.ID = t2.ID  
WHERE t1.Val = t2.Val;

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!