You can use something like:
select tableA.path, tableA.value from tableA,tableB where
tableA.path=tableB.path and (tableA.value=tableB.value or
(tableA.value IS NULL AND tableB.value IS NULL));
It's possible that won't use an index, either, due to the OR, in which
case you could try a union between a select with is-null only, and
another with equality, something like:
select tableA.path, tableA.value from tableA,tableB where
tableA.path=tableB.path and tableA.value=tableB.value union
select tableA.path, tableA.value from tableA,tableB where
tableA.path=tableB.path and tableA.value IS NULL AND tableB.value IS
NULL;
If you really can treat null as '', then you might be better off
defining the column as NOT NULL DEFAULT ''.
-scott
On 6/15/07, Sean Cunningham <[EMAIL PROTECTED]> wrote:
I am hoping there is an obvious answer to this that I've overlooked.
I have two tables:
create table tableA (path TEXT, value TEXT);
create index myIndexA on tableA (path, value);
create table tableB(path TEXT, value TEXT);
create index myIndexB on tableB (path, value);
Now some simple inserts:
insert into tableA(path,value) values('alpha', '1');
insert into tableA(path,value) values('beta', '2');
insert into tableB(path,value) values('alpha', '1');
insert into tableB(path,value) values('beta', '999');
The following select statement gets what you'd expect, and it uses
the index.
sqlite> select tableA.path, tableA.value from tableA,tableB where
tableA.path=tableB.path and tableA.value=tableB.value;
alpha|1
gamma|3
However, if your data has NULL's:
insert into tableA(path,value) values('gamma', NULL);
insert into tableB(path,value) values('gamma', NULL);
The same select above neglects to report the tuple ('gamma',NULL) as
the equal. This is correct given SQL's treatment of NULL, and is
easily fixed:
sqlite> select tableA.path, tableA.value from tableA,tableB where
ifnull(tableA.path,'')=ifnull(tableB.path,'') and ifnull
(tableA.value,'')=ifnull(tableB.value,'');
alpha|1
gamma|3
gamma|
However, the above statement has the unfortunate side effect of
bypassing the index:
sqlite> explain select tableA.path, tableA.value from tableA,tableB
where ifnull(tableA.path,'')=ifnull(tableB.path,'') and ifnull
(tableA.value,'')=ifnull(tableB.value,'');
0|Goto|0|35|
1|Integer|0|0|
2|OpenRead|0|2|
3|SetNumColumns|0|2|
4|Integer|0|0|
5|OpenRead|1|4|
6|SetNumColumns|1|2|
7|Rewind|0|32|
8|Rewind|1|31|
9|Column|0|0|
10|String8|0|0|
11|CollSeq|0|0|collseq(BINARY)
12|Function|2|2|ifnull(2)
13|Column|1|0|
14|String8|0|0|
15|CollSeq|0|0|collseq(BINARY)
16|Function|2|2|ifnull(2)
17|Ne|28417|30|
18|Column|0|1|
19|String8|0|0|
20|CollSeq|0|0|collseq(BINARY)
21|Function|2|2|ifnull(2)
22|Column|1|1|
23|String8|0|0|
24|CollSeq|0|0|collseq(BINARY)
25|Function|2|2|ifnull(2)
26|Ne|28417|30|
27|Column|0|0|
28|Column|0|1|
29|Callback|2|0|
30|Next|1|9|
31|Next|0|8|
32|Close|0|0|
33|Close|1|0|
34|Halt|0|0|
35|Transaction|0|0|
36|VerifyCookie|0|4|
37|Goto|0|1|
38|Noop|0|0|
My question is: Is there another way to write such a select
statement which can solve both problems of treating NULL==NULL and
using the index.
Thanks,
Sean
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
