Returning NULL or empty values when the SELECT does not satisfy all of the query

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Returning NULL or empty values when the SELECT does not satisfy all of the query

Jose Isaias Cabrera-4

Greetings.

I have a few tables that I am bringing data from, but I found a bug in my logic, which I am trying to see if I can make it work.  Please look at this scenario

create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4, '2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4, '2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4, '2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4, '2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4, '2019-02-11');
insert into t (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4, '2019-02-12');
insert into t (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4, '2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4, '2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4, '2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4, '2019-02-13');
insert into t (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4, '2019-02-13');

select * from t;

create table z (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
insert into z (f, g, h, i, j, idate) values ('p001', 'b', 6, 'o', 4, '2019-02-15');
insert into z (f, g, h, i, j, idate) values ('p002', 'b', 6, 'o', 4, '2019-02-15');
insert into z (f, g, h, i, j, idate) values ('p003', 'b', 6, 'i', 4, '2019-02-15');
insert into z (f, g, h, i, j, idate) values ('p004', 'a', 9, 'i', 4, '2019-02-16');
insert into z (f, g, h, i, j, idate) values ('p005', 'a', 8, 'u', 4, '2019-02-16');
insert into z (f, g, h, i, j, idate) values ('p001', 'a', 8, 'u', 4, '2019-02-17');
insert into z (f, g, h, i, j, idate) values ('p002', 'a', 5, 'e', 4, '2019-02-17');
insert into z (f, g, h, i, j, idate) values ('p003', 'a', 7, 'e', 4, '2019-02-17');
insert into z (f, g, h, i, j, idate) values ('p004', 'a', 7, 'b', 4, '2019-02-17');
insert into z (f, g, h, i, j, idate) values ('p005', 'a', 3, 'b', 4, '2019-02-18');
insert into z (f, g, h, i, j, idate) values ('p001', 'a', 3, 'a', 4, '2019-02-18');
insert into z (f, g, h, i, j, idate) values ('p002', 'a', 3, 'a', 4, '2019-02-18');
insert into z (f, g, h, i, j, idate) values ('p003', 'a', 5, 'a', 4, '2019-02-18');
insert into z (f, g, h, i, j, idate) values ('p004', 'a', 6, 'o', 4, '2019-02-18');
insert into z (f, g, h, i, j, idate) values ('p005', 'a', 7, 'o', 4, '2019-02-18');

select * from z;

I can do this,

select
 a.*, b.* from t as a join z as b on a.a = b.f
where a.a = 'p001'
AND
  a.idate = (select max(idate) from t where a = a.a)
AND
  b.idate = (select max(idate) from z where f = a.a)
ORDER BY a.a
;

and get the correct output,

11|p001|a|3|n|4|2019-02-13|11|p001|a|3|a|4|2019-02-18

without any problem.  But, when I do this,

insert into t (a, b, c, d, e, idate) values ('p006', 'e', 8, 'n', 5, '2019-03-01');

and then do this,

select
 a.*, b.* from t as a join z as b on a.a = b.f
where a.a = 'p006'
AND
  a.idate = (select max(idate) from t where a = a.a)
AND
  b.idate = (select max(idate) from z where f = a.a)
ORDER BY a.a
;

I get nothing.  I know why, but what will save my logic is, to be able to fix the query above and get something like this,

16|p006|e|8|n|5|2019-03-01|||||||

in other words, NULL values instead.  This will fix my "logic". :-) and the world will be at peace again. :-)  Is this even possible?  Thanks.

josé

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Returning NULL or empty values when the SELECT does not satisfy all of the query

Simon Slavin-3
On 5 Apr 2019, at 2:45pm, Jose Isaias Cabrera <[hidden email]> wrote:

> then do this,
>
> select
> a.*, b.* from t as a join z as b on a.a = b.f
> where a.a = 'p006'
> AND
>  a.idate = (select max(idate) from t where a = a.a)
> AND
>  b.idate = (select max(idate) from z where f = a.a)
> ORDER BY a.a
> ;

For testing purposes, break your SELECT down into single operations.  First do

select max(idate) from t where a = 'p006'
select max(idate) from z where f = 'p006'

Are those values the answers you expected ?  Then plug those answers into

