On Sun, 27 Oct 2019 15:09:03 -0700
Benjamin Asher <benashe...@gmail.com> wrote:
> My colleagues and I are trying to understand the role of ON
> constraints vs WHERE clauses in JOINs.
ON applies before JOIN. WHERE applies after.
That's a loose interpretation, but IMO it's nevertheless a useful way
to think about it.
It *mostly* doesn't matter. It was invented for outer joins.
In SQL-89, there was only the inner join. There was no JOIN keyword.
You listed all the tables in FROM:
FROM R, S
WHERE R.key = S.key
and all was good with the world.
To support outer joins, vendors invented various syntaxes. To unifiy
them, SQL-92 defined JOIN and ON:
FROM R JOIN S
ON R.key = S.key
with that syntax, JOIN could be modifed to be LEFT or RIGHT outer, and
the inner table could be further restricted:
FROM R LEFT OUTER JOIN S
ON R.key = S.key AND S.col = 'foo'
That means: all rows from R joined to rows in S for which col = 'foo'.
If no such S rows exist, every result row will be NULL for the S
columns. Compare with
FROM R LEFT OUTER JOIN S
ON R.key = S.key
WHERE S.col = 'foo'
Here, R and S are joined, and the WHERE test is applied to the joined
result. If no such S rows exist, there will be no result rows at all.
When wrestling with this topic, it's useful to remember that ON doesn't
constrain the outer table:
FROM R LEFT OUTER JOIN S
ON R.key = S.key AND R.col = 'foo'
Here,
AND R.col = 'foo'
while valid syntax (unfortunately), has no effect. R is the outer
table, and all rows of the outer table are always produced, no matter
what ON says about it. Perhaps the above should mean:
FROM (select * from R WHERE R.col = 'foo') as R
LEFT OUTER JOIN S
ON R.key = S.key
but it does not. Perfection remains, as ever, elusive.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
