On Sunday, 27 October, 2019 16:09, Benjamin Asher <[email protected]> wrote:
> Is there an advantage to putting WHERE-type filtering in the
> ON constraint vs leaving it in the WHERE clause for LEFT JOINs?
The ON clause is merely syntactic sugar for a parenthesized AND clause appended
to the parenthesized WHERE clause EXCEPT in the case of an OUTER JOIN.
That is when you emit:
SELECT <columns>
FROM <table1>
JOIN <table2>
ON <conditions1>
WHERE <whereconditions>
This is really nothing more than:
SELECT <columns>
FROM <table1>, <table2>
WHERE (<conditions1>)
AND (<wherecondition>)
This applies no matter how many <tablen> there are, nor how may ON
<conditionsn> there are. The ON clause does not even have to mention tables
that have already appeared so long as they eventually appear so the WHERE
clause can be processed. JOIN or INNER JOIN is merely an alternate spelling of
a comma, and ON merely puts its argument conditional expression in parenthesis
and tacks it onto the end of the WHERE conditions with an AND (the where
condition clause itself being parenthesized).
In the case of an OUTER JOIN the ON condition specifies the selection
constraint for descent into the immediately preceding table (that is, the ON
clause binds to the table that is the target of the join). If the selection
criteria in that descent condition cannot be met, that table tuple is replaced
with a tuple of all NULL.
Once upon a time there was no ON clause, and one would specify outer
constraints with a *. dibble *= dabble means that you want all the values of
dibble even if there is no matching dabble, and the dabble row is therefore
comprised of all nulls (this is a left outer join). Similarly dibble =* dabble
meant that one wants all the dabble even if there is no matching dibble, in
which case the tuple from which the dibble came will be all nulls (right outer
join). There was also a dibble *=* dabble which meant that you wanted all the
results where dibble matched dabble, plus the ones where dibble had no match
and the tuple from when dabble came was therefore all nulls, and the ones where
dabble had no match in which case the tuple from which dibble came was all
nulls (full outer join).
The JOIN .. ON semantics were invented because some people had difficulty
composing appropriate WHERE clauses without it.
So for OUTER JOINS (of which a LEFT JOIN is but an instance of the entire
class) whether a condition appears in the ON clause or the WHERE clause is
material to how the query is performed and the results obtained. For an INNER
JOIN, it does not matter whether the condition is specified in an ON clause or
in the WHERE clause.
In fact, in the case of INNER JOIN you do not need either keyword at all:
SELECT a, b
FROM x, y
ON x.a == y.c
WHERE y.q == 5
is the same as:
SELECT a, b
FROM x, y
ON y.q == 5
WHERE x.a == y.c
is the same as
SELECT a, b
FROM X JOIN Y
WHERE x.a == y.c
AND y.q == 5
which is really just:
SELECT a, b
FROM x, y
WHERE x.a == y.c
AND y.q == 5
however, for outer joins:
SELECT a, b
FROM x LEFT JOIN y
ON x.a == y.c
WHERE y.q == 5
is the same as:
SELECT a, b
FROM x, y
WHERE x.a == y.c
AND y.q == 5
(that is, because the WHERE clause requires that y.q not be NULL, the OUTER
JOIN is meaningless and merely results is more processing CPU and memory usage
than is necessary since the OUTER JOIN is really just an inner join).
SELECT a, b
FROM x LEFT JOIN y
ON x.a == x.c and y.q == 5
gives a completely different set of results.
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