> select
> a.*, b.* from t as a join z as b on a.a = b.f
> where a.a = 'p006'
> AND
>  a.idate = X
> AND
>  b.idate = Y
> ORDER BY a.a

and see whether it does what you expect.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Returning NULL or empty values when the SELECT does not satisfy all of the query

Jose Isaias Cabrera-4

Yeah, that is what I am doing now, but that results in two selects, and it's causing me to have to rewrite other pieces of the program.  It's a long story...  The owners of the business said that "there will never be...", WRONG! So, with that in mind, I wrote the reporting tool. Now, it's missing that record, and I want to leave all of my other SELECTs from other places intact, and I just have to deal with the empty/NULL values, which was already 95% in place.  I just have to address a few lines on a bunch of places to address searches, etc...


From: Simon Slavin
Sent: Friday, April 5, 2019 09:50 AM
To: SQLite mailing list
Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query

On 5 Apr 2019, at 2:45pm, Jose Isaias Cabrera <[hidden email]> wrote:

> then do this,
>
> select
> a.*, b.* from t as a join z as b on a.a = b.f
> where a.a = 'p006'
> AND
>  a.idate = (select max(idate) from t where a = a.a)
> AND
>  b.idate = (select max(idate) from z where f = a.a)
> ORDER BY a.a
> ;

For testing purposes, break your SELECT down into single operations.  First do

select max(idate) from t where a = 'p006'
select max(idate) from z where f = 'p006'

Are those values the answers you expected ?  Then plug those answers into

> select
> a.*, b.* from t as a join z as b on a.a = b.f
> where a.a = 'p006'
> AND
>  a.idate = X
> AND
>  b.idate = Y
> ORDER BY a.a

and see whether it does what you expect.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Returning NULL or empty values when the SELECT does not satisfy all of the query

Simon Davies
In reply to this post by Jose Isaias Cabrera-4
On Fri, 5 Apr 2019 at 14:45, Jose Isaias Cabrera <[hidden email]> wrote:

>
> Greetings.
>
> I have a few tables that I am bringing data from, but I found a bug in my logic, which I am trying to see if I can make it work.  Please look at this scenario
>
> create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4, '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4, '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4, '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4, '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4, '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4, '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4, '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4, '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4, '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4, '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4, '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4, '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4, '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4, '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4, '2019-02-13');
>
> select * from t;
>
> create table z (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
> insert into z (f, g, h, i, j, idate) values ('p001', 'b', 6, 'o', 4, '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p002', 'b', 6, 'o', 4, '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p003', 'b', 6, 'i', 4, '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 9, 'i', 4, '2019-02-16');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 8, 'u', 4, '2019-02-16');
> insert into z (f, g, h, i, j, idate) values ('p001', 'a', 8, 'u', 4, '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p002', 'a', 5, 'e', 4, '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p003', 'a', 7, 'e', 4, '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 7, 'b', 4, '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 3, 'b', 4, '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p001', 'a', 3, 'a', 4, '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p002', 'a', 3, 'a', 4, '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p003', 'a', 5, 'a', 4, '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 6, 'o', 4, '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 7, 'o', 4, '2019-02-18');
>
> select * from z;
>
> I can do this,
>
> select
>  a.*, b.* from t as a join z as b on a.a = b.f
> where a.a = 'p001'
> AND
>   a.idate = (select max(idate) from t where a = a.a)
> AND
>   b.idate = (select max(idate) from z where f = a.a)
> ORDER BY a.a
> ;
>
> and get the correct output,
>
> 11|p001|a|3|n|4|2019-02-13|11|p001|a|3|a|4|2019-02-18
>
> without any problem.  But, when I do this,
>
> insert into t (a, b, c, d, e, idate) values ('p006', 'e', 8, 'n', 5, '2019-03-01');
>
> and then do this,
>
> select
>  a.*, b.* from t as a join z as b on a.a = b.f
> where a.a = 'p006'
> AND
>   a.idate = (select max(idate) from t where a = a.a)
> AND
>   b.idate = (select max(idate) from z where f = a.a)
> ORDER BY a.a
> ;
>
> I get nothing.  I know why, but what will save my logic is, to be able to fix the query above and get something like this,
>
> 16|p006|e|8|n|5|2019-03-01|||||||
>
> in other words, NULL values instead.  This will fix my "logic". :-) and the world will be at peace again. :-)  Is this even possible?  Thanks.

