The difference between these two is what happens when a row
of A has no matches in B.
select * from A left join B on A.Time = B.Time
select * from A left join B where A.Time = B.Time
In the first one the condition is carried out during the join
so if a row of A has no matches in B then the B part of the result
is filled out with NULL values.
In the second one A is joined with B and then the "where" is applied
afterwards so if there is no match to a row of A then that row of
A will not appear at all in the result.
Here is an example in R, Note that there was no match to the
third row of A in B so in the first case that row of A appears in the
result with
NULLs in the B column positions. In the second case the third row of A
is dropped from the result.
> library(sqldf)
>
> # BOD comes with R
> A <- BOD[1:3, ]
> B <- BOD[1:2, ]
> A
Time demand
1 1 8.3
2 2 10.3
3 3 19.0
> B
Time demand
1 1 8.3
2 2 10.3
>
> sqldf("select * from A left join B on A.Time = B.Time")
Time demand Time..3 demand..4
1 1 8.3 1 8.3
2 2 10.3 2 10.3
3 3 19.0 NA NA
> sqldf("select * from A left join B where A.Time = B.Time")
Time demand Time..3 demand..4
1 1 8.3 1 8.3
2 2 10.3 2 10.3
On Sun, Oct 27, 2019 at 6:09 PM Benjamin Asher <benashe...@gmail.com> wrote:
>
> Hi there! My colleagues and I are trying to understand the role of ON
> constraints vs WHERE clauses in JOINs. It seems both of the following work,
> but I’m not really sure why:
>
> Query A:
>
> SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=‘constant’;
>
>
> Query B:
>
> SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.x WHERE tab1.x=‘constant’;
>
> Is there a difference between the two (function and performance)? Is there an
> advantage to putting WHERE-type filtering in the ON constraint vs leaving it
> in the WHERE clause for LEFT JOINs?
>
> Thanks!
>
> Ben
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
