I don’t think I did say that Marc. All I was saying was the ON didn’t have to
refer to the closest join.
select * from
tblA inner join tblB ON tblA.key = tblC key – no mention of tblB in ON
inner join tblC ON tblA.key = tblB.key; – no mention of tblC in ON
Normally that would be written as
select * from
tblA inner join tblB ON tblA.key = tblB key
inner join tblC ON tblA.key = tblC.key;
I was just pointing out that they’re the same thing as the ONs are moved to the
where
select * from tblA inner join tblB inner join tblC
where tblA.key = tblB.key AND tblA.key=tblC.key
which I thought illustrated Keith’s point.
________________________________
From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of
Allen, Marc <mlal...@pdisoftware.com>
Sent: Monday, October 28, 2019 5:27:57 PM
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] LEFT JOIN: ON vs. WHERE
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
mlal...@pdisoftware.com
www.pdisoftware.com<http://www.pdisoftware.com> <http://www.pdisoftware.com>
On 10/28/19, 1:20 PM, "sqlite-users on behalf of James K. Lowden"
<sqlite-users-boun...@mailinglists.sqlite.org on behalf of
jklow...@schemamania.org> wrote:
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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