left join:

select
 a.*, b.* from t as a left join z as b on a.a = b.f
AND
  a.idate = (select max(idate) from t where a = a.a)
AND
  b.idate = (select max(idate) from z where f = a.a)
where a.a = 'p006'
ORDER BY a.a
;

> josé

Regards,
Simon
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Returning NULL or empty values when the SELECT does not satisfy all of the query

James K. Lowden
In reply to this post by Jose Isaias Cabrera-4
On Fri, 5 Apr 2019 14:01:20 +0000
Jose Isaias Cabrera <[hidden email]> wrote:

> The owners of the business said that "there will never be...",

The perfect opportunity for a CHECK constraint or to enforce a foreign
key.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Returning NULL or empty values when the SELECT does not satisfy all of the query

Jose Isaias Cabrera-4

Folks, this is called a "Teachable Moment", by James K. Lowden. ;-)


From: James K. Lowden
Sent: Friday, April 5, 2019 12:31 PM
To: [hidden email]
Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query

On Fri, 5 Apr 2019 14:01:20 +0000
Jose Isaias Cabrera <[hidden email]> wrote:

> The owners of the business said that "there will never be...",

The perfect opportunity for a CHECK constraint or to enforce a foreign
key.

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Returning NULL or empty values when the SELECT does not satisfy all of the query

Jose Isaias Cabrera-4
In reply to this post by Simon Davies

Thanks,  Simon.  Works like a charm...


From: Simon Davies
Sent: Friday, April 5, 2019 12:24 PM
To: SQLite mailing list
Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query

On Fri, 5 Apr 2019 at 14:45, Jose Isaias Cabrera <[hidden email]> wrote:

>
> Greetings.
>
> I have a few tables that I am bringing data from, but I found a bug in my logic, which I am trying to see if I can make it work.  Please look at this scenario
>
> create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4, '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4, '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4, '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4, '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4, '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4, '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4, '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4, '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4, '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4, '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4, '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4, '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4, '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4, '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4, '2019-02-13');
>
> select * from t;
>
> create table z (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
> insert into z (f, g, h, i, j, idate) values ('p001', 'b', 6, 'o', 4, '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p002', 'b', 6, 'o', 4, '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p003', 'b', 6, 'i', 4, '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 9, 'i', 4, '2019-02-16');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 8, 'u', 4, '2019-02-16');
> insert into z (f, g, h, i, j, idate) values ('p001', 'a', 8, 'u', 4, '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p002', 'a', 5, 'e', 4, '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p003', 'a', 7, 'e', 4, '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 7, 'b', 4, '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 3, 'b', 4, '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p001', 'a', 3, 'a', 4, '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p002', 'a', 3, 'a', 4, '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p003', 'a', 5, 'a', 4, '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 6, 'o', 4, '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 7, 'o', 4, '2019-02-18');
>
> select * from z;
>
> I can do this,
>
> select
>  a.*, b.* from t as a join z as b on a.a = b.f
> where a.a = 'p001'
> AND
>   a.idate = (select max(idate) from t where a = a.a)
> AND
>   b.idate = (select max(idate) from z where f = a.a)
> ORDER BY a.a
> ;
>
> and get the correct output,
>
> 11|p001|a|3|n|4|2019-02-13|11|p001|a|3|a|4|2019-02-18
>
> without any problem.  But, when I do this,
>
> insert into t (a, b, c, d, e, idate) values ('p006', 'e', 8, 'n', 5, '2019-03-01');
>
> and then do this,
>
> select
>  a.*, b.* from t as a join z as b on a.a = b.f
> where a.a = 'p006'
> AND
>   a.idate = (select max(idate) from t where a = a.a)
> AND
>   b.idate = (select max(idate) from z where f = a.a)
> ORDER BY a.a
> ;
>
> I get nothing.  I know why, but what will save my logic is, to be able to fix the query above and get something like this,
>
> 16|p006|e|8|n|5|2019-03-01|||||||
>
> in other words, NULL values instead.  This will fix my "logic". :-) and the world will be at peace again. :-)  Is this even possible?  Thanks.

left join:

select
 a.*, b.* from t as a left join z as b on a.a = b.f
AND
  a.idate = (select max(idate) from t where a = a.a)
