Re: [sqlite] LEFT JOIN: ON vs. WHERE

[x]

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  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 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" 
 wrote:

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 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

Re: [sqlite] LEFT JOIN: ON vs. WHERE

[Allen, Marc]

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 
 

On 10/28/19, 1:20 PM, "sqlite-users on behalf of James K. Lowden" 
 wrote:

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 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

Re: [sqlite] LEFT JOIN: ON vs. WHERE

[x]

If I remember correctly I found out by mistake that the ON doesn’t even have to 
relate to the JOIN it follows as long as it fits in with the WHERE. E.g.



SELECT * FROM Tbl1 INNER JOIN Tbl2 ON Tbl3.Col=Tbl1.Col INNER JOIN Tbl3 ON 
Tbl2.Col=Tbl1.Col;



Something like 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, 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.b == t2.b and t1.a == 1;
> 1|2|2|3
> 2|3||
> 1|3|3|4

Thank you, Keith.  What you're saying is that when ON applies to the
outer table, it still constrains the JOIN.  The tuple

2 3 3 4

would have been produced, but for the ON restriction

t.a = 1

> This sort of thing is useful

I don't doubt it.  I can't remember ever having written a query like
that (not knowing that's what it would do).  I would probably have
expressed the giraffe-neck problem as

select * from t1
left join ( select * from t2 where b <> 1 ) as t2
on t1.b = t2.b

because usually, in domains I worked with, the constraint on the inner
table wasn't a function of the join criteria, but of some type or group
or catagory column, or date.

--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

Re: [sqlite] LEFT JOIN: ON vs. WHERE

[James K. Lowden]

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.b == t2.b and t1.a == 1;
> 1|2|2|3
> 2|3||
> 1|3|3|4

Thank you, Keith.  What you're saying is that when ON applies to the
outer table, it still constrains the JOIN.  The tuple

2 3 3 4

would have been produced, but for the ON restriction 

t.a = 1

> This sort of thing is useful

I don't doubt it.  I can't remember ever having written a query like
that (not knowing that's what it would do).  I would probably have
expressed the giraffe-neck problem as 

select * from t1 
left join ( select * from t2 where b <> 1 ) as t2
on t1.b = t2.b

because usually, in domains I worked with, the constraint on the inner
table wasn't a function of the join criteria, but of some type or group
or catagory column, or date.  

--jkl

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN: ON vs. WHERE

[Keith Medcalf]

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 (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.

Saying that it has no effect is incorrect.  It does constrain the inner loop 
candidates.  It has no effect on the outer loop candidate selection because we 
are already inside the inner loop before that condition is evaluated.  In other 
words, there can be no matching t2 tuple if the t1 tuple does not have t1.a == 
1.

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.b == t2.b and t1.a == 1;
1|2|2|3
2|3||
1|3|3|4

This sort of thing is useful, for example, if t2 represents data about the neck 
length of giraffes, and t1.a tells the animal type (1 == giraffe).  Clearly 
things which are not giraffe's do not have a giraffe neck length so this 
constraint is properly part of the inner candidate selection.

-- 
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN: ON vs. WHERE

[James K. Lowden]

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 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

Re: [sqlite] LEFT JOIN: ON vs. WHERE

[Gabor Grothendieck]

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 of the result
is filled out with NULL values.

In the second one A is joined with B and then the "where" is applied
afterwards so if there is no match to a row of A then that row of
A  will not appear at all in the result.

Here is an example in R,  Note that there was no match to the
third row of A in B so in the first case that row of A appears in the
result with
NULLs in the B column positions.  In the second case the third row of A
is dropped from the result.

> library(sqldf)
>
> # BOD comes with R
> A <- BOD[1:3, ]
> B <- BOD[1:2, ]
> A
  Time demand
118.3
22   10.3
33   19.0
> B
  Time demand
118.3
22   10.3
>
> sqldf("select * from A left join B on A.Time = B.Time")
  Time demand Time..3 demand..4
118.3   1   8.3
22   10.3   2  10.3
33   19.0  NANA
> sqldf("select * from A left join B where A.Time = B.Time")
  Time demand Time..3 demand..4
118.3   1   8.3
22   10.3   2  10.3


On Sun, Oct 27, 2019 at 6:09 PM Benjamin Asher  wrote:
>
> 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=tab2.x WHERE tab1.x=‘constant’;
>
> Is there a difference between the two (function and performance)? Is there an 
> advantage to putting WHERE-type filtering in the ON constraint vs leaving it 
> in the WHERE clause for LEFT JOINs?
>
> Thanks!
>
> Ben
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



-- 
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN: ON vs. WHERE

[Benjamin Asher]

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 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 
>  FROM 
>  JOIN 
>ON 
> WHERE 
> 
> This is really nothing more than:
> 
> SELECT 
>  FROM , 
> WHERE ()
>   AND ()
> 
> This applies no matter how many  there are, nor how may ON 
>  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
> 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

Re: [sqlite] LEFT JOIN: ON vs. WHERE

[Keith Medcalf]

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 clause EXCEPT in the case of an OUTER JOIN.

That is when you emit:

SELECT 
  FROM 
  JOIN 
ON 
 WHERE 

This is really nothing more than:

SELECT 
  FROM , 
 WHERE ()
   AND ()

This applies no matter how many  there are, nor how may ON 
 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN: ON vs. WHERE

[Ben Asher]

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’;
>> 
>> 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 your data, you either 
> don't need it, or it does nothing.
> 
> Suppose you have many rows in tab1 where x='constant' and also many rows in 
> tab2 where x='constant'.  What are you expecting the engine to do with them ? 
>  Should it match one tab1 row with one tab2 row ?  Or should it generate a 
> set of every possible combination ?  This tells you what your ON clause 
> should be.
> 
> Generally, a constant belongs in a WHERE clause.  To answer your original 
> question
> 
>> Is there an advantage to putting WHERE-type filtering in the ON constraint 
>> vs leaving it in the WHERE clause for LEFT JOINs?
> 
> SQLite reads the entire statement and does things to figure out the best way 
> to execute it given the indexes available.  The kind of deconstruction you 
> did to the query in your head, happens inside the SQL engine.  So we cannot 
> give you an answer "always put it in ON" or "always put it in WHERE".  
> Instead we say "Don't make your query look weird just to save a millisecond.  
> Have your query make sense, so someone who reads it understands what you are 
> doing.".
> 
> If you want to worry about speed or efficiency, worry about making useful 
> indexes for your tables instead of exactly how to phrase your query.
> ___
> 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

Re: [sqlite] LEFT JOIN: ON vs. WHERE

[Simon Slavin]

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 your data, you either 
don't need it, or it does nothing.

Suppose you have many rows in tab1 where x='constant' and also many rows in 
tab2 where x='constant'.  What are you expecting the engine to do with them ?  
Should it match one tab1 row with one tab2 row ?  Or should it generate a set 
of every possible combination ?  This tells you what your ON clause should be.

Generally, a constant belongs in a WHERE clause.  To answer your original 
question

> Is there an advantage to putting WHERE-type filtering in the ON constraint vs 
> leaving it in the WHERE clause for LEFT JOINs?

SQLite reads the entire statement and does things to figure out the best way to 
execute it given the indexes available.  The kind of deconstruction you did to 
the query in your head, happens inside the SQL engine.  So we cannot give you 
an answer "always put it in ON" or "always put it in WHERE".  Instead we say 
"Don't make your query look weird just to save a millisecond.  Have your query 
make sense, so someone who reads it understands what you are doing.".

If you want to worry about speed or efficiency, worry about making useful 
indexes for your tables instead of exactly how to phrase your query.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN: ON vs. WHERE

[Benjamin Asher]

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 myself to adjust 
this query in the future, it’s better practice to move constants to the WHERE 
clause and ensure there are column-based join constraints.

Does that seem right? My colleague presented me with a related query to debug, 
and now I’m questioning everything.

Ben

> On Oct 27, 2019, at 3:45 PM, Benjamin Asher  wrote:
> 
> 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 sample setup. I think Simon pointed out 
> what I’m also thinking is the problem: could Query A return rows that aren’t 
> matched up properly because of a lack of a constraint joining the 2 tables?
> 
> Thanks for putting together that more complete example :).
> 
> Ben
> 
>> On Oct 27, 2019, at 3:35 PM, Richard Hipp  wrote:
>> 
>> 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 tab2 SELECT * FROM tab1;
>> 
>> SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x='constant';
>> 
>> SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.x WHERE tab1.x='constant';
>> 
>> 
>> -- 
>> D. Richard Hipp
>> d...@sqlite.org
>> ___
>> 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

Re: [sqlite] LEFT JOIN: ON vs. WHERE

[Benjamin Asher]

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 sample setup. I think Simon pointed out 
what I’m also thinking is the problem: could Query A return rows that aren’t 
matched up properly because of a lack of a constraint joining the 2 tables?

Thanks for putting together that more complete example :).

Ben

> On Oct 27, 2019, at 3:35 PM, Richard Hipp  wrote:
> 
> 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 tab2 SELECT * FROM tab1;
> 
> SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x='constant';
> 
> SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.x WHERE tab1.x='constant';
> 
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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

Re: [sqlite] LEFT JOIN: ON vs. WHERE

[Simon Slavin]

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 
useful.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN: ON vs. WHERE

[Richard Hipp]

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 tab2 SELECT * FROM tab1;

SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x='constant';

SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.x WHERE tab1.x='constant';


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] LEFT JOIN: ON vs. WHERE

[Benjamin Asher]

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=tab2.x WHERE tab1.x=‘constant’;

Is there a difference between the two (function and performance)? Is there an 
advantage to putting WHERE-type filtering in the ON constraint vs leaving it in 
the WHERE clause for LEFT JOINs?

Thanks!

Ben
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Dinu]

Another reason to do at least the LEFT JOIN + WHERE -> INNER JOIN
optimisation:
If a view is based on a LEFT JOIN, running a WHERE query on it will exhibit
the same poor behavior and here there will be no way to rewrite the query.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Dinu]

Keith Medcalf wrote
> The primary difference is likely the test on b.c.  

/There was some text here that seems to have been lost and my browser opened
a "super resume templates" page... maybe I've been already spectre-hacked?
Anyway, I repost:/

The primary difference from what I see is that it changes the query plan to
start off with an index-based search on b, not a search (scan) on a. To me,
this can be due to the supplimentary condition in the ON clause making this
plan more appealing to the optimizer. So with this in mind, an easy
optimisation would be to just "permeate" any imperative non-null condition
in the where clause to the on clause. I don't know if the execution plan is
identical to that of an inner join (I never tried to actually convert it to
an inner join and I'm working on sth else atm), but it's satisfactory and it
starts off the right way, with a search on b, with an execution time well in
the expected margins.


Keith Medcalf wrote
> I think the query is ill-conceived when written as an outer join.

If anything, to reflect intentionality the query should have been written as
such:

SELECT FROM (
  SELECT FROM a LEFT JOIN b
) WHERE b.c=5




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Dinu]

Keith Medcalf wrote
> The primary difference is likely the test on b.c.  


Keith Medcalf wrote
> I think the query is ill-conceived when written as an outer join.

If anything, to reflect intentionality the query should have been written as
such:

SELECT FROM (
  SELECT FROM a LEFT JOIN b
) WHERE b.c=5




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Keith Medcalf]

>To reiterate, Keith: to get the query to execute properly, I didn't
>change
>the LEFT JOIN to an INNER JOIN!
>Nope,
>I rewrote
>
>SELECT
>FROM
>  a
>  LEFT JOIN b ON 
>WHERE b.c=5

This means:

Take all rows of table a
match with rows of table b on the key and where there is no match "fill" 
columns that would have come from b with a NULL
AND THEN AFTER THAT
return all rows where b.c = 5


>to

>SELECT
>FROM
>  a
>  LEFT JOIN b ON  AND b.c=5
>WHERE b.c IS NOT NULL

This translates to:
Take all rows of table a
match with rows of table b on the key AND where b.c = 5 and where there is no 
match "fill" columns that would have come from b with a NULL
AND THEN AFTER THAT
return only rows where b.c is not NULL

The primary difference is likely the test on b.c.  You see, to check if b.c is 
a particular value requires unpacking the row and retrieving the value.  
Checking whether b.c IS NULL does not require unpacking the data but merely 
reading the 'datatype', which should be a much faster operation.

If you wrote the query as an inner join it would be much faster since it would 
not be possible after projection for b.c to be anything other than not null.

>So I just added a redundant predicate and it runs perfectly, on
>SQLite!

>That's why I said this simple improvement can surely be taken care of
>on the
>optimizer, while the larger discussion of actually changing the outer
>join
>to an inner join or even tackling the -OR- case is for sure something
>nice
>to think of, but increasingly more complicated.

I think the query is ill-conceived when written as an outer join.  You are 
asking the query to "project" a result containing a crap-load of rows which you 
will subsequently remove from the result set.  For bitty-bases (a couple of 
hundred rows) this is irrelevant.  However, for tables containing millions of 
rows, creating a googillion of rows then culling out all of them except 4 is a 
very bad use of computer resources.  Why generate them in the first place if 
you are just going to discard them?

I have seen many a mis-designed program which works blazingly quickly on a 
"test" database that contains a dozen customers, half-a-dozen products, with 
two or three components each.  But when populated with the production data of 
1,000,000 customers, 500,000 products, and between 100 and 10,000 components 
per product frankly take "years" to run any step and are completely unuseable.

One should always design as if each table contained a googillion rows.  If it 
is fast enough then, it will certainly be efficient enough to handle your ten 
row per table database.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Dinu]

To reiterate, Keith: to get the query to execute properly, I didn't change
the LEFT JOIN to an INNER JOIN!
Nope,
I rewrote

SELECT
FROM
  a
  LEFT JOIN b ON 
WHERE b.c=5

to

SELECT
FROM
  a
  LEFT JOIN b ON  AND b.c=5
WHERE b.c IS NOT NULL

So I just added a redundant predicate and it runs perfectly, on SQLite!
That's why I said this simple improvement can surely be taken care of on the
optimizer, while the larger discussion of actually changing the outer join
to an inner join or even tackling the -OR- case is for sure something nice
to think of, but increasingly more complicated.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Dinu]

Keith Medcalf wrote
> but cannot be re-ordered for OUTER JOINS.

Actually, I think order requirements (or rather, row grouping requirements,
as far as I can paint it the requirement is just that all outer joined rows
come in a bunch for each main join tuple) would not be violated if the join
is made on an unique key left-side and an index is used right-side :) or
something similar. I don't know, extensive algebra must be involved :) Even
without index order inference, the main trunk keys can be sorted in a temp
structure to preserve the condition, like in a GROUP BY query. However, I do
see that SQLite seems to actually do it (scan b before a I mean) if I
reqrite the query as I showed.

select *
  from a, b
 where a.a *= b.a

was replaced by

select *
  from a LEFT JOIN b ON a.a = b.a

Right, right, and with this in mind you can see my problem with the query is
so easy to understand:

My query, on the old format, is:

select *
from a, b
where 
  a.a *= b.a AND
  b.c = 5

My "improved" query, on the old format:

select *
from a, b
where 
  a.a *= b.a AND
  b.c *= 5 AND
  b.c = 5 // (OR IS NOT NULL)

You can see the b.c *= 5 (JOIN ON ... AND b.c=5 ... WHERE b.c IS NOT NULL)
is redundant, because it's just a weaker predicate, and I needed to add it
just as an index hint on the join loop to trigger the right execution plan.


Keith Medcalf wrote
> I would sincerely doubt that there is *any* SQL optimizer or query planner
> that can optimize mis-spoken queries containing OUTER JOINS.

I don't know about mis-spoken, I don't think anything is mis-spoken.
This app is running fine on Maria, I'm in the process of porting in to
SQLite. I wouldn't have picked on this query unless it was lagging behind
orders of magnitude (2.5s vs 50ms). So I think Maria does it (I haven't
bothered to check the execution plan there, went straight to hacking
SQLite).



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Keith Medcalf]

