[sqlite] Incorrect left join behavior?
Hi, I have a table as below. Note that machine1 has 3 components (1-3),
while machine2 has only 1 components (1).
Machine
Component
Version
machine1
component1
1
machine1
component2
1
machine1
component3
1
machine2
component1
1
create table test(Machine, Component, Version);
insert into test values('machine1', 'component1', 1);
insert into test values('machine1', 'component2', 1);
insert into test values('machine1', 'component3', 1);
insert into test values('machine2', 'component1', 1);
I need to see what component versions are different on the machines. What I
really need is a "self outer join", but since SQLite doesn't do outer joins,
I can use two left joins. While writing the first left join, I discovered
that it is behaving like an inner join.
select *
from test a
left join test b on a.component = b.component
where a.machine = 'machine1'
and b.machine = 'machine2';
The expected behavior would be to return 3 rows: one row showing the
component versions for machine1 and machine2, and two rows showing the
component version for machine1 with null for machine2.
The actual behavior is that of an inner join: I get only one row that shows
the component versions for both machines, and the other two components,
which don't exist for machine2, do not appear in the results at all.
I'm aware that SQLite only does left joins properly using SQL92 syntax,
which I am using. I'm also aware that I'm using a where clause, and the
docs state that join constraints in a where clause cause a left join to
behave as an inner join. However, my where clause does not constrain the
join expression, just the initial rows involved available to be joined.
I'm able to work around this by:
select *
from (select * from test where machine = 'machine1') a
(select * from test where machine = 'machine2') b
on a.component = b.component;
But, I just wanted to point this behavior out. Perhaps I've missed
something. It looks like any time there is a where clause, a left join is
going to behave as an inner join.
Thanks,
~Mike
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Incorrect left join behavior?
> I can use two left joins. While writing the first left join, I discovered
> that it is behaving like an inner join.
>
> select *
> from test a
> left join test b on a.component = b.component
> where a.machine = 'machine1'
> and b.machine = 'machine2';
By the WHERE condition you limit results of your left join only to
those that have b.machine equal to 'machine2' and exclude other 2 rows
where b.machine is null. To get the result you need you have to
rewrite the query in one of the following ways:
select *
from test a left join test b
on a.component = b.component
and b.machine = 'machine2'
where a.machine = 'machine1';
or
select *
from test a left join test b
on a.component = b.component
where a.machine = 'machine1'
and (b.machine is null or b.machine = 'machine2');
I think the first syntax is preferable.
Pavel
On Wed, May 25, 2011 at 9:09 AM, Michael Stephenson
wrote:
> Hi, I have a table as below. Note that machine1 has 3 components (1-3),
> while machine2 has only 1 components (1).
>
>
>
>
> Machine
>
> Component
>
> Version
>
>
> machine1
>
> component1
>
> 1
>
>
> machine1
>
> component2
>
> 1
>
>
> machine1
>
> component3
>
> 1
>
>
> machine2
>
> component1
>
> 1
>
>
>
> create table test(Machine, Component, Version);
>
> insert into test values('machine1', 'component1', 1);
>
> insert into test values('machine1', 'component2', 1);
>
> insert into test values('machine1', 'component3', 1);
>
> insert into test values('machine2', 'component1', 1);
>
>
>
> I need to see what component versions are different on the machines. What I
> really need is a "self outer join", but since SQLite doesn't do outer joins,
> I can use two left joins. While writing the first left join, I discovered
> that it is behaving like an inner join.
>
>
>
> select *
>
> from test a
>
> left join test b on a.component = b.component
>
> where a.machine = 'machine1'
>
> and b.machine = 'machine2';
>
>
>
>
>
> The expected behavior would be to return 3 rows: one row showing the
> component versions for machine1 and machine2, and two rows showing the
> component version for machine1 with null for machine2.
>
>
>
> The actual behavior is that of an inner join: I get only one row that shows
> the component versions for both machines, and the other two components,
> which don't exist for machine2, do not appear in the results at all.
>
>
>
> I'm aware that SQLite only does left joins properly using SQL92 syntax,
> which I am using. I'm also aware that I'm using a where clause, and the
> docs state that join constraints in a where clause cause a left join to
> behave as an inner join. However, my where clause does not constrain the
> join expression, just the initial rows involved available to be joined.
>
>
>
> I'm able to work around this by:
>
>
>
> select *
>
> from (select * from test where machine = 'machine1') a
>
> (select * from test where machine = 'machine2') b
>
> on a.component = b.component;
>
>
>
> But, I just wanted to point this behavior out. Perhaps I've missed
> something. It looks like any time there is a where clause, a left join is
> going to behave as an inner join.
>
>
>
> Thanks,
>
>
>
> ~Mike
>
>
>
>
>
> ___
> 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] Incorrect left join behavior?
Makes sense. Thanks.
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
Sent: Wednesday, May 25, 2011 9:37 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Incorrect left join behavior?
> I can use two left joins. While writing the first left join, I discovered
> that it is behaving like an inner join.
>
> select *
> from test a
> left join test b on a.component = b.component
> where a.machine = 'machine1'
> and b.machine = 'machine2';
By the WHERE condition you limit results of your left join only to
those that have b.machine equal to 'machine2' and exclude other 2 rows
where b.machine is null. To get the result you need you have to
rewrite the query in one of the following ways:
select *
from test a left join test b
on a.component = b.component
and b.machine = 'machine2'
where a.machine = 'machine1';
or
select *
from test a left join test b
on a.component = b.component
where a.machine = 'machine1'
and (b.machine is null or b.machine = 'machine2');
I think the first syntax is preferable.
Pavel
On Wed, May 25, 2011 at 9:09 AM, Michael Stephenson
wrote:
> Hi, I have a table as below. Note that machine1 has 3 components (1-3),
> while machine2 has only 1 components (1).
>
>
>
>
> Machine
>
> Component
>
> Version
>
>
> machine1
>
> component1
>
> 1
>
>
> machine1
>
> component2
>
> 1
>
>
> machine1
>
> component3
>
> 1
>
>
> machine2
>
> component1
>
> 1
>
>
>
> create table test(Machine, Component, Version);
>
> insert into test values('machine1', 'component1', 1);
>
> insert into test values('machine1', 'component2', 1);
>
> insert into test values('machine1', 'component3', 1);
>
> insert into test values('machine2', 'component1', 1);
>
>
>
> I need to see what component versions are different on the machines. What
I
> really need is a "self outer join", but since SQLite doesn't do outer
joins,
> I can use two left joins. While writing the first left join, I discovered
> that it is behaving like an inner join.
>
>
>
> select *
>
> from test a
>
> left join test b on a.component = b.component
>
> where a.machine = 'machine1'
>
> and b.machine = 'machine2';
>
>
>
>
>
> The expected behavior would be to return 3 rows: one row showing the
> component versions for machine1 and machine2, and two rows showing the
> component version for machine1 with null for machine2.
>
>
>
> The actual behavior is that of an inner join: I get only one row that
shows
> the component versions for both machines, and the other two components,
> which don't exist for machine2, do not appear in the results at all.
>
>
>
> I'm aware that SQLite only does left joins properly using SQL92 syntax,
> which I am using. I'm also aware that I'm using a where clause, and the
> docs state that join constraints in a where clause cause a left join to
> behave as an inner join. However, my where clause does not constrain the
> join expression, just the initial rows involved available to be joined.
>
>
>
> I'm able to work around this by:
>
>
>
> select *
>
> from (select * from test where machine = 'machine1') a
>
> (select * from test where machine = 'machine2') b
>
> on a.component = b.component;
>
>
>
> But, I just wanted to point this behavior out. Perhaps I've missed
> something. It looks like any time there is a where clause, a left join is
> going to behave as an inner join.
>
>
>
> Thanks,
>
>
>
> ~Mike
>
>
>
>
>
> ___
> 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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
