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联接,结果将会更改.
看看下面的例子
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;