>Thanks for acknowledging this, you are absolutely right, that's why I
>stated
>that no DB does perfect optimisations and that computing the
>alternative
>-OR- based closures are probably much harder to tackle. Also E. Pasma
>pointed out the -OR- queries as I wrote them are not really
>semantically
>equivalent unless the 2 joins are disjunct.

I suspect that query re-write of an outer join to an inner join would be 
violating some rule in the new SQL standards, most likely about visitation 
(nested loop) order -- which is freely reorderable for INNER JOINS but cannot 
be re-ordered for OUTER JOINS.  Back in the "olden days" one specified outer 
join conditions in the WHERE clause using *= =* or *=* syntax, where the * was 
on the side of the operator where all rows came from.  This was deprecated many 
years ago when the  JOIN ON  syntatactic sugar was created 
because too many people where forgetting that they need to use the appropriate 
* to "bind" the where condition to the appropriate outer join 
binding, then complaining that it was too difficult to remember or figure out 
where a plain "WHERE" clause was needed and where a "OUTER JOIN" bound 
condition was required, resulting in many calls to SQL Database support lines.  
And these same vendors also happen to be who write the specs, so they promptly 
changed the spec to eliminate the support calls.

So the ** format was written out of the standard and the OUTER JOIN 
ON became mandatory ONLY FOR OUTER JOIN operations.  In all other cases the 
JOIN ON syntax was just syntactic sugar for the old "list of tables" and where 
clause.  Don't recall exactly when this occurred but it was about two decades 
ago.  ( I remember it well because there were many queries that could not be 
expressed in the new-fangled format )

So really, adding conditions to apply to an OUTER JOIN to the where clause is 
equivalent to the common error of yester-decades of forgetting the *.

OLD SYNTAX:

select *
  from a, b
 where a.a *= b.a

was replaced by

select *
  from a LEFT JOIN b ON a.a = b.a

Of course, the old syntax allowed one to specify algebraic conditions that can 
no longer be expressed with the new syntax.  But that is OK, it is easier and 
simpler to say:  go RTFM, it is jolly clear, and if you don't like it then 
boo-hoo on you.  A good implementation was destroyed.  Live with it, love it, 
and get over it.

I would sincerely doubt that there is *any* SQL optimizer or query planner that 
can optimize mis-spoken queries containing OUTER JOINS.  Not even DB2's 
exhaustive search query planner/optimizer can do it, and it is quite possibly 
now one of the best in existence if you tell it that it can take unlimited time 
and resources to generate (and run) the plan.  All others pale in comparison.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[R Smith]

At the risk of preserving this thread well past its end of life cycle...

On 2018/01/05 6:58 PM, Dinu wrote:

Ryan,

You cannot ask SQL a Method query, that's where my whole RDBMS understanding
takes me.


Everything you ask SQL is underpinned by a specific Method. Perhaps I 
should have been more clear - by METHOD I mean, in the case of the join 
method, "the /way/ you expect the query to enumerate rows" such as an 
Inner join being one method, and outer join being another method. 
Sorting/Ordering is a method of output, grouping, etc.


Why would the SQL standard propose these different methods if they were 
not meaningful and distinct?


As I said before, there need not be an AI to judge that the query 
uttered by the programmer can in fact, in a narrow case, be recomputed 
as another query because the result will be the same and hopefully that 
the alternate method would be more efficient.


Note that I said there needn't be... I did not say there /can't/ be one, 
indeed, query flattening is a good example, but with query flattening 
the cost is low and the reward is high for a really broad spectrum of cases.


Why did I say it is not needed?  Well, what you propose has a relatively 
high cost (added heuristic AI) considering it is paid across all DB 
engine query planning to achieve a small advantage in the narrowest of 
use cases, not to mention that - should the programmer wish for a 
speed-up for the left join that conforms to this narrow set of 
circumstances, he or she could instantly change it to a normal join (the 
way it should have been to start with) and enjoy the fruits of the added 
speed with zero cost to the rest of us who wouldn't have made the 
imperfect query in the first place.


Why do I call it narrow?  Have you looked at your example queries in 
detail? Do you know how many things must be exactly just so (or how many 
other normal query things must be absent) for that join replacement to 
work algebraically? At least in the case of query flattening, it 
improved a query construct that is found abundantly and considered the 
correct construct for the expected results.


You essentially want the engine to second-guess programmers who didn't 
write the best query for their expected results. That kind of 
hand-holding belongs to the realms of Microsoft and MySQL.


(Apologies for all the word clarifications, but I'd rather avoid having 
this turn into a "semantics" debate, so trying to be as clear as 
possible on meanings of statements... not sure I succeeded though)  :)



Cheers,
Ryan

PS: I'm not judging MySQL, at least it has the benefit of being a fully 
fledged server-side software and greatly tweak-able on the fly for all 
its hand-holdy functionality.
PPS: I will say this - If you're not using MySQL in STRICT mode, you 
are  n  hours away from some disaster, where n is a not-too-big positive 
integer.

PPPS: I wish SQLite had a STRICT mode. :)



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Dinu]

Short sum-up:
- The -OR- alternative I provided is not semantically equivalent, I will
work on one that is :)
- The other one, without the -OR- (second post) still stands.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Dinu]

Richard,

Thanks for acknowledging this, you are absolutely right, that's why I stated
that no DB does perfect optimisations and that computing the alternative
-OR- based closures are probably much harder to tackle. Also E. Pasma
pointed out the -OR- queries as I wrote them are not really semantically
equivalent unless the 2 joins are disjunct.

However, the case of the imperative WHERE NOT NULL implying INNER JOIN is
just a matter of replacing a predicate with a stronger one, so in all
fairness I imagined it a far lesser overhead than, say, the query flattener.
And I imagine it's a much more common situation, too, especially when users
are adding additional filters via WHERE clauses to a base query, so it might
benefit a lot of users, too. I know it would us, by not having to rewrite
these queries when porting; we are working on x86 servers, and a stick of
memory or a hard drive cost less than a programmer's day for us :) 

For the extra memory, I know for computing relational closures the spatial
complexity can get big, but only when the structure of the query is written
warrants it in the first place, so it shouldn't manifest heavily on a query
that doesn't have this structure.

This is just my best view on this, obviously it's a political decision to be
made so it's no make-it-or-break-it thing, like mentioned before, we are
porting some pretty big system and when I notice differences with SQLite, I
jolt them down, in the hope it might benefit you or the millions of users,
if not by changing SQLite, then simply by pointing out the workaround to
other users, such as moving the WHERE condition out to the ON clause, it's
not necessarily a trivial thing to consider for everyone. 

Ryan, 

You cannot ask SQL a Method query, that's where my whole RDBMS understanding
takes me. It nullifies the purpose of queries as well as all efforts you
yourselves have put into a lot of things, query flattening to mention just
one. The "same result" is not accidental, the equivalent queries will
produce the same result no matter which data populates the tables. That is
the only deffinition I know of semantic equivalence. SQL is declarative and
thus everything that describes the same thing is the same thing.

E.Pasma,

Thanks for taking the time to make the TC. This is always a huge putdown for
me, because finally the execution plan depends on the data indexes are
populated with (via ANALYZE) and are tables are huge so it's always a
putdown for me to create a minimal TC.

Indeed I noticed just now the 2 queries are not equivalent that way :)
Thanks for pointing that out! I will work on an equivalent -and- optimized
rewrite :)

For the query plans though, here is where the index stats come in: here a
"SCAN a" makes sense, but in our case the number of records in a is on the
order of 1x records to b and c, and also the cardinality of b.d and c.d
is on the order of 1000; so a "SEARCH b, SEARCH c" works out. 

At minimum you should have indexes on b.d, c.d, a.ab, a.ac; but even so and
with adding another 1000 records on a, b and c and running the query:

EXPLAIN QUERY PLAN
SELECT * 
FROM 
  a 
JOIN 
  b ON b=ab AND b.d IN (1,2,3) 
JOIN 
  c ON c=ac AND c.d IN (4,5) 

selectid |order |from |detail |
-|--|-|---|
0|0 |0|SCAN TABLE a   |
0|1 |1|SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?) |
0|0 |0|EXECUTE LIST SUBQUERY 1|
0|2 |2|SEARCH TABLE c USING INTEGER PRIMARY KEY (rowid=?) |

it still plans a "SCAN a" first. So I guess I'll have to backtrack from the
real data to generate a TC.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[E.Pasma]

op 05-01-2018 17:23 schreef David Raymond op david.raym...@tomtom.com:

>> Anyway the two queries return the same set of rows.
> 
>> This test also show a small semantic difference in the two queries.
>> The set of rows is the same but the second query leaves certain
>> details null if only one of the OR conditions is true. That occurs in
>> row 1.
> 
> You're contradicting yourself there. If there's a difference in the results
> then they're not the same set of rows.
> 
> We'll just look at the "all 1" case. a has (1, 1, 1), b has (1, 1) and c has
> (1, 1)
> 
> Best if viewed in a fixed-width font
> 
> Step by step version 1:
> 
> a left join b on b = ab
> 
> a b
> a ab ac   b  d
> (1, 1, 1) (1, 1)
> 
> result:
> a ab ac  b  d
> (1, 1, 1, 1, 1)
> 
> left join c on c = ab
> 
> c
> a ab ac  b  d   c  d
> (1, 1, 1, 1, 1) (1, 1)
> 
> result:
> a ab ac  b  d  c  d
> (1, 1, 1, 1, 1, 1, 1)
> 
> where b.d in (1, 2, 3) or c.d in (4, 5)
> 
> b.d is 1, so it passes
> result:
> 
> a ab ac  b  d  c  d
> (1, 1, 1, 1, 1, 1, 1)
> 
> The OR worked. We got the values from both tables b, and c and because one of
> them was correct.
> 
> 
> 
> Now, Step by step version 2:
> 
> a left join b on b = ab and b.d in (1, 2, 3)
> 
> a b
> a ab ac   b  d
> (1, 1, 1) (1, 1)
> 
> result:
> a ab ac  b  d
> (1, 1, 1, 1, 1)  same so far (but only by coincidence)
> 
> left join c on c = ac and c.d in (4, 5)
> 
> c
> a ab ac  b  d   c  d
> (1, 1, 1, 1, 1) (1, 1)
> 
> the ON condition doesn't match. Since this is an outer join, and there were no
> matches for the row in the left side, nulls are included
> 
> result:
> a ab ac  b  dc d
> (1, 1, 1, 1, 1,  null, null)
> 
> where b.d is not null or c.d is not null
> 
> b.d isn't null, so that passes.
> result:
> 
> a ab ac  b  dc d
> (1, 1, 1, 1, 1,  null, null)
> 
> So we get a row saying that a matched something in b, but we're throwing out
> the value from the c table, which is not what we wanted.

Thanks, all clear except this last line. Did we not want the value to be
thrown out. Or not want the value?
It depends on that which query is favourite.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[David Raymond]

> Anyway the two queries return the same set of rows.

> This test also show a small semantic difference in the two queries.  
> The set of rows is the same but the second query leaves certain  
> details null if only one of the OR conditions is true. That occurs in  
> row 1.

You're contradicting yourself there. If there's a difference in the results 
then they're not the same set of rows.

We'll just look at the "all 1" case. a has (1, 1, 1), b has (1, 1) and c has 
(1, 1)

Best if viewed in a fixed-width font

Step by step version 1:

a left join b on b = ab

 a b
 a ab ac   b  d
(1, 1, 1) (1, 1)

result:
 a ab ac  b  d
(1, 1, 1, 1, 1)

left join c on c = ab

 c
 a ab ac  b  d   c  d
(1, 1, 1, 1, 1) (1, 1)

result:
 a ab ac  b  d  c  d
(1, 1, 1, 1, 1, 1, 1)

where b.d in (1, 2, 3) or c.d in (4, 5)

b.d is 1, so it passes
result:

 a ab ac  b  d  c  d
(1, 1, 1, 1, 1, 1, 1)

The OR worked. We got the values from both tables b, and c and because one of 
them was correct.



Now, Step by step version 2:

a left join b on b = ab and b.d in (1, 2, 3)

 a b
 a ab ac   b  d
(1, 1, 1) (1, 1)

result:
 a ab ac  b  d
(1, 1, 1, 1, 1)  same so far (but only by coincidence)

left join c on c = ac and c.d in (4, 5)

 c
 a ab ac  b  d   c  d
(1, 1, 1, 1, 1) (1, 1)

the ON condition doesn't match. Since this is an outer join, and there were no 
matches for the row in the left side, nulls are included

result:
 a ab ac  b  dc d
(1, 1, 1, 1, 1,  null, null)

where b.d is not null or c.d is not null

b.d isn't null, so that passes.
result:

 a ab ac  b  dc d
(1, 1, 1, 1, 1,  null, null)

So we get a row saying that a matched something in b, but we're throwing out 
the value from the c table, which is not what we wanted.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[E.Pasma]

Dinu wrote:


Hi all,
I've ran into an optimisation problem with a double-left join that  
works as

an "either" clause.

The query is as follows:

SELECT *
FROM
 a
LEFT JOIN
 b ON 
LEFT JOIN
 c ON 
WHERE
 b.someId IN (1,2,3) OR
 c.someId IN (4,5)

This results in a bloated execution plan:
SEARCH a
SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX
SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX

However, the semantically equivalent:
SELECT *
FROM
 a
LEFT JOIN
 b ON  AND b.someId IN (1,2,3)
LEFT JOIN
 c ON AND c.someId IN (4,5)
WHERE
 b.someId IS NOT NULL OR
 c.someId IS NOT NULL

Gets the proper execution plan:
SEARCH b
SEARCH c
EXECUTE LIST SUBQUERY




Hello, the discussion about whether the two queries are equivalent is  
not satisfactory to me. What Keith sais


Your "where" condition is effectively converted an OUTER JOIN into  
an INNER JOIN ..


is true. But the OR condition makes this true for either the one or  
the other outer join. I hope this is what Dinu means here:


And the two queries are algebrically identical. "(X ∊ S or X:=null)  
AND (X is not null)" is
equivalent to "X ∊ S is not null". The two queries might look  
different only

from an imperative programming point of view.


Anyway the two queries return the same set of rows.

Furthermore: what is a "bloated" execution plan?
I set up some test data and the query deamed bloated appears just as  
fast. See below.


This test also show a small semantic difference in the two queries.  
The set of rows is the same but the second query leaves certain  
details null if only one of the OR conditions is true. That occurs in  
row 1.

The outcome of the "bloated" execution plan is more complete.

Possibly I am too pragmatical and don't understand the discussion.

E. Pasma


My test script:

create table a (a integer primary key, ab, ac);
create table b (b integer primary key, d);
create table c (c integer primary key, d);
insert into a values (null,1,1);
insert into a select null, 2,2 from a;
insert into a select null, 3,2 from a;
insert into a select null, 4,4 from a;
insert into a select null, 5,5 from a;
insert into a select null, 6,6 from a;
insert into a select null, 7,7 from a;
insert into a select null, 8,8 from a;
insert into a select null, 9,9 from a;
insert into a select null, 10,10 from a;

insert into b values (1,1),(2,2),(3,3),(4,3),(6,3);
insert into c values (1,1),(4,5),(5,5),(7,1);
.eqp on
.timer on
SELECT *
FROM
 a
LEFT JOIN
 b ON b=ab
LEFT JOIN
 c ON c=ac
WHERE
 b.d IN (1,2,3) OR
 c.d IN (4,5)
;

SELECT *
FROM
 a
LEFT JOIN
 b ON b=ab AND b.d IN (1,2,3)
LEFT JOIN
 c ON c=ac AND c.d IN (4,5)
