Sorry for top posting.. I'm way too lazy to figure out how to make Outlook play
nicely.
You say:
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.
However, this isn't true. While it doesn't constrain R, it does constrain the
join. This will still return all rows of R, but all S columns will be NULL for
any row where R.col is not 'foo'.
This is actually useful, especially for efficiency. This allows the database
to not even bother doing the lookup on S if R.col != 'foo' and still return
that R record.
Marc
Marc L. Allen | Software Engineer IV, PDI Marketing Cloud Solutions
2551 Eltham Ave. Suite N, Norfolk, VA 23513
Office 757.853.3000 x6015
Direct 757.605.6015
[email protected]
www.pdisoftware.com <http://www.pdisoftware.com>
On 10/28/19, 1:20 PM, "sqlite-users on behalf of James K. Lowden"
<[email protected] on behalf of
[email protected]> wrote:
On Sun, 27 Oct 2019 15:09:03 -0700
Benjamin Asher <[email protected]> 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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