AND
  b.idate = (select max(idate) from z where f = a.a)
where a.a = 'p006'
ORDER BY a.a
;

> josé

Regards,
Simon
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Returning NULL or empty values when the SELECT does not satisfy all of the query

Jose Isaias Cabrera-4

A request for a teachable moment.. :-)

Why does this work,

select
 a.*, b.* from t as a LEFT JOIN z as b on a.a = b.f
AND
  a.idate = (select max(idate) from t where a = a.a)
AND
  b.idate = (select max(idate) from z where f = a.a)
where a.a = 'p006'
ORDER BY a.a
;

and this one does not,

select
 a.*, b.* from t as a LEFT JOIN z as b on a.a = b.f
where a.a = 'p006'
AND
  a.idate = (select max(idate) from t where a = a.a)
AND
  b.idate = (select max(idate) from z where f = a.a)
ORDER BY a.a
;

Thanks.




________________________________
From: sqlite-users <[hidden email]> on behalf of Jose Isaias Cabrera <[hidden email]>
Sent: Friday, April 5, 2019 02:36 PM
To: Simon Davies; SQLite mailing list
Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query


Thanks,  Simon.  Works like a charm...


From: Simon Davies
Sent: Friday, April 5, 2019 12:24 PM
To: SQLite mailing list
Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query

On Fri, 5 Apr 2019 at 14:45, Jose Isaias Cabrera <[hidden email]> wrote:

>
> Greetings.
>
> I have a few tables that I am bringing data from, but I found a bug in my logic, which I am trying to see if I can make it work.  Please look at this scenario
>
> create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4, '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4, '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4, '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4, '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4, '2019-02-11');
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4, '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4, '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4, '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4, '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4, '2019-02-12');
> insert into t (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4, '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4, '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4, '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4, '2019-02-13');
> insert into t (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4, '2019-02-13');
>
> select * from t;
>
> create table z (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
> insert into z (f, g, h, i, j, idate) values ('p001', 'b', 6, 'o', 4, '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p002', 'b', 6, 'o', 4, '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p003', 'b', 6, 'i', 4, '2019-02-15');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 9, 'i', 4, '2019-02-16');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 8, 'u', 4, '2019-02-16');
> insert into z (f, g, h, i, j, idate) values ('p001', 'a', 8, 'u', 4, '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p002', 'a', 5, 'e', 4, '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p003', 'a', 7, 'e', 4, '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 7, 'b', 4, '2019-02-17');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 3, 'b', 4, '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p001', 'a', 3, 'a', 4, '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p002', 'a', 3, 'a', 4, '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p003', 'a', 5, 'a', 4, '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p004', 'a', 6, 'o', 4, '2019-02-18');
> insert into z (f, g, h, i, j, idate) values ('p005', 'a', 7, 'o', 4, '2019-02-18');
>
> select * from z;
>
> I can do this,
>
> select
>  a.*, b.* from t as a join z as b on a.a = b.f
> where a.a = 'p001'
> AND
>   a.idate = (select max(idate) from t where a = a.a)
> AND
>   b.idate = (select max(idate) from z where f = a.a)
> ORDER BY a.a
> ;
>
> and get the correct output,
>
> 11|p001|a|3|n|4|2019-02-13|11|p001|a|3|a|4|2019-02-18
>
> without any problem.  But, when I do this,
>
> insert into t (a, b, c, d, e, idate) values ('p006', 'e', 8, 'n', 5, '2019-03-01');
>
> and then do this,
>
> select
>  a.*, b.* from t as a join z as b on a.a = b.f
> where a.a = 'p006'
> AND
>   a.idate = (select max(idate) from t where a = a.a)
> AND
>   b.idate = (select max(idate) from z where f = a.a)
> ORDER BY a.a
> ;
>
> I get nothing.  I know why, but what will save my logic is, to be able to fix the query above and get something like this,
>
> 16|p006|e|8|n|5|2019-03-01|||||||
>
> in other words, NULL values instead.  This will fix my "logic". :-) and the world will be at peace again. :-)  Is this even possible?  Thanks.

left join:

select
 a.*, b.* from t as a left join z as b on a.a = b.f
AND
  a.idate = (select max(idate) from t where a = a.a)
AND
  b.idate = (select max(idate) from z where f = a.a)