WHERE
 b.d IS NOT NULL OR
 c.d IS NOT NULL
;

Output:

--EQP-- 0,0,0,SCAN TABLE a
--EQP-- 0,1,1,SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?)
--EQP-- 0,2,2,SEARCH TABLE c USING INTEGER PRIMARY KEY (rowid=?)
--EQP-- 0,0,0,EXECUTE LIST SUBQUERY 1
1|1|1|1|1|1|1
2|2|2|2|2||
3|3|2|3|3||
4|3|2|3|3||
5|4|4|4|3|4|5
6|4|4|4|3|4|5
7|4|4|4|3|4|5
8|4|4|4|3|4|5
9|5|5|||5|5
10|5|5|||5|5
11|5|5|||5|5
12|5|5|||5|5
13|5|5|||5|5
14|5|5|||5|5
15|5|5|||5|5
16|5|5|||5|5
17|6|6|6|3||
18|6|6|6|3||
19|6|6|6|3||
20|6|6|6|3||
21|6|6|6|3||
22|6|6|6|3||
23|6|6|6|3||
24|6|6|6|3||
25|6|6|6|3||
26|6|6|6|3||
27|6|6|6|3||
28|6|6|6|3||
29|6|6|6|3||
30|6|6|6|3||
31|6|6|6|3||
32|6|6|6|3||
Run Time: real 0.003 user 0.001587 sys 0.000358
--EQP-- 0,0,0,SCAN TABLE a
--EQP-- 0,1,1,SEARCH TABLE b USING INTEGER PRIMARY KEY (rowid=?)
--EQP-- 0,0,0,EXECUTE LIST SUBQUERY 1
--EQP-- 0,2,2,SEARCH TABLE c USING INTEGER PRIMARY KEY (rowid=?)
1|1|1|1|1||
2|2|2|2|2||
3|3|2|3|3||
4|3|2|3|3||
5|4|4|4|3|4|5
6|4|4|4|3|4|5
7|4|4|4|3|4|5
8|4|4|4|3|4|5
9|5|5|||5|5
10|5|5|||5|5
11|5|5|||5|5
12|5|5|||5|5
13|5|5|||5|5
14|5|5|||5|5
15|5|5|||5|5
16|5|5|||5|5
17|6|6|6|3||
18|6|6|6|3||
19|6|6|6|3||
20|6|6|6|3||
21|6|6|6|3||
22|6|6|6|3||
23|6|6|6|3||
24|6|6|6|3||
25|6|6|6|3||
26|6|6|6|3||
27|6|6|6|3||
28|6|6|6|3||
29|6|6|6|3||
30|6|6|6|3||
31|6|6|6|3||
32|6|6|6|3||
Run Time: real 0.002 user 0.001560 sys 0.000296








___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Richard Hipp]

On 1/4/18, Dinu  wrote:
>  I think it's every DB's intention to optimize as best possible
> a query into an execution plan. None does it perfectly, but all try to, very
> hard.

There are trade-offs here.  How much slower are you willing for
sqlite3_prepare() to run in order to get a better query plan?  How
much extra memory and disk space are you willing to allocation to
libsqlite3.so in order to get a better query plan?  Are you willing to
impose these costs on (literally) billions of other users that don't
really need the more advanced query planning?  These are hard
questions with no easy answers.

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[R Smith]

On 2018/01/05 4:24 AM, Dinu wrote:

Thank you for your answer, Keith. I had my problem "fixed" before I wrote the
first mail. Also with every problem I also provided the fix that worked, for
anyone that might run into the same problem.
However, it's difficult to not get a little frustrated with your answer.

At https://sqlite.org/queryplanner.html I read:

"The best feature of SQL (in all its implementations, not just SQLite) is
that it is a declarative language, not a procedural language. When
programming in SQL you tell the system what you want to compute, not how to
compute it."


I'm sure his frustration is on a par. :)
While we've both stated that your queries are not equivalent, Keith took 
the time to write an explanation of why that is, which seemingly did not 
hit home, and now I will try again with an analogy:


You are essentially have a delivery person usually tasked to take a 
truck, go to a farm and pick up a load of eggs. Now you ask the same 
driver to use the same truck to go to the corner cafe and pick up 6 
eggs, and then you exclaim "Wow, why he takes the truck? he can just 
take the scooter!! The truck is sooo inefficient for this job!".


And yes, you are right, but the problem is you asked for it to be done 
by truck. Now I agree the ideal in SQL is (as the quote above states) 
that one should merely ask for a result and the engine should decide how 
best to achieve it, but in practice there are many nuances in the 
programming of the engine that thwarts this ideal, not to mention how 
many programmers like to tweak their queries to get the engine to 
execute the quickest, and there is nothing wrong with this. It does 
however mean that the engine should in all circumstances, while trying 
to find the best query plan, still adhere to the type of question that 
was asked of it. You asked a METHOD A question, it won't (and shouldn't) 
apply a METHOD B to reach the accidental similar result.


Second problem, your queries show the narrowest of use cases. The engine 
has to work for ALL use cases which can get very involved and complex. 
The engine needn't have another level of abstraction AI going "Oh this 
one is simple, we will disregard what the programmer asked for and use 
our own more simple query because it should get the same result."




To sum it up: I think it's every DB's intention to optimize as best possible
a query into an execution plan. None does it perfectly, but all try to, very
hard. With this intention, I reported a case where the query planner COULD
be improved. I think you will at least agree with me that making it better
can't be wrong. Whether that happens tomorrow, in a year or never, that's up
to the mercy, resources and priorities of the developers, so I am really am
not interested in an argue over this.


It's a good idea to report possible improvements, and thank you for 
that, but this case isn't able to improve since mangling an outer join 
into an inner join when sometimes it might yield the same result is as 
unsafe as it gets. However, that doesn't mean the devs (who would have 
read all this) doesn't find something of interest and could possibly 
think of a tweak that might improve things, so having this debate is 
never a waste, but the specific algebraic essence of what you are 
suggesting is not correct - 's all we're sayin.


Cheers!
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Dinu]

Thank you for your answer, Keith. I had my problem "fixed" before I wrote the
first mail. Also with every problem I also provided the fix that worked, for
anyone that might run into the same problem.
However, it's difficult to not get a little frustrated with your answer. 

At https://sqlite.org/queryplanner.html I read:

"The best feature of SQL (in all its implementations, not just SQLite) is
that it is a declarative language, not a procedural language. When
programming in SQL you tell the system what you want to compute, not how to
compute it."

And I completely agree with this, "how to compute it" is called relational
algebra and it's what a query planner should do best. And the two queries
are algebrically identical. "(X ∊ S or X:=null) AND (X is not null)" is
equivalent to "X ∊ S is not null". The two queries might look different only
from an imperative programming point of view.

As to why the query is written that way: with the above in mind, I will
contend that there can absolutely never exist a "mistaken" way to write a
query, as long as the description of the predicates is correct and
consistent with the schema. You should consider that quite frequently
queries are the result of one or more levels of logic abstraction (ORM,
DBAL, etc). In my case, modifying the query was not difficult to do, but in
other cases one may have few options on rewriting the way the query
structure is generated. The only way to reduce a fabricated query is through
relational algebra, and that is up to the DB, not the programmer, not the
abstractions in-between.

In this particular case, the where is optional; depending on parameters, I
want the set of data that is correctly defined as the left join of tables a
and b, or I might want a subset of this join that has a particular property
over the left-joined set. The query was correctly written, to rewrite it so
that the query planner might know how to run it is wrong, IMHO.

To sum it up: I think it's every DB's intention to optimize as best possible
a query into an execution plan. None does it perfectly, but all try to, very
hard. With this intention, I reported a case where the query planner COULD
be improved. I think you will at least agree with me that making it better
can't be wrong. Whether that happens tomorrow, in a year or never, that's up
to the mercy, resources and priorities of the developers, so I am really am
not interested in an argue over this. 



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Keith Medcalf]

They are not the same.  Just as 5 - 3 is not the same as 1 + 1, even though 
both come up with the same result, 2. by happenstance.

Your "where" condition is effectively converted an OUTER JOIN into an INNER 
JOIN through artifice (and quite likely mistake).  If you *want* an inner join, 
use an inner join.  If you want an outer join, use an outer join.  Just because 
subtraction of two different numbers may have the same result as addition of 
two other numbers, does not meant that addition and subtraction are the same 
thing.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Dinu
>Sent: Thursday, 4 January, 2018 16:01
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] LEFT JOIN + WHERE / OR optimisation
>
>Algebrically, having a non-null imperative lookup condition in the
>WHERE
>clause means you have a stronger predicate on the same subject (ALL
>MUST fit
>vs. ANY that fit).
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>___
>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

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Dinu]

Algebrically, having a non-null imperative lookup condition in the WHERE
clause means you have a stronger predicate on the same subject (ALL MUST fit
vs. ANY that fit).



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Dinu]

I think they are equivalent, if you look closer.

SELECT FROM a LEFT JOIN b ON a.x=b.x WHERE b.y=5 -is- equivalent to 
SELECT FROM a JOIN b ON a.x=b.x AND b.y=5
SELECT FROM a JOIN b WHERE a.x=b.x AND b.y=5
SELECT FROM a LEFT JOIN b ON a.x=b.x AND b.y=5 WHERE b.y IS NOT NULL

All the above are semantically equivalent. When there is only one LEFT JOIN,
the presence of any non-null non-alternative condition on the joined table
in the WHERE clause transforms it in an INNER join. There is no other way to
have a non-null value except if the row exists. The reciprocal is not true
of course.

I don't know how difficult it is to compute the -OR- closure, as it is more
difficult. But for an imperative non-null condition, I did expect the WHERE
condition to be ported to the ON lookup for optimisation.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[David Raymond]

The ON condition is used  the "add one result row for each row of the 
outer table where nothing matches the ON condition"
The WHERE condition is used  those rows are added.

Example with the basic "not in" type of outer join:

SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table a (x);

sqlite> create table b (x);

sqlite> insert into a values (1), (2), (3);

sqlite> insert into b values (2);

sqlite> select a.x from a left outer join b on a.x = b.x where b.x is null;
x
1
3

sqlite> select a.x from a left outer join b on a.x = b.x and b.x is null;
x
1
2
3

sqlite> select a.x from a left outer join b where a.x = b.x and b.x is null;

sqlite>

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Keith Medcalf
Sent: Thursday, January 04, 2018 2:53 PM
To: SQLite mailing list
Subject: Re: [sqlite] LEFT JOIN + WHERE / OR optimisation


They are not semantically equivalent.  join conditions attached to an outer 
join operation are not semantically equivalent to the same conditions being in 
the where clause.

In other words:

select a,b,c
  from a
  join b
  join c on a.a=b.b
 where c.c=b.d

is simply syntactic sugar for

select a,b,c
  from a, b, c
 where a.a=b.b
   and c.c=b.d;

In all cases the conditions in ON clauses of INNER JOINS are nothing more than 
WHERE clause filters.  You do not even have to have the tables used in the ON 
clause "referenced" at the point you refer to them.

the word "INNER JOIN" is syntactic sugar for a comma (,), and ON is sytactic 
sugar for the word WHERE (or AND).

However, for OUTER JOINS the conditions in the ON clause "glue themselves" to 
the OUTER JOIN operation and ARE NEITHER syntactically or symantically the same 
as WHERE clause conditions.

That is to say the behaviour observed is how it is designed to work and you 
expectations are misguided.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Dinu
>Sent: Thursday, 4 January, 2018 12:29
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] LEFT JOIN + WHERE / OR optimisation
>
>Hi all,
>I've ran into an optimisation problem with a double-left join that
>works as
>an "either" clause.
>
>The query is as follows:
>
>SELECT *
>FROM
>  a
>LEFT JOIN
>  b ON 
>LEFT JOIN
>  c ON 
>WHERE
>  b.someId IN (1,2,3) OR
>  c.someId IN (4,5)
>
>This results in a bloated execution plan:
>SEARCH a
>SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX
>SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX
>
>However, the semantically equivalent:
>SELECT *
>FROM
>  a
>LEFT JOIN
>  b ON  AND b.someId IN (1,2,3)
>LEFT JOIN
>  c ON AND c.someId IN (4,5)
>WHERE
>  b.someId IS NOT NULL OR
>  c.someId IS NOT NULL
>
>Gets the proper execution plan:
>SEARCH b
>SEARCH c
>EXECUTE LIST SUBQUERY
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>___
>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

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[R Smith]

On 2018/01/04 9:28 PM, Dinu wrote:

Hi all,
I've ran into an optimisation problem with a double-left join that works as
an "either" clause.

The query is as follows:

SELECT *
FROM
   a
LEFT JOIN
   b ON 
LEFT JOIN
   c ON 
WHERE
   b.someId IN (1,2,3) OR
   c.someId IN (4,5)

This results in a bloated execution plan:
SEARCH a
SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX
SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX

However, the semantically equivalent:
SELECT *
FROM
   a
LEFT JOIN
   b ON  AND b.someId IN (1,2,3)
LEFT JOIN
   c ON AND c.someId IN (4,5)
WHERE
   b.someId IS NOT NULL OR
   c.someId IS NOT NULL

Gets the proper execution plan:
SEARCH b
SEARCH c
EXECUTE LIST SUBQUERY


These Queries are not equivalent, they cannot and should not have the 
same query plan.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Keith Medcalf]

They are not semantically equivalent.  join conditions attached to an outer 
join operation are not semantically equivalent to the same conditions being in 
the where clause.

In other words:

select a,b,c
  from a
  join b
  join c on a.a=b.b
 where c.c=b.d

is simply syntactic sugar for

select a,b,c
  from a, b, c
 where a.a=b.b
   and c.c=b.d;

In all cases the conditions in ON clauses of INNER JOINS are nothing more than 
WHERE clause filters.  You do not even have to have the tables used in the ON 
clause "referenced" at the point you refer to them.

the word "INNER JOIN" is syntactic sugar for a comma (,), and ON is sytactic 
sugar for the word WHERE (or AND).

However, for OUTER JOINS the conditions in the ON clause "glue themselves" to 
the OUTER JOIN operation and ARE NEITHER syntactically or symantically the same 
as WHERE clause conditions.

That is to say the behaviour observed is how it is designed to work and you 
expectations are misguided.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Dinu
>Sent: Thursday, 4 January, 2018 12:29
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] LEFT JOIN + WHERE / OR optimisation
>
>Hi all,
>I've ran into an optimisation problem with a double-left join that
>works as
>an "either" clause.
>
>The query is as follows:
>
>SELECT *
>FROM
>  a
>LEFT JOIN
>  b ON 
>LEFT JOIN
>  c ON 
>WHERE
>  b.someId IN (1,2,3) OR
>  c.someId IN (4,5)
>
>This results in a bloated execution plan:
>SEARCH a
>SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX
>SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX
>
>However, the semantically equivalent:
>SELECT *
>FROM
>  a
>LEFT JOIN
>  b ON  AND b.someId IN (1,2,3)
>LEFT JOIN
>  c ON AND c.someId IN (4,5)
>WHERE
>  b.someId IS NOT NULL OR
>  c.someId IS NOT NULL
>
>Gets the proper execution plan:
>SEARCH b
>SEARCH c
>EXECUTE LIST SUBQUERY
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>___
>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

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Dinu]

Probably related:

Compound join with a left outer join generates different execution plans:

LEFT JOIN (
  b 
  JOIN c ON 
)
WHERE
  b.something = 5

vs.

LEFT JOIN (
  b 
  JOIN c ON  AND b.something = 5
)
WHERE
  b.something IS NOT NULL




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] LEFT JOIN + WHERE / OR optimisation

[Dinu]

Hi all,
I've ran into an optimisation problem with a double-left join that works as
an "either" clause.

The query is as follows:

SELECT *
FROM
  a
LEFT JOIN
  b ON 
LEFT JOIN 
  c ON 
