SQL表联接中ON和WHERE子句之间的区别 [英] Difference between ON and WHERE clauses in SQL table joins

问题描述

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

这三个语句具有相同的结果.除了不能单独使用where而不使用on之外，在表连接中是否还存在完全使用where的特殊原因?

These three statements have the same result. Apart from the fact that where cannot be used exclusively, without using on, is there any particular reason to use where at all in table joins?

推荐答案

主要区别是使用不同的联接时.

The main difference is when you are using different joins.

通常，如果使用内部联接，您应该会看到相同的结果，但是一旦开始使用LEFT联接，结果将发生变化.

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.

看看下面的例子

并查看以下文章(非常解释)

编辑@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,
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;

这篇关于SQL表联接中ON和WHERE子句之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！