where a.a = 'p006'
ORDER BY a.a
;

> josé

Regards,
Simon
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Returning NULL or empty values when the SELECT does not satisfy all of the query

Igor Korot
In reply to this post by Jose Isaias Cabrera-4
Hi,

On Fri, Apr 5, 2019 at 1:36 PM Jose Isaias Cabrera <[hidden email]> wrote:
>
>
> Thanks,  Simon.  Works like a charm...

Unless backwards compatibility is important (do you expect to go back to
pre-foreign keys implementation), I'd do FOREIGN KEY amd forget anout that...

Thank you.

>
>
> From: Simon Davies
> Sent: Friday, April 5, 2019 12:24 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query
>
> On Fri, 5 Apr 2019 at 14:45, Jose Isaias Cabrera <[hidden email]> wrote:
> >
> > Greetings.
> >
> > I have a few tables that I am bringing data from, but I found a bug in my logic, which I am trying to see if I can make it work.  Please look at this scenario
> >
> > create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
> > insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4, '2019-02-11');
> > insert into t (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4, '2019-02-11');
> > insert into t (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4, '2019-02-11');
> > insert into t (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4, '2019-02-11');
> > insert into t (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4, '2019-02-11');
> > insert into t (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4, '2019-02-12');
> > insert into t (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4, '2019-02-12');
> > insert into t (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4, '2019-02-12');
> > insert into t (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4, '2019-02-12');
> > insert into t (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4, '2019-02-12');
> > insert into t (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4, '2019-02-13');
> > insert into t (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4, '2019-02-13');
> > insert into t (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4, '2019-02-13');
> > insert into t (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4, '2019-02-13');
> > insert into t (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4, '2019-02-13');
> >
> > select * from t;
> >
> > create table z (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
> > insert into z (f, g, h, i, j, idate) values ('p001', 'b', 6, 'o', 4, '2019-02-15');
> > insert into z (f, g, h, i, j, idate) values ('p002', 'b', 6, 'o', 4, '2019-02-15');
> > insert into z (f, g, h, i, j, idate) values ('p003', 'b', 6, 'i', 4, '2019-02-15');
> > insert into z (f, g, h, i, j, idate) values ('p004', 'a', 9, 'i', 4, '2019-02-16');
> > insert into z (f, g, h, i, j, idate) values ('p005', 'a', 8, 'u', 4, '2019-02-16');
> > insert into z (f, g, h, i, j, idate) values ('p001', 'a', 8, 'u', 4, '2019-02-17');
> > insert into z (f, g, h, i, j, idate) values ('p002', 'a', 5, 'e', 4, '2019-02-17');
> > insert into z (f, g, h, i, j, idate) values ('p003', 'a', 7, 'e', 4, '2019-02-17');
> > insert into z (f, g, h, i, j, idate) values ('p004', 'a', 7, 'b', 4, '2019-02-17');
> > insert into z (f, g, h, i, j, idate) values ('p005', 'a', 3, 'b', 4, '2019-02-18');
> > insert into z (f, g, h, i, j, idate) values ('p001', 'a', 3, 'a', 4, '2019-02-18');
> > insert into z (f, g, h, i, j, idate) values ('p002', 'a', 3, 'a', 4, '2019-02-18');
> > insert into z (f, g, h, i, j, idate) values ('p003', 'a', 5, 'a', 4, '2019-02-18');
> > insert into z (f, g, h, i, j, idate) values ('p004', 'a', 6, 'o', 4, '2019-02-18');
> > insert into z (f, g, h, i, j, idate) values ('p005', 'a', 7, 'o', 4, '2019-02-18');
> >
> > select * from z;
> >
> > I can do this,
> >
> > select
> >  a.*, b.* from t as a join z as b on a.a = b.f
> > where a.a = 'p001'
> > AND
> >   a.idate = (select max(idate) from t where a = a.a)
> > AND
> >   b.idate = (select max(idate) from z where f = a.a)
> > ORDER BY a.a
> > ;
> >
> > and get the correct output,
> >
> > 11|p001|a|3|n|4|2019-02-13|11|p001|a|3|a|4|2019-02-18
> >
> > without any problem.  But, when I do this,
> >
> > insert into t (a, b, c, d, e, idate) values ('p006', 'e', 8, 'n', 5, '2019-03-01');
> >
> > and then do this,
> >
> > select
> >  a.*, b.* from t as a join z as b on a.a = b.f
> > where a.a = 'p006'
> > AND
> >   a.idate = (select max(idate) from t where a = a.a)
> > AND
> >   b.idate = (select max(idate) from z where f = a.a)
> > ORDER BY a.a
> > ;
> >
> > I get nothing.  I know why, but what will save my logic is, to be able to fix the query above and get something like this,
> >
> > 16|p006|e|8|n|5|2019-03-01|||||||
> >
> > in other words, NULL values instead.  This will fix my "logic". :-) and the world will be at peace again. :-)  Is this even possible?  Thanks.
>
> left join:
>
> select
>  a.*, b.* from t as a left join z as b on a.a = b.f
> AND
>   a.idate = (select max(idate) from t where a = a.a)
> AND
>   b.idate = (select max(idate) from z where f = a.a)
> where a.a = 'p006'
> ORDER BY a.a
> ;
>
> > josé
>
> Regards,
> Simon
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Returning NULL or empty values when the SELECT does not satisfy all of the query

