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 figur
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,
.
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, b);
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 t1
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 (u
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 i
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 part
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 clau
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’;
>>
>> Que
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 on
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 your
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
usef
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 ta
Hi there! My colleagues and I are trying to understand the role of ON
constraints vs WHERE clauses in JOINs. It seems both of the following work, but
I’m not really sure why:
Query A:
SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=‘constant’;
Query B:
SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x
16 matches
Mail list logo