WHERE
  b.someId IN (1,2,3) OR
  c.someId IN (4,5)

This results in a bloated execution plan:
SEARCH a
SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX
SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX

However, the semantically equivalent:
SELECT *
FROM
  a
LEFT JOIN
  b ON  AND b.someId IN (1,2,3)
LEFT JOIN 
  c ON AND c.someId IN (4,5)
WHERE
  b.someId IS NOT NULL OR
  c.someId IS NOT NULL

Gets the proper execution plan:
SEARCH b
SEARCH c
EXECUTE LIST SUBQUERY



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Left join help.

[James K. Lowden]

On Fri, 26 Apr 2013 14:06:23 +0200
Clemens Ladisch  wrote:

> When using CROSS where standard SQL allows it (for an actual cartesian
> product), the table ordering does not really matter for optimization
> purposes because the DB has to do two nested full table scans anyway.

Well, no, not really: it doesn't *have* to.  

Logically, 

from A join B 
on A.a = B.a

is exactly the same as 

from A cross join B
where A.a = B.a

and

from A, B
where A.a = B.a

and the DBMS is free to treat them the same.  In fact, many do.  :-)

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Left join help.

[Danilo Cicerone]

Many thanks to you all, I really appraciate your helpfulness,
Danilo


2013/4/26 Clemens Ladisch 

> Hick Gunter wrote:
> > Actually "CROSS" just forces SQLite to use the tables in the order
> > specified.
>
> This is an SQLite optimization extension.  I used CROSS because this is
> the only explicit join type where standard SQL allows to omit the join
> expression.
>
> When using CROSS where standard SQL allows it (for an actual cartesian
> product), the table ordering does not really matter for optimization
> purposes because the DB has to do two nested full table scans anyway.
>
> > It is the LEFT join that creates the rows where there is no match on
> > the right hand side.
>
> It is the CROSS join that creates the rows where there is no appx
> record.  The LEFT join just does not filter out those.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Left join help.

[Clemens Ladisch]

Hick Gunter wrote:
> Actually "CROSS" just forces SQLite to use the tables in the order
> specified.

This is an SQLite optimization extension.  I used CROSS because this is
the only explicit join type where standard SQL allows to omit the join
expression.

When using CROSS where standard SQL allows it (for an actual cartesian
product), the table ordering does not really matter for optimization
purposes because the DB has to do two nested full table scans anyway.

> It is the LEFT join that creates the rows where there is no match on
> the right hand side.

It is the CROSS join that creates the rows where there is no appx
record.  The LEFT join just does not filter out those.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Left join help.

[Hick Gunter]

Actually "CROSS" just forces SQLite to use the tables in the order specified. 
The result sets are identical, except for ordering and/or speed. It is the LEFT 
join that creates the rows where there is no match on the right hand side.

explain query plan select sub_descr,itx_descr,ifnull(app_load,0) from itx join 
subj left outer join appx on app_ref_itx=itx_id and app_ref_sub=sub_id;
sele  order  from  deta
  -    
0 0  0 SCAN TABLE itx (~100 rows)
0 1  1 SCAN TABLE subj (~100 rows)
0 2  2 SEARCH TABLE appx USING INDEX 
sqlite_autoindex_appx_1 (app_ref_itx=? AND app_ref_sub=?) (~1 rows)
explain query plan SELECT sub_descr,
   ...>itx_descr,
   ...>IFNULL(app_load, 0)
   ...> FROM   subj
   ...> CROSS JOIN itx
   ...>  LEFT JOIN appx ON sub_id = app_ref_sub AND
   ...>itx_id = app_ref_itx
   ...> ;
sele  order  from  deta
  -    
0 0  0 SCAN TABLE subj (~100 rows)
0 1  1 SCAN TABLE itx (~100 rows)
0 2  2 SEARCH TABLE appx USING INDEX 
sqlite_autoindex_appx_1 (app_ref_itx=? AND app_ref_sub=?) (~1 rows)

-Ursprüngliche Nachricht-
Von: Clemens Ladisch [mailto:clem...@ladisch.de]
Gesendet: Freitag, 26. April 2013 12:40
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Left join help.

Danilo Cicerone wrote:
> table appx stores item's(table itx) quantity load for each user (table
> subj). I'd to know how many items each user has:
>
> Paul|Box|3
> Paul|Letter|0
> Paul|Pen|0
> John|Box|0
> John|Letter|4
> John|Pen|0
>
> I tried:
>
> select sub_descr, itx_descr, app_load from subj left outer join appx
> on sub_id = app_ref_sub left outer join itx on app_ref_itx = itx_id;
>
> but it returns only:
>
> Paul|Box|3
> John|Letter|4

Your first LEFT includes users that do not have any items.
Your second LEFT includes quantity loads for which no item exists.

What you actually want is all combinations of users and items.  This is a cross 
join:

SELECT sub_descr,
   itx_descr,
   IFNULL(app_load, 0)
FROM   subj
CROSS JOIN itx
 LEFT JOIN appx ON sub_id = app_ref_sub AND
   itx_id = app_ref_itx


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Left join help.

[Hick Gunter]

http://www.sqlite.org/lang_select.html

(1) "If there is an ON clause specified, then the ON expression is evaluated 
for each row of the cartesian product as a boolean expression. All rows for 
which the expression evaluates to false are excluded from the dataset."


(2) " If the join-op is a "LEFT JOIN" or "LEFT OUTER JOIN", then after the ON 
or USING filtering clauses have been applied, an extra row is added to the 
output for each row in the original left-hand input dataset that corresponds to 
no rows at all in the composite dataset (if any). The added rows contain NULL 
values in the columns that would normally contain values copied from the 
right-hand input dataset."

(3) " When more than two tables are joined together as part of a FROM clause, 
the join operations are processed in order from left to right. In other words, 
the FROM clause (A join-op-1 B join-op-2 C) is computed as ((A join-op-1 B) 
join-op-2 C)."


([1,Paul], [2,John]) X ([1,1,3],[3,2,4])



=(1)=> ([1,Paul,1,1,3],[2,John,3,2,4])



(2) does not apply, as all LEFT rows have 1 corresponding result row



=(3)=>


([1,Paul,1,1,3],[2,John,3,2,4]) X ([1,Box],[2,Pen],[3,Letter])

=(1)=> ([1,Paul,1,1,3,1,Box],[2,John,3,2,4,3,Letter])



(2) does not apply, as all LEFT rows have 1 corresponding result row



>From which you select [Paul,Box,3] and [John,Letter,4].



The only way you are going to get more than 2 rows out of the query is to first 
join (no need for LEFT or OUTER) subj with itx; then you can go looking for the 
appx row that matches the combination.





Solution 1 (subquery)



select sub_descr,itx_descr,ifnull((select app_load from appx where 
app_ref_itx=itx_id and app_ref_sub=sub_id),0) load from itx  join subj;



sub_descr  itx_descr  load

-  -  

Paul   Box3

John   Box0

Paul   Pen0

John   Pen0

Paul   Letter 0

John   Letter 4



Solution 2 (join):



select sub_descr,itx_descr,ifnull(app_load,0) from itx join subj left outer 
join appx on app_ref_itx=itx_id and app_ref_sub=sub_id;



sub_descr  itx_descr  ifnull(app_load,0)

-  -  --

Paul   Box3

John   Box0

Paul   Pen0

John   Pen0

Paul   Letter 0

John   Letter 4



Don't forget to add an ORDER BY clause if you want a specific order.



-Ursprüngliche Nachricht-
Von: Danilo Cicerone [mailto:cyds...@gmail.com]
Gesendet: Freitag, 26. April 2013 11:40
An: SQLITE Forum
Betreff: [sqlite] Left join help.



Hi to all,

I'm looking for a query on the following schema:



PRAGMA foreign_keys=ON;

BEGIN TRANSACTION;

CREATE TABLE subj

(

   sub_id INTEGER PRIMARY KEY, -- 00

   sub_descr TEXT DEFAULT NULL -- 01

);

INSERT INTO "subj" VALUES(1,'Paul');

INSERT INTO "subj" VALUES(2,'John');

CREATE TABLE itx

(

   itx_id INTEGER PRIMARY KEY, -- 00

   itx_descr TEXT DEFAULT NULL -- 01

);

INSERT INTO "itx" VALUES(1,'Box');

INSERT INTO "itx" VALUES(2,'Pen');

INSERT INTO "itx" VALUES(3,'Letter');

CREATE TABLE appx

(

   app_ref_itx INTEGER DEFAULT NULL -- 00

  REFERENCES itx(itx_id) ON UPDATE CASCADE ON DELETE RESTRICT,

   app_ref_sub INTEGER DEFAULT NULL -- 01

  REFERENCES subj(sub_id) ON UPDATE CASCADE ON DELETE RESTRICT,

   app_load INTEGER NOT NULL DEFAULT 0, -- 02

   UNIQUE(app_ref_itx, app_ref_sub)

);

INSERT INTO "appx" VALUES(1,1,3);

INSERT INTO "appx" VALUES(3,2,4);

COMMIT;



table appx stores item's(table itx) quantity load for each user (table subj). 
I'd to know how many items each user has:



Paul|Box|3

Paul|Letter|0

Paul|Pen|0

John|Box|0

John|Letter|4

John|Pen|0



and the above is the result what I aim to!!!



I tried:



select sub_descr, itx_descr, app_load from subj left outer join appx on sub_id 
= app_ref_sub left outer join itx on app_ref_itx = itx_id;



but it returns only:



Paul|Box|3

John|Letter|4



Thanks in advance, Danilo

___

sqlite-users mailing list

sqlite-users@sqlite.org<mailto:sqlite-users@sqlite.org>

http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 - 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Left join help.

[Clemens Ladisch]

Danilo Cicerone wrote:
> table appx stores item's(table itx) quantity load for each user (table
> subj). I'd to know how many items each user has:
>
> Paul|Box|3
> Paul|Letter|0
> Paul|Pen|0
> John|Box|0
> John|Letter|4
> John|Pen|0
>
> I tried:
>
> select sub_descr, itx_descr, app_load from subj
> left outer join appx on sub_id = app_ref_sub
> left outer join itx on app_ref_itx = itx_id;
>
> but it returns only:
>
> Paul|Box|3
> John|Letter|4

Your first LEFT includes users that do not have any items.
Your second LEFT includes quantity loads for which no item exists.

What you actually want is all combinations of users and items.  This
is a cross join:

SELECT sub_descr,
   itx_descr,
   IFNULL(app_load, 0)
FROM   subj
CROSS JOIN itx
 LEFT JOIN appx ON sub_id = app_ref_sub AND
   itx_id = app_ref_itx


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Left join help.

[Danilo Cicerone]

Hi to all,
I'm looking for a query on the following schema:

PRAGMA foreign_keys=ON;
BEGIN TRANSACTION;
CREATE TABLE subj
(
   sub_id INTEGER PRIMARY KEY, -- 00
   sub_descr TEXT DEFAULT NULL -- 01
);
INSERT INTO "subj" VALUES(1,'Paul');
INSERT INTO "subj" VALUES(2,'John');
CREATE TABLE itx
(
   itx_id INTEGER PRIMARY KEY, -- 00
   itx_descr TEXT DEFAULT NULL -- 01
);
INSERT INTO "itx" VALUES(1,'Box');
INSERT INTO "itx" VALUES(2,'Pen');
INSERT INTO "itx" VALUES(3,'Letter');
CREATE TABLE appx
(
   app_ref_itx INTEGER DEFAULT NULL -- 00
  REFERENCES itx(itx_id) ON UPDATE CASCADE ON DELETE RESTRICT,
   app_ref_sub INTEGER DEFAULT NULL -- 01
  REFERENCES subj(sub_id) ON UPDATE CASCADE ON DELETE RESTRICT,
   app_load INTEGER NOT NULL DEFAULT 0, -- 02
   UNIQUE(app_ref_itx, app_ref_sub)
);
INSERT INTO "appx" VALUES(1,1,3);
INSERT INTO "appx" VALUES(3,2,4);
COMMIT;

table appx stores item's(table itx) quantity load for each user (table
subj). I'd to know how many items each user has:

Paul|Box|3
Paul|Letter|0
Paul|Pen|0
John|Box|0
John|Letter|4
John|Pen|0

and the above is the result what I aim to!!!

I tried:

select sub_descr, itx_descr, app_load from subj
left outer join appx on sub_id = app_ref_sub
left outer join itx on app_ref_itx = itx_id;

but it returns only:

Paul|Box|3
John|Letter|4

Thanks in advance, Danilo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN optimization

[Dan Kennedy]

On 09/23/2011 04:01 AM, Mira Suk wrote:


EXPLAIN QUERY PLAN
SELECT DISTINCT ItemsME.Points
FROM (ItemsME_Properties LEFT JOIN ItemsME ON ItemsME_Properties.IDR = 
ItemsME.IDR)
WHERE ItemsME.IDR
IN
(SELECT IDR FROM cProds WHERE Prod = 106)

selectidorderfromdetail
000SCAN TABLE ItemsME_Properties (~100 rows)
011SEARCH TABLE ItemsME USING INDEX IDR (IDR=?) (~2 rows)
000EXECUTE LIST SUBQUERY 1
100SEARCH TABLE cProds USING INDEX Prod (Prod=?) (~210 rows)
000EXECUTE LIST SUBQUERY 2
200SEARCH TABLE cProds USING INDEX Prod (Prod=?) (~210 rows)
000USE TEMP B-TREE FOR DISTINCT
this takes about 3 seconds


EXPLAIN QUERY PLAN
SELECT DISTINCT ItemsME.Points
FROM (ItemsME_Properties INNER JOIN ItemsME ON ItemsME_Properties.IDR = 
ItemsME.IDR)
WHERE ItemsME.IDR
IN
(SELECT IDR FROM cProds WHERE Prod = 106)

001SEARCH TABLE ItemsME USING INDEX IDR (IDR=?) (~25 rows)
000EXECUTE LIST SUBQUERY 1
100SEARCH TABLE cProds USING INDEX Prod (Prod=?) (~210 rows)
010SEARCH TABLE ItemsME_Properties USING COVERING INDEX IDR (IDR=?) 
(~1 rows)
000USE TEMP B-TREE FOR DISTINCT
this runs in about 15 milisec

=>  results are equal from both cases as ItemsME_Properties is temporary table 
(updated with triggers as reads are much more frequent) to workaround performance 
issues with search... (it should be 1:1 with ItemsME, just with less data, and 
some data reformatted)

maybe I'm just stupid but I don't really see reason for scan in first case.


Basically it is because with an inner join SQLite can
reorder the tables in the join - "A INNER JOIN B" is
the same as "B INNER JOIN A". But it can't do that
with left joins - as "A LEFT JOIN B ON " is not
always the same as "B LEFT JOIN A ON ".

And in this case SQLite needs to reorder tables ItemsME
and ItemsME_Properties in order to avoid the table scan.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN optimization

[Mira Suk]

_
> Od: "Jim Morris" 
> Komu: 
> Datum: 22.09.2011 23:06
> Předmět: Re: [sqlite] LEFT JOIN optimization
>
> Your where clause "WHERE ItemsME.IDR ..." is only satisfied if there is 
> an associated ItemsME record so the left outer join is pointless.  Just 
> use the inner join.  Normally the left outer join would include all of 
> ItemsME_Properties, that probably explains the table scan.
 
Yeah. the answer is I'm stupid. the left join was just for test sake (as was 
said tables are 1:1 and IDR is unique) as I have all kinds of performance 
problems with left outers. this one is on me not on SQLite lol - still would 
say it's strange quirk - but the query is also strange.
 
Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN optimization

[Jim Morris]

Your where clause "WHERE ItemsME.IDR ..." is only satisfied if there is 
an associated ItemsME record so the left outer join is pointless.  Just 
use the inner join.  Normally the left outer join would include all of 
ItemsME_Properties, that probably explains the table scan.


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] LEFT JOIN optimization

