which I thought illustrated Keith’s point.
From: sqlite-users on behalf of
Allen, Marc
Sent: Monday, October 28, 2019 5:27:57 PM
To: SQLite mailing list
Subject: Re: [sqlite] LEFT JOIN: ON vs. WHERE
Sorry for top posting.. I'm way too lazy to figure out
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,
that.
From: sqlite-users on behalf of
James K. Lowden
Sent: Monday, October 28, 2019 10:32:21 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] LEFT JOIN: ON vs. WHERE
On Mon, 28 Oct 2019 12:10:38 -0600
"Keith Medcalf" wrote:
> CREATE TABLE t1 (a,
On Mon, 28 Oct 2019 12:10:38 -0600
"Keith Medcalf" wrote:
> CREATE TABLE t1 (a, b);
> INSERT INTO t1 VALUES(1,2);
> INSERT INTO t1 VALUES(2,3);
> INSERT INTO t1 VALUES(1,3);
> CREATE TABLE t2 (b, c);
> INSERT INTO t2 VALUES(2,3);
> INSERT INTO t2 VALUES(3,4);
> select * from t1 left join t2 on
On Monday, 28 October, 2019 11:19, James K. Lowden
wrote:
>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
On Sun, 27 Oct 2019 15:09:03 -0700
Benjamin Asher 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
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
Ah! I see. Thanks for that walk through of OUTER JOIN.
Ben
> On Oct 27, 2019, at 4:18 PM, Keith Medcalf wrote:
>
>
> On Sunday, 27 October, 2019 16:09, Benjamin Asher
> wrote:
>
>> Is there an advantage to putting WHERE-type filtering in the
>> ON constraint vs leaving it in the WHERE
On Sunday, 27 October, 2019 16:09, Benjamin Asher 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
This is exactly the kind of advice I was looking for.
Thanks again!
Ben
> On Oct 27, 2019, at 4:04 PM, Simon Slavin wrote:
>
> On 27 Oct 2019, at 10:45pm, Benjamin Asher wrote:
>
>> Query A
>>
>> SELECT * FROM tab1 LEFT JOIN tab2 ON tab2.x='constant' WHERE
>> tab1.x='constant’;
>>
>>
On 27 Oct 2019, at 10:45pm, Benjamin Asher wrote:
> Query A
>
> SELECT * FROM tab1 LEFT JOIN tab2 ON tab2.x='constant' WHERE
> tab1.x='constant’;
>
> Query B
>
> SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.x WHERE tab1.x=‘constant'
Your use of JOIN here is extremely usual. Depending
Okay playing with this some more: it seems like since everything is equal to
the same constant, it doesn’t really matter? That said, it feels like poor form
/ not good practice (join can become not useful if you don’t have a constraint
joining particular columns). If I want to make it easier on
Oh you’re right. I realize now I messed up the example. Here are the updated
queries:
Query A
SELECT * FROM tab1 LEFT JOIN tab2 ON tab2.x='constant' WHERE tab1.x='constant’;
Query B
SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.x WHERE tab1.x=‘constant
These return the same results in
On 27 Oct 2019, at 10:09pm, Benjamin Asher wrote:
> SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=‘constant’;
You're doing a JOIN here. How should the engine know which row of tab2
corresponds to which row of tab1 ?
Your query is syntactically correct, but it doesn't appear to do anything
On 10/27/19, Benjamin Asher wrote:
> It seems both of the following work,
> but I’m not really sure why:
I get different answers for the two queries when I try them:
CREATE TABLE tab1(x,y);
CREATE TABLE tab2(x,y);
INSERT INTO tab1 VALUES('constant','string'),('abc','def'),(97,15);
INSERT INTO
15 matches
Mail list logo
