Simon Slavin writes:
> I understand that SQLite handles VIEWs as if you had defined and saved
> a SELECT statement. So if you JOIN with a VIEW, does SQLite handle it
> as as JOIN or a sub-SELECT ?
It's just the same as if you JOIN with a sub-SELECT (the query for the
view), as far as I can tell.
On 10 Dec 2011, at 10:35pm, Nick Smallbone wrote:
> the difference between my query and your query is
> that I have a subquery (select * from b) and you don't.
I understand that SQLite handles VIEWs as if you had defined and saved a SELECT
statement. So if you JOIN with a VIEW, does SQLite han
"Black, Michael (IS)"
writes:
> Natural joins are generally considered to be evil. Too many columns
> in common can be bad.
>
> If you just spell it out it works as expected
>
> sqlite> explain query plan select * from a left join b where a.id=1 and
> b.id=a.id;
> 0|0|0|SEARCH TABLE a USING COV
Simon Slavin writes:
> On 9 Dec 2011, at 10:25pm, Nick Smallbone wrote:
>
>> select * from a left natural join (select * from b) where id = 1;
>
> Try not to use sub-selects when you can use a JOIN instead.
> Especially don't use them in combination. If you express this as just
> a JOIN you'll
Black, Michael (IS) wrote:
> sqlite> explain query plan select * from a left join b where a.id=1 and
> b.id=a.id;
Make it
select * from a left join b on b.id=a.id where a.id=1;
The join condition must be in the ON clause, otherwise the left join behaves
like a plain vanilla inner join.
--
Ig
ion Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Simon Slavin [slav...@bigfraud.org]
Sent: Saturday, December 10, 2011 8:24 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Poor performance with nes
On 9 Dec 2011, at 10:25pm, Nick Smallbone wrote:
> select * from a left natural join (select * from b) where id = 1;
Try not to use sub-selects when you can use a JOIN instead. Especially don't
use them in combination. If you express this as just a JOIN you'll find that
the optimizer works
Hi list,
I have the following schema:
CREATE TABLE a(id int primary key);
CREATE TABLE b(id int primary key);
I want to find information about a particular id, and my query boils
down to something like
select * from a left natural join (select * from b) where id = 1;
(in the real code, t
8 matches
Mail list logo