[Mira Suk]

EXPLAIN QUERY PLAN
SELECT DISTINCT ItemsME.Points
FROM (ItemsME_Properties LEFT JOIN ItemsME ON ItemsME_Properties.IDR = 
ItemsME.IDR)
WHERE ItemsME.IDR
IN
(SELECT IDR FROM cProds WHERE Prod = 106)
 
selectid    order    from    detail
0    0    0    SCAN TABLE ItemsME_Properties (~100 rows)
0    1    1    SEARCH TABLE ItemsME USING INDEX IDR (IDR=?) (~2 rows)
0    0    0    EXECUTE LIST SUBQUERY 1
1    0    0    SEARCH TABLE cProds USING INDEX Prod (Prod=?) (~210 rows)
0    0    0    EXECUTE LIST SUBQUERY 2
2    0    0    SEARCH TABLE cProds USING INDEX Prod (Prod=?) (~210 rows)
0    0    0    USE TEMP B-TREE FOR DISTINCT
this takes about 3 seconds
 
 
EXPLAIN QUERY PLAN
SELECT DISTINCT ItemsME.Points
FROM (ItemsME_Properties INNER JOIN ItemsME ON ItemsME_Properties.IDR = 
ItemsME.IDR)
WHERE ItemsME.IDR
IN
(SELECT IDR FROM cProds WHERE Prod = 106)
 
0    0    1    SEARCH TABLE ItemsME USING INDEX IDR (IDR=?) (~25 rows)
0    0    0    EXECUTE LIST SUBQUERY 1
1    0    0    SEARCH TABLE cProds USING INDEX Prod (Prod=?) (~210 rows)
0    1    0    SEARCH TABLE ItemsME_Properties USING COVERING INDEX IDR (IDR=?) 
(~1 rows)
0    0    0    USE TEMP B-TREE FOR DISTINCT
this runs in about 15 milisec
 
=> results are equal from both cases as ItemsME_Properties is temporary table 
(updated with triggers as reads are much more frequent) to workaround 
performance issues with search... (it should be 1:1 with ItemsME, just with 
less data, and some data reformatted)
 
maybe I'm just stupid but I don't really see reason for scan in first case. am 
I really stupid ?
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Left Join Issue

[Vitali Kiruta]

>
> SELECT
> CallsList.NUMBER,ContactList.LAST_NAME,CallsList.DATE_AND_TIME,CallsList.READ_STATUS,LineSettingsList.LINE_NAME,CallsList.LINE_ID,CallsList.NUM_OF_CALLS
> FROM CallsList WHERE NUMBER<='31'AND CALL_TYPE=0 COLLATE NOCASE LEFT JOIN
> ContactList ON CallsList.NUMBER=ContactList.CONTACT_NUM_1 LEFT JOIN
> LineSettingsList ON CallsList.LINE_ID=LineSettingsList.LINE_ID ORDER BY
> NUMBER COLLATE NOCASE DESC;
>
>
> Error: near "LEFT": syntax error


Seems like you need to put the WHERE clause after the JOIN clause. Try this:

SELECT 
CallsList.NUMBER,ContactList.LAST_NAME,CallsList.DATE_AND_TIME,CallsList.READ_STATUS,LineSettingsList.LINE_NAME,CallsList.LINE_ID,CallsList.NUM_OF_CALLS
FROM CallsList LEFT JOIN
ContactList ON CallsList.NUMBER=ContactList.CONTACT_NUM_1 LEFT JOIN
LineSettingsList ON CallsList.LINE_ID=LineSettingsList.LINE_ID
WHERE NUMBER<='31'AND CALL_TYPE=0 COLLATE NOCASE
ORDER BY NUMBER COLLATE NOCASE DESC;

Regards
Vitali
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Left Join Issue

[Suresh Arunachalam]

Hi All,

I am facing syntax error on Left join with my sqllite setup. The schema and
query are given below with error

CREATE TABLE CallsList ( ENTRY_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT
NULL,NUMBER TEXT NOT NULL,DATE_AND_TIME INTEGER NOT NULL,LINE_ID INTEGER NOT
NULL,NUM_OF_CALLS INTEGER,READ_STATUS CHAR,CALL_TYPE CHAR NOT NULL );

CREATE TABLE ContactList ( ENTRY_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT
NULL,LAST_NAME TEXT,FIRST_NAME TEXT,CONTACT_NUM_1 TEXT NOT
NULL,CONTACT_NUM_1_TYPE CHAR NOT NULL,CONTACT_NUM_1_DEFAULT CHAR NOT
NULL,CONTACT_NUM_1_OWN CHAR NOT NULL,CONTACT_NUM_2 TEXT,CONTACT_NUM_2_TYPE
CHAR,CONTACT_NUM_2_DEFAULT CHAR,CONTACT_NUM_2_OWN CHAR,ASSOCIATED_MELODY
INTEGER,LINE_ID INTEGER );

CREATE TABLE LineSettingsList ( ENTRY_ID INTEGER PRIMARY KEY AUTOINCREMENT
NOT NULL,LINE_NAME TEXT,LINE_ID INTEGER NOT NULL,ATTACHED_HANDSETS INTEGER
NOT NULL,DIALING_PREFIX TEXT,FP_MELODY INTEGER,FP_VOLUME
INTEGER,BLOCKED_NUMBER TEXT,MULTIPLE_CALLS_MODE CHAR NOT NULL,INTRUSION_CALL
CHAR,PERMANENT_CLIR CHAR,PERMANENT_CLIR_ACTIVATION_CODE
TEXT,PERMANENT_CLIR_DEACTIVATION_CODE TEXT,CALL_FWD_UNCOND
CHAR,CALL_FWD_UNCOND_ACTIVATION_CODE TEXT,CALL_FWD_UNCOND_DEACTIVATION_CODE
TEXT,CALL_FWD_UNCOND_TARGET_NUMBER TEXT,CALL_FWD_NO_ANSWER
CHAR,CALL_FWD_NO_ANSWER_ACTIVATION_CODE
TEXT,CALL_FWD_NO_ANSWER_DEACTIVATION_CODE
TEXT,CALL_FWD_NO_ANSWER_TARGET_NUMBER TEXT,CALL_FWD_NO_ANSWER_TIMEOUT
CHAR,CALL_FWD_BUSY CHAR,CALL_FWD_BUSY_ACTIVATION_CODE
TEXT,CALL_FWD_BUSY_DEACTIVATION_CODE TEXT,CALL_FWD_BUSY_TARGET_NUMBER TEXT
);

SELECT
CallsList.NUMBER,ContactList.LAST_NAME,CallsList.DATE_AND_TIME,CallsList.READ_STATUS,LineSettingsList.LINE_NAME,CallsList.LINE_ID,CallsList.NUM_OF_CALLS
FROM CallsList WHERE NUMBER<='31'AND CALL_TYPE=0 COLLATE NOCASE LEFT JOIN
ContactList ON CallsList.NUMBER=ContactList.CONTACT_NUM_1 LEFT JOIN
LineSettingsList ON CallsList.LINE_ID=LineSettingsList.LINE_ID ORDER BY
NUMBER COLLATE NOCASE DESC;


Error: near "LEFT": syntax error

I am aware of sql syntaxes and not an expert DBA. please let me where I am
going wrong in framing the syntax.


Thanks
Suresh
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] left join using where clause instead of using on clause

[Jay A. Kreibich]

On Mon, Oct 05, 2009 at 02:02:51PM +0200, Tim Lind scratched on the wall:
> Hi
> 
> I have a query that is using a left join, with a where clause, and the  
> results I expect are not returned because the one table doesn't have a  
> related record.
> If I put the constraint in the on clause of the query instead, the  
> expected results are returned with the null record of the related table.
> 
> Is this standard behaviour of SQL or specific to SQLite?

  It sounds to me like you're mixing syntax.

  If use the JOIN syntax, the condition must be part of the JOIN/FROM
  clause:

 SELECT * FROM t1 LEFT JOIN t2 ON t1.t1_id = t2.t2_id WHERE ...

  If you use the WHERE condition, there is no "JOIN":

 SELECT * FROM t1, t2 WHERE t1.t1_id = t2.t2_id 

  Which also means there is no way to make this a "LEFT" join without
  explicitly dealing with the IS NULL case.  I'm fairly sure SQLite
  doesn't have a special syntax like Oracle and other old-school
  pre-JOIN-syntax databases (or, if it does, it is well hidden in the
  docs).

  If you're trying something like this:

 SELECT * FROM t1 LEFT JOIN t2 WHERE t1.i = t2.i

  That's not going to do what you think it is going to do.  You're
  asking for an unconditional LEFT JOIN, then you're putting a
  condition on the result of that join.  Thanks to 3VL, that condition
  will get rid of any rows with a NULL.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] left join using where clause instead of using on clause

[Simon Slavin]

On 5 Oct 2009, at 1:02pm, Tim Lind wrote:

> I have a query that is using a left join, with a where clause, and the
> results I expect are not returned because the one table doesn't have a
> related record.
> If I put the constraint in the on clause of the query instead, the
> expected results are returned with the null record of the related  
> table.
>
> Is this standard behaviour of SQL or specific to SQLite?

Standard SQL, and things you do need to consider when deciding the  
phrasing of your SELECT command.  Which kind of JOIN you use makes a  
difference.  Which of the tables you use in your WHERE clause makes a  
difference.  Check out how a RIGHT JOIN works, and watch your clauses.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] left join using where clause instead of using on clause

[Igor Tandetnik]

Tim Lind wrote:
> I have a query that is using a left join, with a where clause, and the
> results I expect are not returned because the one table doesn't have a
> related record.
> If I put the constraint in the on clause of the query instead, the
> expected results are returned with the null record of the related
> table.
>
> Is this standard behaviour of SQL or specific to SQLite?

I believe this is standard behavior. Imagine you have

select *
from table1 left join table2 on (condition1)
where condition2;

Condition1 is used to build the source recordset. For every pair of rows 
from table1 and table2 for which condition1 holds, a corresponding 
"concatenated" row is added to that recordset. If for some row in table1 
there is no row in table2 that satisfies the condition, a row is added 
to the recordset with all table2 fields set to NULL.

Condition2 is used to filter the source recordset. Only rows matching 
this condition are returned by the statement. Note that some of the 
source rows have lots of NULLs, and that most comparisons involving 
NULLs are false. That's why, unless you are careful, the condition in 
WHERE clause that mentions table2 fields is likely to filter out all 
table1-only records, and render left join pointless.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] left join using where clause instead of using on clause

[Pavel Ivanov]

> That said, constraints go in the WHERE clause. JOIN conditions go in
> the JOIN clause.

Not necessarily. My personal thinking was that it doesn't matter where
you put your join conditions - in WHERE clause or in JOIN clause. And
I've always put these condition into WHERE clause because it was
easier for me to read such queries then. Of course I had to make some
workarounds for such cases as OP's (I've written things like "column
is null or column = something") and for example Oracle even has
special syntax of WHERE clause for such cases.
But if described behavior is standard for SQL (which sounds reasonable
for me although I don't know it for sure) then it can make a lot of
queries easier.

Pavel

On Mon, Oct 5, 2009 at 8:18 AM, P Kishor  wrote:
> On Mon, Oct 5, 2009 at 7:02 AM, Tim Lind  wrote:
>> Hi
>>
>> I have a query that is using a left join, with a where clause, and the
>> results I expect are not returned because the one table doesn't have a
>> related record.
>> If I put the constraint in the on clause of the query instead, the
>> expected results are returned with the null record of the related table.
>>
>> Is this standard behaviour of SQL or specific to SQLite?
>
> what is the schema?
> what is the query?
> what is the result you expect?
> what is the result you actually get?
> what changes do you make to your query to get what you want?
> how can we read your mind?
>
> That said, constraints go in the WHERE clause. JOIN conditions go in
> the JOIN clause.
>
>
> --
> Puneet Kishor
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] left join using where clause instead of using on clause

[P Kishor]

On Mon, Oct 5, 2009 at 7:02 AM, Tim Lind  wrote:
> Hi
>
> I have a query that is using a left join, with a where clause, and the
> results I expect are not returned because the one table doesn't have a
> related record.
> If I put the constraint in the on clause of the query instead, the
> expected results are returned with the null record of the related table.
>
> Is this standard behaviour of SQL or specific to SQLite?

what is the schema?
what is the query?
what is the result you expect?
what is the result you actually get?
what changes do you make to your query to get what you want?
how can we read your mind?

That said, constraints go in the WHERE clause. JOIN conditions go in
the JOIN clause.


-- 
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] left join using where clause instead of using on clause

[Tim Lind]

Hi

I have a query that is using a left join, with a where clause, and the  
results I expect are not returned because the one table doesn't have a  
related record.
If I put the constraint in the on clause of the query instead, the  
expected results are returned with the null record of the related table.

Is this standard behaviour of SQL or specific to SQLite?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] left join does not return data if right side table is empty

[D. Richard Hipp]

On Feb 3, 2009, at 12:16 PM, Nathan Biggs wrote:

> I have a question about version 3.6.10.
>
> I downloaded the amalgamation source code and built a static  
> library.  I
> have included the static library in my Windows console application.  I
> have noticed something very strange though.  to execute my statement I
> use the prepare and then step not execute.  If I run the following  
> sql,
> I get no return data.
>
> select a.column1, b.column2
> from aTable a
> left join bTable b
> on a.id = b.aTable_id
>
> If I run the same sql statement through the command-line program
> downloaded from the site for 3.6.10, it returns the data with  
> b.column2
> being null.  Which is what I expected.

The command-line program uses exactly the same interfaces you are  
using.  Are you sure that szSql is correct?  Why are you not checking  
the return code from sqlite3_perpare()?

>
>
> Below is my code
>
>// open database
>rc = sqlite3_open(szFile, );
>if( rc ){
>fprintf(stderr, "Can't open database: %s\n",  
> sqlite3_errmsg(db));
>sqlite3_close(db);
>exit(1);
>}
>
>sqlite3_stmt *ppStmt = 0;;
>const char *zLeftover;
>
>//compile sql statement
>rc = sqlite3_prepare(db, szSQL, -1, , );
>
>//step through recordset
>int i = 0;
>int nCol = 0;
>char rowstr[256] = "";
>const char *temp;
>
>//get column count
>nCol = sqlite3_column_count(ppStmt);
>
>//for each row
>while(sqlite3_step(ppStmt)==SQLITE_ROW){
>
>//for each column
>for(int j=0;jtemp = (char *)sqlite3_column_text(ppStmt, j);
>strcat(rowstr, temp);
>strcat(rowstr, "|");
>}
>
>cout << rowstr << endl;
>strcpy(rowstr, "");
>i++;  //increment row counter
>}
>
>//delete recordset
>rc = sqlite3_finalize(ppStmt);
>
>// close database
>sqlite3_close(db);
>
>cout << "finished" << endl;
>
>
> -- 
> *Nathan Biggs*
> Computerway Food Systems
> System Controls Manager
>
> (336) 841-7289 /Work/
> (336) 841-2594 /Fax/
> nbi...@mycfs.com
>
> PO Box 5623 (27262)
> 635 Southwest Street
> High Point, NC 27260
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] left join does not return data if right side table is empty

[Nathan Biggs]

I have a question about version 3.6.10.

I downloaded the amalgamation source code and built a static library.  I 
have included the static library in my Windows console application.  I 
have noticed something very strange though.  to execute my statement I 
use the prepare and then step not execute.  If I run the following sql, 
I get no return data.

select a.column1, b.column2
from aTable a
left join bTable b
on a.id = b.aTable_id

If I run the same sql statement through the command-line program 
downloaded from the site for 3.6.10, it returns the data with b.column2 
being null.  Which is what I expected. 

Below is my code