Jose Isaias Cabrera-4

It's a long story, but that project is done.  It's a reporting tool.  It worked well, until the process changed. I will keep it in mind for future projects... :-)



From: Igor Korot
Sent: Friday, April 5, 2019 02:55 PM
To: SQLite mailing list
Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query

Hi,

On Fri, Apr 5, 2019 at 1:36 PM Jose Isaias Cabrera <[hidden email]> wrote:
>
>
> Thanks,  Simon.  Works like a charm...

Unless backwards compatibility is important (do you expect to go back to
pre-foreign keys implementation), I'd do FOREIGN KEY amd forget anout that...

Thank you.

>
>
> From: Simon Davies
> Sent: Friday, April 5, 2019 12:24 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query
>
> On Fri, 5 Apr 2019 at 14:45, Jose Isaias Cabrera <[hidden email]> wrote:
> >
> > Greetings.
> >
> > I have a few tables that I am bringing data from, but I found a bug in my logic, which I am trying to see if I can make it work.  Please look at this scenario
> >
> > create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
> > insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4, '2019-02-11');
> > insert into t (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4, '2019-02-11');
> > insert into t (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4, '2019-02-11');
> > insert into t (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4, '2019-02-11');
> > insert into t (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4, '2019-02-11');
> > insert into t (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4, '2019-02-12');
> > insert into t (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4, '2019-02-12');
> > insert into t (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4, '2019-02-12');
> > insert into t (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4, '2019-02-12');
> > insert into t (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4, '2019-02-12');
> > insert into t (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4, '2019-02-13');
> > insert into t (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4, '2019-02-13');
> > insert into t (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4, '2019-02-13');
> > insert into t (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4, '2019-02-13');
> > insert into t (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4, '2019-02-13');
> >
> > select * from t;
> >
> > create table z (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
> > insert into z (f, g, h, i, j, idate) values ('p001', 'b', 6, 'o', 4, '2019-02-15');
> > insert into z (f, g, h, i, j, idate) values ('p002', 'b', 6, 'o', 4, '2019-02-15');
> > insert into z (f, g, h, i, j, idate) values ('p003', 'b', 6, 'i', 4, '2019-02-15');
> > insert into z (f, g, h, i, j, idate) values ('p004', 'a', 9, 'i', 4, '2019-02-16');
> > insert into z (f, g, h, i, j, idate) values ('p005', 'a', 8, 'u', 4, '2019-02-16');
> > insert into z (f, g, h, i, j, idate) values ('p001', 'a', 8, 'u', 4, '2019-02-17');
> > insert into z (f, g, h, i, j, idate) values ('p002', 'a', 5, 'e', 4, '2019-02-17');
> > insert into z (f, g, h, i, j, idate) values ('p003', 'a', 7, 'e', 4, '2019-02-17');
> > insert into z (f, g, h, i, j, idate) values ('p004', 'a', 7, 'b', 4, '2019-02-17');
> > insert into z (f, g, h, i, j, idate) values ('p005', 'a', 3, 'b', 4, '2019-02-18');
> > insert into z (f, g, h, i, j, idate) values ('p001', 'a', 3, 'a', 4, '2019-02-18');
> > insert into z (f, g, h, i, j, idate) values ('p002', 'a', 3, 'a', 4, '2019-02-18');
> > insert into z (f, g, h, i, j, idate) values ('p003', 'a', 5, 'a', 4, '2019-02-18');
> > insert into z (f, g, h, i, j, idate) values ('p004', 'a', 6, 'o', 4, '2019-02-18');
> > insert into z (f, g, h, i, j, idate) values ('p005', 'a', 7, 'o', 4, '2019-02-18');
> >
> > select * from z;
> >
> > I can do this,
> >
> > select
> >  a.*, b.* from t as a join z as b on a.a = b.f
> > where a.a = 'p001'
> > AND
> >   a.idate = (select max(idate) from t where a = a.a)
> > AND
> >   b.idate = (select max(idate) from z where f = a.a)
> > ORDER BY a.a
> > ;
> >
> > and get the correct output,
> >
> > 11|p001|a|3|n|4|2019-02-13|11|p001|a|3|a|4|2019-02-18
> >
> > without any problem.  But, when I do this,
> >
> > insert into t (a, b, c, d, e, idate) values ('p006', 'e', 8, 'n', 5, '2019-03-01');
> >
> > and then do this,
> >
> > select
> >  a.*, b.* from t as a join z as b on a.a = b.f
> > where a.a = 'p006'
> > AND
> >   a.idate = (select max(idate) from t where a = a.a)
> > AND
> >   b.idate = (select max(idate) from z where f = a.a)
> > ORDER BY a.a
> > ;
> >
> > I get nothing.  I know why, but what will save my logic is, to be able to fix the query above and get something like this,
> >
> > 16|p006|e|8|n|5|2019-03-01|||||||
> >
> > in other words, NULL values instead.  This will fix my "logic". :-) and the world will be at peace again. :-)  Is this even possible?  Thanks.
>
> left join:
>
> select
>  a.*, b.* from t as a left join z as b on a.a = b.f
> AND
>   a.idate = (select max(idate) from t where a = a.a)
> AND
>   b.idate = (select max(idate) from z where f = a.a)
> where a.a = 'p006'
> ORDER BY a.a
> ;
>
> > josé
>
> Regards,
> Simon
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Returning NULL or empty values when the SELECT does not satisfy all of the query

