SQL在...上的左联接和在.上的左联接之间有区别 [英] SQL Different between Left join on... and Left Join on..where

问题描述

我有两个SQL将两个表连接在一起:

I have two sql to join two table together:

select top 100 a.XXX
              ,a.YYY
              ,a.ZZZ
              ,b.GGG
              ,b.JJJ
from table_01 a 
    left join table_02 b
        on a.XXX = b.GGG
            and b.JJJ = "abc"
            and a.YYY between '01/08/2009 13:18:00' and '12/08/2009 13:18:00'

---

select top 100 a.XXX
              ,a.YYY
              ,a.ZZZ
              ,b.GGG
              ,b.JJJ 
from table_01 a 
    left join table_02 b
        on a.XXX = b.GGG 
where b.JJJ = "abc"
    and a.YYY between '01/08/2009 13:18:00' and '12/08/2009 13:18:00'

它们的结果不同，但是我不明白原因. 如果能在这里得到一些帮助，我将不胜感激.

The outcome of them is different but I don't understand the reason why.
I would be grateful if I can get some help here.

推荐答案

无论何时使用LEFT JOIN，有关右表内容的所有条件都应在ON子句中，否则您将有效地转换您的LEFT JOIN到INNER JOIN.

Whenever you are using LEFT JOIN, all the conditions about the content of the right table should be in the ON clause, otherwise you are effectively converting your LEFT JOIN to an INNER JOIN.

这样做的原因是，当使用LEFT JOIN时，将返回左表中的所有行.如果它们与右边的表匹配，则匹配的行的值也将被返回，但是如果它们与右边的表的任何行都不匹配，则右边的表将返回null的行值.
由于您无法将任何内容与NULL(甚至没有另一个NULL)进行比较(

The reason for that is that when a LEFT JOIN is used, all the rows from the left table will be returned. If they are matched by the right table, the values of the matching row(s) will be returned as well, but if they are not matched with any row on the right table, then the right table will return a row of null values.
Since you can't compare anything to NULL (not even another NULL) (Read this answer to find out why), you are basically telling your database to return all rows that are matched in both tables.
However, when the condition is in the ON clause, Your database knows to treat it as a part of the join condition.

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