// open database
rc = sqlite3_open(szFile, );
if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
   
sqlite3_stmt *ppStmt = 0;;
const char *zLeftover;

//compile sql statement
rc = sqlite3_prepare(db, szSQL, -1, , );

//step through recordset
int i = 0;
int nCol = 0;
char rowstr[256] = "";
const char *temp;
   
//get column count
nCol = sqlite3_column_count(ppStmt);
   
//for each row
while(sqlite3_step(ppStmt)==SQLITE_ROW){   

//for each column
for(int j=0;j

Re: [sqlite] Left Join

[Tommy Anderson]

It's working, Thank you!


Igor Tandetnik wrote:
> 
> "Tommy Anderson" <[EMAIL PROTECTED]>
> wrote in message news:[EMAIL PROTECTED]
>> Select * From ExcelMapValue
>>
>> OUTER JOIN   (SELECT ProjectId, InputId, DataValue, Formula
>> FROM ProjectData
>> WHERE  (ProjectId = @Id))  derivedtbl_1
>>
>> On ExcelMapValue.InputId =  derivedtbl_1.InputId
> 
> Try this:
> 
> Select * From ExcelMapValue e left join ProjectData p
> on (e.InputId = p.InputId and p.ProjectId = @Id);
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Left-Join-tp20725943p20735463.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Left Join

[Igor Tandetnik]

"Tommy Anderson" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> Select * From ExcelMapValue
>
> OUTER JOIN   (SELECT ProjectId, InputId, DataValue, Formula
> FROM ProjectData
> WHERE  (ProjectId = @Id))  derivedtbl_1
>
> On ExcelMapValue.InputId =  derivedtbl_1.InputId

Try this:

Select * From ExcelMapValue e left join ProjectData p
on (e.InputId = p.InputId and p.ProjectId = @Id);

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Left Join

[Tommy Anderson]

Hi, 

I have a left join problem with SQLite. 

this Select SELECT ProjectId, InputId, DataValue, Formula
FROM ProjectData
WHERE  (ProjectId = @Id) contains almost 15 000 rows.

this Table ExcelMapValue contains almost 5 000 rows. and contains these
columns InputId, sheetCode, rowIndex, ColumnIndex

When I run this, it takes at least 2 minutes or it says out of memory 

Select * From ExcelMapValue

OUTER JOIN   (SELECT ProjectId, InputId, DataValue, Formula
FROM ProjectData
WHERE  (ProjectId = @Id))  derivedtbl_1
  
On ExcelMapValue.InputId =  derivedtbl_1.InputId  


Anyone can help me ? I don't understand why it takes forever to run. In Sql
server it runs in less than a second.
-- 
View this message in context: 
http://www.nabble.com/Left-Join-tp20725943p20725943.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Left Join help

[Andy Smith]

Dennis,

Thanks for the reply.  The original schema was causing us way to many
problems.  We ended up going with the second schema and the last query I
posted we managed to get it down to .058 secs instead of 2.9 secs.  I do
appreciate all the suggestions and assistance.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote
Sent: Wednesday, April 09, 2008 6:27 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Left Join help

Andy Smith wrote:
> 
> With all the changes that got me down to 4.8 seconds.  Better.  

Can you post the exact query that ran in 4.8 seconds? Did you set 
case_sensitive_like on before you ran the query?

> We are
> also trying a schema in which we break up the container so it's a bit
> smaller.  Now my other 54 queries run sub .5 secs  except 1.  Which is
a
> variant of the one I have asked for help on.  Here is the new version
on
> a schema based on the container table being split apart.
> 
> explain query plan SELECT a.id, a.title, a.dateAdded, a.url, a.path,
> a.containerID, a.mimeType, a.width, a.height, a.genreID,
a.thumbnailID,
> a.releaseYearID, a.artistID, (SELECT w.title AS containerName FROM
> allAlbums AS w WHERE w.id=a.containerID), (SELECT x.title AS genreName
> FROM allgenres AS x WHERE x.id=a.genreID), (SELECT y.title AS
> releaseYearName FROM allreleaseYears AS y WHERE y.id=a.releaseYearID),
> (SELECT z.title AS artistName FROM allartists AS z WHERE
> z.id=a.artistID) FROM mediaImage AS a INNER JOIN mediaAudio AS b ON
> b.containerID=a.containerID WHERE b.title LIKE 'Opus%' ESCAPE '\'
ORDER
> BY a.title, a.id LIMIT 0,9;
> 
> 0|0|TABLE mediaImage AS a WITH INDEX mediaImage_title ORDER BY
> 1|1|TABLE mediaAudio AS b WITH INDEX mediaAudio_containerID
> 0|0|TABLE allAlbums AS w USING PRIMARY KEY
> 0|0|TABLE allgenres AS x USING PRIMARY KEY
> 0|0|TABLE allreleaseYears AS y USING PRIMARY KEY
> 0|0|TABLE allartists AS z USING PRIMARY KEY
> 
> This runs in about 2.9 seconds.  Still way slower than needed. I am
> really debating whether this can be sped up to meet the < 1 second
goal.
> 

This query has the same issues as the first one. The escape clause on 
the like invalidates the use of an index. The order of the tables a and 
b is sub optimal. The first scan is a full table scan using the title 
index for the ordering only. It will be faster swap the order the tables

are scanned to eliminate all the records that don't have the correct 
title first.

Instead of this:

FROM mediaImage AS a
INNER JOIN mediaAudio AS b ON b.containerID=a.containerID
WHERE b.title LIKE 'Opus%' ESCAPE '\'
ORDER BY a.title, a.id
LIMIT 0,9;

try this:

FROM mediaAudio AS b
JOIN mediaImage AS a ON b.containerID=a.containerID
WHERE b.title LIKE 'Opus%'
ORDER BY a.title, a.id
LIMIT 0,9;

Make sure you turn on case_sensitive_like before executing the query. 
The title index will be used to select the b table records based on the 
like condition, then the a table records will be matched by the 
containerID.

This query (and the first one) will have to sort the result records 
instead of using the title index for the ordering, but there will 
hopefully be few records to sort, so it should be fast. How many records

does this query return?

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



This message is confidential to Prodea Systems, Inc unless otherwise indicated 
or apparent from its nature. This message is directed to the intended recipient 
only, who may be readily determined by the sender of this message and its 
contents. If the reader of this message is not the intended recipient, or an 
employee or agent responsible for delivering this message to the intended 
recipient:(a)any dissemination or copying of this message is strictly 
prohibited; and(b)immediately notify the sender by return message and destroy 
any copies of this message in any form(electronic, paper or otherwise) that you 
have.The delivery of this message and its information is neither intended to be 
nor constitutes a disclosure or waiver of any trade secrets, intellectual 
property, attorney work product, or attorney-client communications. The 
authority of the individual sending this message to legally bind Prodea Systems 
 
is neither apparent nor implied,and must be independently verified.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Left Join help

[Dennis Cote]

Andy Smith wrote:
> 
> With all the changes that got me down to 4.8 seconds.  Better.  

Can you post the exact query that ran in 4.8 seconds? Did you set 
case_sensitive_like on before you ran the query?

> We are
> also trying a schema in which we break up the container so it's a bit
> smaller.  Now my other 54 queries run sub .5 secs  except 1.  Which is a
> variant of the one I have asked for help on.  Here is the new version on
> a schema based on the container table being split apart.
> 
> explain query plan SELECT a.id, a.title, a.dateAdded, a.url, a.path,
> a.containerID, a.mimeType, a.width, a.height, a.genreID, a.thumbnailID,
> a.releaseYearID, a.artistID, (SELECT w.title AS containerName FROM
> allAlbums AS w WHERE w.id=a.containerID), (SELECT x.title AS genreName
> FROM allgenres AS x WHERE x.id=a.genreID), (SELECT y.title AS
> releaseYearName FROM allreleaseYears AS y WHERE y.id=a.releaseYearID),
> (SELECT z.title AS artistName FROM allartists AS z WHERE
> z.id=a.artistID) FROM mediaImage AS a INNER JOIN mediaAudio AS b ON
> b.containerID=a.containerID WHERE b.title LIKE 'Opus%' ESCAPE '\' ORDER
> BY a.title, a.id LIMIT 0,9;
> 
> 0|0|TABLE mediaImage AS a WITH INDEX mediaImage_title ORDER BY
> 1|1|TABLE mediaAudio AS b WITH INDEX mediaAudio_containerID
> 0|0|TABLE allAlbums AS w USING PRIMARY KEY
> 0|0|TABLE allgenres AS x USING PRIMARY KEY
> 0|0|TABLE allreleaseYears AS y USING PRIMARY KEY
> 0|0|TABLE allartists AS z USING PRIMARY KEY
> 
> This runs in about 2.9 seconds.  Still way slower than needed. I am
> really debating whether this can be sped up to meet the < 1 second goal.
> 

This query has the same issues as the first one. The escape clause on 
the like invalidates the use of an index. The order of the tables a and 
b is sub optimal. The first scan is a full table scan using the title 
index for the ordering only. It will be faster swap the order the tables 
are scanned to eliminate all the records that don't have the correct 
title first.

Instead of this:

FROM mediaImage AS a
INNER JOIN mediaAudio AS b ON b.containerID=a.containerID
WHERE b.title LIKE 'Opus%' ESCAPE '\'
ORDER BY a.title, a.id
LIMIT 0,9;

try this:

FROM mediaAudio AS b
JOIN mediaImage AS a ON b.containerID=a.containerID
WHERE b.title LIKE 'Opus%'
ORDER BY a.title, a.id
LIMIT 0,9;

Make sure you turn on case_sensitive_like before executing the query. 
The title index will be used to select the b table records based on the 
like condition, then the a table records will be matched by the 
containerID.

This query (and the first one) will have to sort the result records 
instead of using the title index for the ordering, but there will 
hopefully be few records to sort, so it should be fast. How many records 
does this query return?

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Left Join help

[Andy Smith]

LL
);
CREATE TABLE "usermediaVideo" (
-- fundamental information
"mediaVideoID"   INTEGER  NOT NULL REFERENCES "mediaVideo(id)",
"userID" INTEGER  NOT NULL REFERENCES "user(userid)",

-- user-specific media item information
"notes"  TEXT DEFAULT NULL,-- user-supplied
"comments"   TEXT DEFAULT NULL,
"dateLastPlayed" TIMESTAMPDEFAULT NULL,
"lastPlayedPosition" BIGINT UNSIGNED
  DEFAULT NULL,-- in
hundredths-of-a-second
"playCount"  INTEGER UNSIGNED
  DEFAULT NULL,
"dateLastSkipped" TIMESTAMPDEFAULT NULL,
"skipCount"  INTEGER UNSIGNED
  DEFAULT NULL,
"rating" INTEGER  DEFAULT NULL, -- range: 1..5

-- non-NULL only for audio/video media items
"bookmark"   BIGINT UNSIGNED
  DEFAULT NULL,-- in
hundredths-of-a-second
"useBookmark"tinyint  DEFAULT NULL,

-- housekeeping...
"dateModified"   TIMESTAMPDEFAULT CURRENT_TIMESTAMP
  NOT NULL
);
CREATE INDEX allAlbums_dateAdded ON allAlbums(dateAdded);
CREATE INDEX allAlbums_title ON allAlbums(title);
CREATE INDEX allArtists_dateAddedON allArtists(dateAdded);
CREATE INDEX allArtists_titleON allArtists(title);
CREATE INDEX allGenres_dateAdded ON allGenres(dateAdded);
CREATE INDEX allGenres_title ON allGenres(title);
CREATE INDEX allPlaylists_title  ON allPlaylists(title);
CREATE INDEX allPlaylistss_dateAdded ON allPlaylists(dateAdded);
CREATE INDEX allReleaseYears_dateAdded ON allReleaseYears(dateAdded);
CREATE INDEX allReleaseYears_titlON allReleaseYears(title);
CREATE INDEX allTags_dateAdded   ON allTags(dateAdded);
CREATE INDEX allTags_title   ON allTags(title);
CREATE UNIQUE INDEX "auxid"  ON containeraux(title,type);
CREATE INDEX container_avgRating ON container(avgRating);
CREATE INDEX container_dateAdded ON container(dateAdded);
CREATE INDEX container_title ON container(title);
CREATE INDEX container_type  ON container(type);
CREATE INDEX extend_contact  ON extend(contact);
CREATE INDEX extend_titleON extend(title);
CREATE INDEX extension_containerID   ON extension(containerID);
CREATE INDEX extension_extendID  ON extension(extendID);
CREATE INDEX extension_keyvalue  ON extension(keyvalue);
CREATE INDEX extension_mediaitemID   ON extension(mediaitemID);
CREATE INDEX mediaAudio_artistID    ON mediaAudio(artistID);
CREATE INDEX mediaAudio_avgRating   ON mediaAudio(avgRating);
CREATE INDEX mediaAudio_containerID ON mediaAudio(containerID);
CREATE INDEX mediaAudio_dateAdded   ON mediaAudio(dateAdded);
CREATE INDEX mediaAudio_genreID ON mediaAudio(genreID);
CREATE INDEX mediaAudio_releaseYearID   ON mediaAudio(releaseYearID);
CREATE INDEX mediaAudio_thumb320ID  ON mediaAudio(thumb320ID);
CREATE INDEX mediaAudio_thumbnailID ON mediaAudio(thumbnailID);
CREATE INDEX mediaAudio_title   ON mediaAudio(title);
CREATE INDEX mediaImage_artistIDON mediaImage(artistID);
CREATE INDEX mediaImage_avgRating   ON mediaImage(avgRating);
CREATE INDEX mediaImage_containerID ON mediaImage(containerID);
CREATE INDEX mediaImage_dateAdded   ON mediaImage(dateAdded);
CREATE INDEX mediaImage_genreID ON mediaImage(genreID);
CREATE INDEX mediaImage_releaseYearID   ON mediaImage(releaseYearID);
CREATE INDEX mediaImage_thumb320ID  ON mediaImage(thumb320ID);
CREATE INDEX mediaImage_thumbnailID ON mediaImage(thumbnailID);
CREATE INDEX mediaImage_title   ON mediaImage(title);
CREATE INDEX mediaVideo_avgRating   ON mediaVideo(avgRating);
CREATE INDEX mediaVideo_containerID ON mediaVideo(containerID);
CREATE INDEX mediaVideo_dateAdded   ON mediaVideo(dateAdded);
CREATE INDEX mediaVideo_genreID ON mediaVideo(genreID);
CREATE INDEX mediaVideo_mpaaRating  ON mediaVideo(mpaaRating);
CREATE INDEX mediaVideo_releaseYearID   ON mediaVideo(releaseYearID);
CREATE INDEX mediaVideo_thumb320ID  ON mediaVideo(thumb320ID);
CREATE INDEX mediaVideo_thumbnailID ON mediaVideo(thumbnailID);
CREATE INDEX mediaVideo_title   ON mediaVideo(title);
CREATE INDEX tag_containerID ON tag(containerID);
CREATE INDEX tag_mediaitemID ON tag(mediaitemID);
CREATE INDEX tag_parentIDON tag(parentID);
CREATE UNIQUE INDEX "ucid"   ON
usercontainer(containerID,userID);
CREATE INDEX usercontainer_containerID ON usercontainer(containerID);
CREATE INDEX usercontainer_useridON usercontainer(userid);
CREATE UNIQUE INDEX "usermediaAudio_id" ON
usermediaAudio(mediaAudioID,userID);
CREATE IN

Re: [sqlite] Left Join help

[Dennis Cote]

Andy Smith wrote:
> I have to get this down to < 1 sec. I have 50 queries that are all
> formatted similar.  We have created another schema where the container
> is split into smaller tables which might help in speeding this up.  From
> what I see the Left joins are killing the speed.
> 

I don't think that is your problem. You are doing a full table scan of a 
large table. The last four left joins are required no matter what, but 
they will be quick since they use the container primary key to get the 
required record directly.

You want to use the most specific test you can to eliminate as many 
records as possible early on. The problem is that your LIKE test can't 
use an index as given. See http://www.sqlite.org/optoverview.html for 
details. You need to make a few changes to your query to get it to use 
the index on the mediaitem title for the like test.

First remove the unnecessary escape clause from the like clause. The set 
the case_sensitive_like pragma on. Next drop the index mediaitem_type so 
that sqlite will use the title index instead. Finally rearrange the 
order of the a and b tables so the like test is applied first using the 
index.

 sqlite> drop index mediaitem_type;
 sqlite> pragma case_sensitive_like = 1;
 sqlite> explain query plan
...> SELECT
...> a.id,
...> a.title,
...> a.type,
...> a.dateAdded,
...> a.url,
...> a.path,
...> a.containerID,
...> a.mimeType,
...> a.width,
...> a.height,
...> a.genreID,
...> a.thumbnailID,
...> a.releaseYearID,
...> a.artistID,
...> w.title AS containerName,
...> x.title AS genreName,
...> y.title AS releaseYearName,
...> z.title AS artistName
...> FROM mediaitem AS b
...> LEFT JOIN mediaitem AS a ON b.containerID=a.containerID
...> LEFT JOIN container AS w ON w.id=a.containerID
...> LEFT JOIN container AS x ON x.id=a.genreID
...> LEFT JOIN container AS y ON y.id=a.releaseYearID
...> LEFT JOIN container AS z ON z.id=a.artistID
...> WHERE b.title LIKE 'Opus%' --remove the escape clause
...> AND b.type=0
...> AND a.type=1
...> ORDER BY a.title, a.id
...> LIMIT 0,9;
 0|0|TABLE mediaitem AS b WITH INDEX mediaitem_title
 1|1|TABLE mediaitem AS a WITH INDEX mediaitem_containerID
 2|2|TABLE container AS w USING PRIMARY KEY
 3|3|TABLE container AS x USING PRIMARY KEY
 4|4|TABLE container AS y USING PRIMARY KEY
 5|5|TABLE container AS z USING PRIMARY KEY

With these changes you will use the title index to quickly reduce the 
table to only those records that match the title. For each of these 
records it will check the type, then join the records with the same 
containerID using that index with another check for the required type. 
Finally it will select the indicated records from the container table 
based on the rows selected.

HTH
Dennis Cote
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Left Join help

[Ken]

LT NULL,
"skipCount"  INTEGER UNSIGNED
  DEFAULT NULL,
"rating" INTEGER  DEFAULT NULL, -- range: 1..5

-- non-NULL only for audio/video media items
"bookmark"   BIGINT UNSIGNED
  DEFAULT NULL,-- in
hundredths-of-a-second
"useBookmark"tinyint  DEFAULT NULL,

-- housekeeping...
"dateModified"   TIMESTAMPDEFAULT CURRENT_TIMESTAMP
  NOT NULL
);
CREATE UNIQUE INDEX "auxid"  ON containeraux(title,type);
CREATE INDEX container_avgRating ON container(avgRating);
CREATE INDEX container_dateAdded ON container(dateAdded);
CREATE INDEX container_id ON container(id);
CREATE INDEX container_title ON container(title);
CREATE INDEX container_type  ON container(type);
CREATE INDEX extend_contact  ON extend(contact);
CREATE INDEX extend_titleON extend(title);
CREATE INDEX extension_containerID   ON extension(containerID);
CREATE INDEX extension_extendID      ON extension(extendID);
CREATE INDEX extension_keyvalue  ON extension(keyvalue);
CREATE INDEX extension_mediaitemID   ON extension(mediaitemID);
CREATE INDEX mediaitem_artistID  ON mediaitem(artistID);
CREATE INDEX mediaitem_avgRating ON mediaitem(avgRating);
CREATE INDEX mediaitem_containerID   ON mediaitem(containerID);
CREATE INDEX mediaitem_dateAdded ON mediaitem(dateAdded);
CREATE INDEX mediaitem_genreID   ON mediaitem(genreID);
CREATE INDEX mediaitem_mpaaRatingON mediaitem(mpaaRating);
CREATE INDEX mediaitem_releaseYearID ON mediaitem(releaseYearID);
CREATE INDEX mediaitem_thumb320IDON mediaitem(thumb320ID);
CREATE INDEX mediaitem_thumbnailID   ON mediaitem(thumbnailID);
CREATE INDEX mediaitem_title ON mediaitem(title);
CREATE INDEX mediaitem_type  ON mediaitem(type);
CREATE INDEX tag_containerID ON tag(containerID);
CREATE INDEX tag_mediaitemID ON tag(mediaitemID);
CREATE INDEX tag_parentIDON tag(parentID);
CREATE UNIQUE INDEX "ucid"   ON
usercontainer(containerID,userID);
CREATE UNIQUE INDEX "ulid"   ON
usermediaitem(mediaitemID,userID);
CREATE INDEX usercontainer_containerID ON usercontainer(containerID);
CREATE INDEX usercontainer_useridON usercontainer(userid);
CREATE INDEX usermediaitem_mediaitemID ON usermediaitem(mediaitemID);
CREATE INDEX usermediaitem_useridON usermediaitem(userid);

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote
Sent: Wednesday, April 09, 2008 11:25 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Left Join help