James K. Lowden
In reply to this post by Jose Isaias Cabrera-4
On Fri, 5 Apr 2019 18:54:18 +0000
Jose Isaias Cabrera <[hidden email]> wrote:

> Why does this work

I don't know what "work" means, but I can explain the difference.  With
an outer join, JOIN and WHERE are not the same.  In analyzing the
query, we consider JOIN before WHERE.  

> select
>  a.*, b.* from t as a LEFT JOIN z as b on a.a = b.f
> AND
>   a.idate = (select max(idate) from t where a = a.a)
> AND
>   b.idate = (select max(idate) from z where f = a.a)
> where a.a = 'p006'

In an outer join, the outer table -- think "outer" as Venn diagram --
is the "preserved" table.  All rows match, join criteria
notwithstanding. This component has no effect:

>   a.idate = (select max(idate) from t where a = a.a)

because "a" is the outer table, and so all rows match, join criteria
notwithstanding.  

The inner table is a little different, and also different from WHERE in
an inner join.  On the inner table, JOIN restrictions can cause a row
not to match that otherwise would.  In your query:

>   b.idate = (select max(idate) from z where f = a.a)

"b" rows that don't pass that test are rejected from the join.  The "a"
columns will appear (because all "a" rows do) and the "b" columns will
be NULL.  

Only once the join is completed do we consider WHERE:

> where a.a = 'p006'

This restricts rows in the outer table.  

Now let's look at your other query.  

> select
>  a.*, b.* from t as a LEFT JOIN z as b on a.a = b.f

The *only* join criterion is a.a = b.f.  All rows passing that test are
subjected to WHERE: all "a" rows (because outer table) and "b"
rows with a matching "f".  

> where a.a = 'p006'

OK, same as #1.  

> AND
>   a.idate = (select max(idate) from t where a = a.a)

This further restricts the "a" table rows, unlike #1.

> AND
>   b.idate = (select max(idate) from z where f = a.a)

This restricts produced rows to those passing the test.  Different from
#1, no row passes this test for which b.idate is NULL.  