Andy Smith wrote:
> I have quiet a few queries similar to this doing multiple Left Joins
and
> they  run extremely slow > 6 secs. Is there a better way to be writing
> the below query for sqlite.
> 

Andy,

You didn't show your table and index definitions, so it's hard to be 
sure about what would be best.

The following trace shows what I think your tables should look like, and

a couple of indexes that will help for a slightly rearranged version of 
your query. The query plan uses the indexes to locate the subset of the 
records with type=0, then checks their title, next it uses the other 
index to find the matching records and checks that their type=1. These 
records should lead directly to the required container records using the

primary key on that table. I would suspect this is reasonably fast. Let 
me know if it helps.

HTH
Dennis Cote

SQLite version 3.5.7
Enter ".help" for instructions
sqlite>
sqlite> create table container (
...> id integer primary key,
...> title text
...> );
sqlite>
sqlite> create table mediaitem (
...> id integer primary key,
...> title text,
...> type integer,
...> containerID integer references container,
...> genreID integer references container,
...> thumbnailID integer references container,
...> releaseYearID integer references container,
...> artistID integer references container,
...> dateAdded,
...> url,
...> path,
...> mimeType,
...> width,
...> height
...> );
sqlite>
sqlite> create index media_type on mediaitem(type);
sqlite> create index media_container on mediaitem(containerID);
sqlite>
sqlite> explain query plan
...> SELECT
...> a.id,
...> a.title,
...> a.type,
...> a.dateAdded,
...> a.url,
...> a.path,
...> a.containerID,
...> a.mimeType,
...> a.width,
...> a.height,
...> a.genreID,
...> a.thumbnailID,
...> a.releaseYearID,
...> a.artistID,
.

Re: [sqlite] Left Join help

[Andy Smith]

ON container(type);
CREATE INDEX extend_contact  ON extend(contact);
CREATE INDEX extend_titleON extend(title);
CREATE INDEX extension_containerID   ON extension(containerID);
CREATE INDEX extension_extendID      ON extension(extendID);
CREATE INDEX extension_keyvalue  ON extension(keyvalue);
CREATE INDEX extension_mediaitemID   ON extension(mediaitemID);
CREATE INDEX mediaitem_artistID  ON mediaitem(artistID);
CREATE INDEX mediaitem_avgRating ON mediaitem(avgRating);
CREATE INDEX mediaitem_containerID   ON mediaitem(containerID);
CREATE INDEX mediaitem_dateAdded ON mediaitem(dateAdded);
CREATE INDEX mediaitem_genreID   ON mediaitem(genreID);
CREATE INDEX mediaitem_mpaaRatingON mediaitem(mpaaRating);
CREATE INDEX mediaitem_releaseYearID ON mediaitem(releaseYearID);
CREATE INDEX mediaitem_thumb320IDON mediaitem(thumb320ID);
CREATE INDEX mediaitem_thumbnailID   ON mediaitem(thumbnailID);
CREATE INDEX mediaitem_title ON mediaitem(title);
CREATE INDEX mediaitem_type  ON mediaitem(type);
CREATE INDEX tag_containerID ON tag(containerID);
CREATE INDEX tag_mediaitemID ON tag(mediaitemID);
CREATE INDEX tag_parentIDON tag(parentID);
CREATE UNIQUE INDEX "ucid"   ON
usercontainer(containerID,userID);
CREATE UNIQUE INDEX "ulid"   ON
usermediaitem(mediaitemID,userID);
CREATE INDEX usercontainer_containerID ON usercontainer(containerID);
CREATE INDEX usercontainer_useridON usercontainer(userid);
CREATE INDEX usermediaitem_mediaitemID ON usermediaitem(mediaitemID);
CREATE INDEX usermediaitem_useridON usermediaitem(userid);

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dennis Cote
Sent: Wednesday, April 09, 2008 11:25 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Left Join help

Andy Smith wrote:
> I have quiet a few queries similar to this doing multiple Left Joins
and
> they  run extremely slow > 6 secs. Is there a better way to be writing
> the below query for sqlite.
> 

Andy,

You didn't show your table and index definitions, so it's hard to be 
sure about what would be best.

The following trace shows what I think your tables should look like, and

a couple of indexes that will help for a slightly rearranged version of 
your query. The query plan uses the indexes to locate the subset of the 
records with type=0, then checks their title, next it uses the other 
index to find the matching records and checks that their type=1. These 
records should lead directly to the required container records using the

primary key on that table. I would suspect this is reasonably fast. Let 
me know if it helps.

HTH
Dennis Cote

SQLite version 3.5.7
Enter ".help" for instructions
sqlite>
sqlite> create table container (
...> id integer primary key,
...> title text
...> );
sqlite>
sqlite> create table mediaitem (
...> id integer primary key,
...> title text,
...> type integer,
...> containerID integer references container,
...> genreID integer references container,
...> thumbnailID integer references container,
...> releaseYearID integer references container,
...> artistID integer references container,
...> dateAdded,
...> url,
...> path,
...> mimeType,
...> width,
...> height
...> );
sqlite>
sqlite> create index media_type on mediaitem(type);
sqlite> create index media_container on mediaitem(containerID);
sqlite>
sqlite> explain query plan
...> SELECT
...> a.id,
...> a.title,
...> a.type,
...> a.dateAdded,
...> a.url,
...> a.path,
...> a.containerID,
...> a.mimeType,
...> a.width,
...> a.height,
...> a.genreID,
...> a.thumbnailID,
...> a.releaseYearID,
...> a.artistID,
...> w.title AS containerName,
...> x.title AS genreName,
...> y.title AS releaseYearName,
...> z.title AS artistName
...> FROM mediaitem AS b
...> LEFT JOIN mediaitem AS a ON b.containerID=a.containerID
...> LEFT JOIN container AS w ON w.id=a.containerID
...> LEFT JOIN container AS x ON x.id=a.genreID
...> LEFT JOIN container AS y ON y.id=a.releaseYearID
...> LEFT JOIN container AS z ON z.id=a.artistID
...> WHERE b.title LIKE 'Opus%' ESCAPE '\'
...> AND b.type=0
...> AND a.type=1
...> ORDER BY a.title, a.id
...> LIMIT 0,9;
0|0|TABLE mediaitem AS b WITH INDEX media_type
1|1|TABLE mediaitem AS a WITH INDEX media_container
2|2|TABLE container AS w USING PRIMARY KEY
3|3|TABLE container AS x USING PRIMARY KEY
4|4|TABLE container AS y USING PRIMARY KEY
5|5|TABLE container AS z USING PRIMARY KEY
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



This message is confidential to Prodea Systems, Inc unless otherwise indicated 
or apparent from its nature. This message is directed to the intended recipient 
only, who may be readily determined by the sender of this message and its 
contents. If the reader of this message is not the intended recipient, or an 
employee or agent responsible for delivering this message to the intended 
recipient:(a)any dissemination or copying of this message is strictly 
prohibited; and(b)immediately notify the sender by return message and destroy 
any copies of this message in any form(electronic, paper or otherwise) that you 
have.The delivery of this message and its information is neither intended to be 
nor constitutes a disclosure or waiver of any trade secrets, intellectual 
property, attorney work product, or attorney-client communications. The 
authority of the individual sending this message to legally bind Prodea Systems 
 
is neither apparent nor implied,and must be independently verified.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Left Join help

[Dennis Cote]

Andy Smith wrote:
> I have quiet a few queries similar to this doing multiple Left Joins and
> they  run extremely slow > 6 secs. Is there a better way to be writing
> the below query for sqlite.
> 

Andy,

You didn't show your table and index definitions, so it's hard to be 
sure about what would be best.

The following trace shows what I think your tables should look like, and 
a couple of indexes that will help for a slightly rearranged version of 
your query. The query plan uses the indexes to locate the subset of the 
records with type=0, then checks their title, next it uses the other 
index to find the matching records and checks that their type=1. These 
records should lead directly to the required container records using the 
primary key on that table. I would suspect this is reasonably fast. Let 
me know if it helps.

HTH
Dennis Cote

SQLite version 3.5.7
Enter ".help" for instructions
sqlite>
sqlite> create table container (
...> id integer primary key,
...> title text
...> );
sqlite>
sqlite> create table mediaitem (
...> id integer primary key,
...> title text,
...> type integer,
...> containerID integer references container,
...> genreID integer references container,
...> thumbnailID integer references container,
...> releaseYearID integer references container,
...> artistID integer references container,
...> dateAdded,
...> url,
...> path,
...> mimeType,
...> width,
...> height
...> );
sqlite>
sqlite> create index media_type on mediaitem(type);
sqlite> create index media_container on mediaitem(containerID);
sqlite>
sqlite> explain query plan
...> SELECT
...> a.id,
...> a.title,
...> a.type,
...> a.dateAdded,
...> a.url,
...> a.path,
...> a.containerID,
...> a.mimeType,
...> a.width,
...> a.height,
...> a.genreID,
...> a.thumbnailID,
...> a.releaseYearID,
...> a.artistID,
...> w.title AS containerName,
...> x.title AS genreName,
...> y.title AS releaseYearName,
...> z.title AS artistName
...> FROM mediaitem AS b
...> LEFT JOIN mediaitem AS a ON b.containerID=a.containerID
...> LEFT JOIN container AS w ON w.id=a.containerID
...> LEFT JOIN container AS x ON x.id=a.genreID
...> LEFT JOIN container AS y ON y.id=a.releaseYearID
...> LEFT JOIN container AS z ON z.id=a.artistID
...> WHERE b.title LIKE 'Opus%' ESCAPE '\'
...> AND b.type=0
...> AND a.type=1
...> ORDER BY a.title, a.id
...> LIMIT 0,9;
0|0|TABLE mediaitem AS b WITH INDEX media_type
1|1|TABLE mediaitem AS a WITH INDEX media_container
2|2|TABLE container AS w USING PRIMARY KEY
3|3|TABLE container AS x USING PRIMARY KEY
4|4|TABLE container AS y USING PRIMARY KEY
5|5|TABLE container AS z USING PRIMARY KEY
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Left Join help

[Igor Tandetnik]

Andy Smith
<[EMAIL PROTECTED]> wrote:
> I have quiet a few queries similar to this doing multiple Left Joins
> and
> they  run extremely slow > 6 secs. Is there a better way to be writing
> the below query for sqlite.
>
>
>
> SELECT a.id, a.title, a.type, a.dateAdded, a.url, a.path,
> a.containerID, a.mimeType, a.width, a.height, a.genreID,
> a.thumbnailID,
> a.releaseYearID, a.artistID, w.title AS containerName, x.title AS
> genreName, y.title AS releaseYearName, z.title AS artistName FROM
> mediaitem AS a LEFT JOIN mediaitem AS b ON b.containerID=a.containerID
> LEFT JOIN container AS w ON w.id=a.containerID LEFT JOIN container AS
> x
> ON x.id=a.genreID LEFT JOIN container AS y ON y.id=a.releaseYearID
> LEFT
> JOIN container AS z ON z.id=a.artistID WHERE a.type=1 AND b.type=0 AND
> b.title LIKE 'Opus%' ESCAPE '\' ORDER BY a.title, a.id LIMIT 0,9;

There's no point to use LEFT JOIN between a and b. Your WHERE clause 
discards all records where b fields are NULLs anyway. See if changing it 
to plain old JOIN helps.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Left Join help

[Andy Smith]

I have quiet a few queries similar to this doing multiple Left Joins and
they  run extremely slow > 6 secs. Is there a better way to be writing
the below query for sqlite.

 

SELECT a.id, a.title, a.type, a.dateAdded, a.url, a.path, a.containerID,
a.mimeType, a.width, a.height, a.genreID, a.thumbnailID,
a.releaseYearID, a.artistID, w.title AS containerName, x.title AS
genreName, y.title AS releaseYearName, z.title AS artistName FROM
mediaitem AS a LEFT JOIN mediaitem AS b ON b.containerID=a.containerID
LEFT JOIN container AS w ON w.id=a.containerID LEFT JOIN container AS x
ON x.id=a.genreID LEFT JOIN container AS y ON y.id=a.releaseYearID LEFT
JOIN container AS z ON z.id=a.artistID WHERE a.type=1 AND b.type=0 AND
b.title LIKE 'Opus%' ESCAPE '\' ORDER BY a.title, a.id LIMIT 0,9;

 

Thanks,

 

Andy

 




This message is confidential to Prodea Systems, Inc unless otherwise indicated 
or apparent from its nature. This message is directed to the intended recipient 
only, who may be readily determined by the sender of this message and its 
contents. If the reader of this message is not the intended recipient, or an 
employee or agent responsible for delivering this message to the intended 
recipient:(a)any dissemination or copying of this message is strictly 
prohibited; and(b)immediately notify the sender by return message and destroy 
any copies of this message in any form(electronic, paper or otherwise) that you 
have.The delivery of this message and its information is neither intended to be 
nor constitutes a disclosure or waiver of any trade secrets, intellectual 
property, attorney work product, or attorney-client communications. The 
authority of the individual sending this message to legally bind Prodea Systems 
 
is neither apparent nor implied,and must be independently verified.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] left join usage and performance

[Filip Jonckers]

First of all I must say that I enjoyed reading your presentation at
http://www.sqlite.org/php2004/page-001.html on the internals of SQLite and
the performance tips.
I could not find any information on the processing of LEFT JOIN queries
though...

As SQLite only does loop-joins, I wonder how I should best use LEFT JOINS.
Most of my queries use LEFT JOINS to get additional information on - in
this case - specific aircraft.

2 Tables "asterix" and "aircraft" with indexes:

CREATE INDEX a_modea ON asterix(modeA);
CREATE INDEX a_modes ON asterix(modeS);
CREATE INDEX b_modes ON aircraft(modeS);

the first table (A) contains approx. 4.000.000 records (several hundred
entries per aircraft)
while the second table (B) contains about 100.000 records (lookup table
with aircraft details)
total database size is approx. 3GB.  (there are 2 other tables in the db
similar in size to the first table)

the following query takes a long time:

select A.ModeA,A.modeS,A.acid,count(A.modeS) plots,B.actype,B.registration
from asterix A LEFT JOIN aircraft B
ON A.modeS = B.modeS
where A.ModeS is not NULL and A.modeA=1234
group by A.ModeS
having plots > 5


How does SQLite handle LEFT JOINs ?

Is there a better way to write such queries than using LEFT JOIN?

Is there a performance benefit in using a combined index like for example:
CREATE INDEX a_full ON asterix(modeA,modeS);


any tips regarding LEFT JOINs are much appreciated

regards,
Filip



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] LEFT JOIN doesn't work as expected

[Rolf Schaeuble]

Right. I'm sorry.
I should get rid of this CC'ing habit  ;-) 

Rolf

D. Richard Hipp wrote:
[EMAIL PROTECTED] wrote:
P.S.: I have put Mr. Hipp on CC because after many hours of testing and
reading up on SQL (that's basically all I did today), I'm quite
convinced that there's a bug.
I read the mailing list

Re: [sqlite] LEFT JOIN doesn't work as expected

[D. Richard Hipp]

[EMAIL PROTECTED] wrote:
P.S.: I have put Mr. Hipp on CC because after many hours of testing and
reading up on SQL (that's basically all I did today), I'm quite
convinced that there's a bug.
I read the mailing list
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

Re: Re: [sqlite] LEFT JOIN doesn't work as expected

[mailinglists]

Klint Gore <[EMAIL PROTECTED]> schrieb am 04.08.2004, 10:30:00:
> On Wed,  4 Aug 2004 08:48:01 +0200,  wrote:
> > Any more ideas on how to get the result I want from SQLite?
> > Or is this something that can't be done?
> 
> union the 2 sets of properties together.  if you need to add objects
> with no properties, it would probably be easier to union them on the
> end.
> 
> SELECT 
>   object.object_id,
>   p1.value_table,
>   string_values.value as stringval,
>   null as intval
> >FROM object
> JOIN properties p1 ON p1.object_id = object.object_id 
> JOIN string_values ON string_values.property_id = p1.property_id
> where p1.value_table = 1
> 
> UNION
> 
> SELECT 
>   object.object_id,
>   p1.value_table,
>   null as stringval,
>   int_values.value
> >FROM object
> JOIN properties p1 ON p1.object_id = object.object_id 
> JOIN int_values ON int_values.property_id = p1.property_id
> where p1.value_table = 2;
> 
> object.object_id  p1.value_table  stringval   int_values.value
>   --  --  
> 1 2   123
> 1 1   Hallo
> 
> klint.
> 

Hello Klint.

With SQLite, this does the trick.
However, it seems that it only works because of the typelessness of
SQLite. If I try this query with MySQL, the result looks like this:
+---+-+---++
| object_id | value_table | stringval | intval |
+---+-+---++
| 1 |   2 | NULL  |123 |
| 1 |   1 |   |   NULL |
+---+-+---++
For the stringval column, NULL is the first value. This value defines
the type for this column. When later "Hallo" is added, it doesn't fit
the type of the column and is somehow converted. It then appears as
empty string.

As I do not want to rely on behaviour specific to SQLite, I can't use
this trick.
Do you have an idea on how to make this work with a 'typed' database?

Thanks

Rolf Schäuble

Re: [sqlite] LEFT JOIN doesn't work as expected

[mailinglists]

Darren Duncan wrote:
> At 8:12 PM +0200 8/3/04, Rolf Schaeuble wrote:
>>SELECT
>>  object.object_id,
>>  properties.value_table,
>>  string_values.value,
>>  int_values.value
>>
>>FROM object
>>LEFT OUTER JOIN properties ON object.object_id = properties.object_id
>>LEFT OUTER JOIN string_values ON
>>  (properties.value_table = 1) AND
>>  (properties.property_id = string_values.property_id)
>>
>>LEFT OUTER JOIN int_values ON
>>  (properties.value_table = 2) AND
>>  (properties.property_id = int_values.property_id)
>>;
> 
> Your original query looks mal-formed.  Join clauses are meant to be 
> used only for saying what the related columns are, and not any 
> filtering conditions.  You do filtering in a WHERE clause.  Like this:
> 
> SELECT
>   object.object_id,
>   properties.value_table,
>   string_values.value,
>   int_values.value
> FROM object
>   LEFT OUTER JOIN properties ON object.object_id = properties.object_id
>   LEFT OUTER JOIN string_values ON properties.property_id = 
> string_values.property_id
>   LEFT OUTER JOIN int_values ON properties.property_id = 
> int_values.property_id
> WHERE
>   (properties.table_value = 1 AND string_values.property_id IS 
> NOT NULL) OR
>   (properties.table_value = 2 AND int_values.property_id IS NOT NULL)
> ;
> 
> Even then, I think that your value_table field serves no purpose at 
> all, and you can just do this, assuming that string_values and 
> int_values records will not exist when they shouldn't:
> 
> SELECT
>   object.object_id,
>   string_values.value,
>   int_values.value
> FROM object
>   LEFT OUTER JOIN properties ON object.object_id = properties.object_id
>   LEFT OUTER JOIN string_values ON properties.property_id = 
> string_values.property_id
>   LEFT OUTER JOIN int_values ON properties.property_id = 
> int_values.property_id
> ;
> 
> Moreover, the above query assumes you can have objects without any 
> properties (which is valid).  However, if all objects must have at 
> least one property, then you can remove the object table from the 
> query, as it doesn't add anything then.
> 
> -- Darren Duncan

Thanks for your answer.

When trying the first of your suggestions, the result I got was this:
+---+-+---+---+
| object_id | value_table | value | value |
+---+-+---+---+
| 1 |   1 | Hallo |   123 |
| 1 |   2 | Hallo |   123 |
+---+-+---+---+

The problem here is that in both rows of the result, both the
string_value and the int_value are present.
I get this result both in SQLite and in MySQL.

I don't really understand why this happens; when the filtering is done
in the JOIN, at least MySQL produces the result that I want.

When I add more int-properties, it gets even funnier: The result doesn't
change. The new properties don't appear, I just get the same result
that I got before inserting them; with my original select in MySQL they
would appear.
To try it, you can use the following SQL statements:
INSERT INTO properties VALUES (1, 101, 2);
INSERT INTO int_values VALUES (101, 111);
INSERT INTO properties VALUES (1, 102, 2);
INSERT INTO int_values VALUES (102, 222);
INSERT INTO properties VALUES (1, 103, 2);
INSERT INTO int_values VALUES (103, 333);

Any more ideas on how to get the result I want from SQLite?
Or is this something that can't be done?

Thanks again.

Rolf Schäuble

Re: [sqlite] LEFT JOIN doesn't work as expected

[Darren Duncan]

At 8:12 PM +0200 8/3/04, Rolf Schaeuble wrote:
SELECT
object.object_id,
properties.value_table,
string_values.value,
int_values.value
FROM object
LEFT OUTER JOIN properties ON object.object_id = properties.object_id
LEFT OUTER JOIN string_values ON
(properties.value_table = 1) AND
(properties.property_id = string_values.property_id)
LEFT OUTER JOIN int_values ON
(properties.value_table = 2) AND
(properties.property_id = int_values.property_id)
;
Your original query looks mal-formed.  Join clauses are meant to be 
used only for saying what the related columns are, and not any 
filtering conditions.  You do filtering in a WHERE clause.  Like this:

SELECT
	object.object_id,
	properties.value_table,
	string_values.value,
	int_values.value
FROM object
	LEFT OUTER JOIN properties ON object.object_id = properties.object_id
	LEFT OUTER JOIN string_values ON properties.property_id = 
string_values.property_id
	LEFT OUTER JOIN int_values ON properties.property_id = 
int_values.property_id
WHERE
	(properties.table_value = 1 AND string_values.property_id IS 
NOT NULL) OR
	(properties.table_value = 2 AND int_values.property_id IS NOT NULL)
;

Even then, I think that your value_table field serves no purpose at 
all, and you can just do this, assuming that string_values and 
int_values records will not exist when they shouldn't:

SELECT
	object.object_id,
	string_values.value,
	int_values.value
FROM object
	LEFT OUTER JOIN properties ON object.object_id = properties.object_id
	LEFT OUTER JOIN string_values ON properties.property_id = 
string_values.property_id
	LEFT OUTER JOIN int_values ON properties.property_id = 
int_values.property_id
;

Moreover, the above query assumes you can have objects without any 
properties (which is valid).  However, if all objects must have at 
least one property, then you can remove the object table from the 
query, as it doesn't add anything then.

-- Darren Duncan

[sqlite] LEFT JOIN doesn't work as expected

[Rolf Schaeuble]

Hello,
today I've stumbled over a problem that I can't seem to find an answer for.
I have the following tables:
CREATE TABLE object (
object_id INTEGER PRIMARY KEY
);
CREATE TABLE properties (
object_id INTEGER,
property_id INTEGER,
value_table INTEGER
);
CREATE TABLE string_values (
property_id INTEGER PRIMARY KEY,
value VARCHAR(255)
);
CREATE TABLE int_values (
property_id INTEGER PRIMARY KEY,
value INTEGER
);
In English:
I have a list of objects. Each object can have 0..n properties. A property is either a 
string or an integer (and later maybe ever more different data types). In the 
'properties' table, the 'value_table' field indicates in which table the value of this 
property is stored: if it's 1, the value is stored in 'string_values'; if it's 2, the 
value is stored in 'int_values'.
Please note that I have left out the indexes to keep it simple; just assume that I 
have an index on each field that I use in WHERE and JOIN-ON statements.
The follwing SQL statements populate the database with an object, an int-property and 
a string-property:
-- Insert one object
INSERT INTO object VALUES (1);
-- Insert one string property
INSERT INTO properties VALUES (1, 100, 1);
INSERT INTO string_values VALUES (100, "Hallo");
-- Insert one int property
INSERT INTO properties VALUES (1, 100, 2);
INSERT INTO int_values VALUES (100, 123);
As you can see, the same values can be used in 'string_values.value' and 
'int_values.value'.
This means, that only the combination of 'value_table'/'property_id' uniquely 
identifies a property value.
When loading the objects into memory, I query for all properties of all objects.
For this I use the following select statement:
SELECT
object.object_id,
properties.value_table,
string_values.value,
int_values.value

FROM object
LEFT OUTER JOIN properties ON object.object_id = properties.object_id
LEFT OUTER JOIN string_values ON
(properties.value_table = 1) AND
(properties.property_id = string_values.property_id)

LEFT OUTER JOIN int_values ON
(properties.value_table = 2) AND
(properties.property_id = int_values.property_id)
;   
The format of the result should look like this:
+---+-+-+--+
| object_id | value_table | string_values.value | int_values.value |
+---+-+-+--+
Using the SQL statement from above, the result I would like to get is this:
+---+-+-+--+
| object_id | value_table | string_values.value | int_values.value |
+---+-+-+--+
| 1 |   1 |   Hallo | NULL |
| 1 |   2 |   NULL  |  123 |
+---+-+-+--+
This works well with MySQL (which is where I first developed the select statement).
When using it with SQLite, however, I get the following result:
+---+-+-+--+
| object_id | value_table | string_values.value | int_values.value |
+---+-+-+--+
| 1 |NULL |   NULL  | NULL |
+---+-+-+--+
I'm by no means a SQL expert (I've basically just started to play around with it), but my theory is this:
When MySQL processes the query, it performs the join from left to right.
First, it finds all objects.
   For each object, it finds all properties.
   For each property,
   - it finds all 'string values'
   - it finds all 'int values'
   
This would match the result of the query. 

But it looks like SQLite performs the query in another order, which results in the 
NULL results.
I could find a select statement that produces the desired result in SQLite:
SELECT
object.object_id,
properties.value_table,
string_values.value,
int_values.value

FROM object
LEFT OUTER JOIN properties ON object.object_id = properties.object_id
LEFT OUTER JOIN string_values ON
(
(properties.value_table = 1) AND
(properties.property_id = string_values.property_id)
) OR
(properties.value_table IS NULL)

LEFT OUTER JOIN int_values ON
(
(properties.value_table = 2) AND
(properties.property_id = int_values.property_id)
) OR
(properties.value_table IS NULL)

;
However, this query is *very* slow.
For a single object with a few properties, this doesn't matter. When using a database 
with
100.000 entries, each having a few properties, the second select is several orders of 
magnitude slower than the first one, since it can't make full