Whenever you disallow NULL on the inner table of an outer join, you
effectively convert the join from outer to inner.  You're asking for:

1. all rows in "a", whether or not they match a "b" row, provided
2. they do match a "b" row (because b.idate cannot be NULL)

You can vote for anyone in either party from this list of Democrats.  

Because both AND clauses restrict the output, we can expect the 2nd
query to produce fewer rows.  If it doesn't, there's no need for an
outer query for the data in their current state.  

By the way, the distinction of JOIN and WHERE is not a relational
concept.  It was added to SQL-92 in part to provide exactly the
separation your question illustrates: how to express an outer join.  

Relationally, that's not needed.  An outer join is nothing but an inner
join + a union with the outer table.  That's cumbersome to express
in SQL, and anything cumbersome to express is hard to optimize, and
query optimization was and is the unsolved technical challenge of SQL.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Returning NULL or empty values when the SELECT does not satisfy all of the query

Jose Isaias Cabrera-4

Wow!  Thanks, JKL.  You sent me back to school on this one. ;-)  I had to go back and refresh the Venn diagram. :-) Thanks.  Also, thanks for the time explaining it.  I have to read it a few more times to really get it into my brains so that I don't keep asking the same questions.  It would be nice to do a compilation of all the questions done in here and any pertinent answer/response.



From: James K. Lowden
Sent: Saturday, April 6, 2019 01:17 PM
To: [hidden email]
Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not satisfy all of the query

On Fri, 5 Apr 2019 18:54:18 +0000
Jose Isaias Cabrera <[hidden email]> wrote:

> Why does this work

I don't know what "work" means, but I can explain the difference.  With
an outer join, JOIN and WHERE are not the same.  In analyzing the
query, we consider JOIN before WHERE.

> select
>  a.*, b.* from t as a LEFT JOIN z as b on a.a = b.f
> AND
>   a.idate = (select max(idate) from t where a = a.a)
> AND
>   b.idate = (select max(idate) from z where f = a.a)
> where a.a = 'p006'

In an outer join, the outer table -- think "outer" as Venn diagram --
is the "preserved" table.  All rows match, join criteria
notwithstanding. This component has no effect:

>   a.idate = (select max(idate) from t where a = a.a)

because "a" is the outer table, and so all rows match, join criteria
notwithstanding.

The inner table is a little different, and also different from WHERE in
an inner join.  On the inner table, JOIN restrictions can cause a row
not to match that otherwise would.  In your query:

>   b.idate = (select max(idate) from z where f = a.a)

"b" rows that don't pass that test are rejected from the join.  The "a"
columns will appear (because all "a" rows do) and the "b" columns will
be NULL.

Only once the join is completed do we consider WHERE:

> where a.a = 'p006'

This restricts rows in the outer table.

Now let's look at your other query.

> select
>  a.*, b.* from t as a LEFT JOIN z as b on a.a = b.f

The *only* join criterion is a.a = b.f.  All rows passing that test are
subjected to WHERE: all "a" rows (because outer table) and "b"
rows with a matching "f".

> where a.a = 'p006'

OK, same as #1.

> AND
>   a.idate = (select max(idate) from t where a = a.a)

This further restricts the "a" table rows, unlike #1.

> AND
>   b.idate = (select max(idate) from z where f = a.a)

This restricts produced rows to those passing the test.  Different from
#1, no row passes this test for which b.idate is NULL.

Whenever you disallow NULL on the inner table of an outer join, you
effectively convert the join from outer to inner.  You're asking for:

1.      all rows in "a", whether or not they match a "b" row, provided
2.      they do match a "b" row (because b.idate cannot be NULL)

You can vote for anyone in either party from this list of Democrats.

Because both AND clauses restrict the output, we can expect the 2nd
query to produce fewer rows.  If it doesn't, there's no need for an
outer query for the data in their current state.

By the way, the distinction of JOIN and WHERE is not a relational
concept.  It was added to SQL-92 in part to provide exactly the
separation your question illustrates: how to express an outer join.

Relationally, that's not needed.  An outer join is nothing but an inner
join + a union with the outer table.  That's cumbersome to express
in SQL, and anything cumbersome to express is hard to optimize, and
query optimization was and is the unsolved technical challenge of SQL.

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users