SQL - Getting null values from an outer select
Using Informix Online 5.08, HP-UX 10.20
I am trying to come up with a way of finding the null records from a sql
statement containing an outer clause, but without using an into temp and
then another statement to get the null values from that
eg
select orders.ordno orders_ordno, despatch.ordno despatch_ordno
from orders, outer(despatch)
where orders.ordno = despatch.ordno
into temp a;
select * from a
where despatch_ordno is null;
which will show all orders without despatch records.
Another way is
select ordno
from orders
where ordno not in (select ordno from despatch)
but on large tables this is slow.
Can anyone help ?
Richard.
Richard wrote:
> Using Informix Online 5.08, HP-UX 10.20
>
> I am trying to come up with a way of finding the null records from a sql
> statement containing an outer clause, but without using an into temp and
> then another statement to get the null values from that
What's all this about nulls and outer? Aren't you just trying to find
parent records which don't have children? :-)
>
>
> eg
>
> select orders.ordno orders_ordno, despatch.ordno despatch_ordno
> from orders, outer(despatch)
> where orders.ordno = despatch.ordno
> into temp a;
>
> select * from a
> where despatch_ordno is null;>
> which will show all orders without despatch records.
>
> Another way is
>
> select ordno
> from orders
> where ordno not in (select ordno from despatch)
This should zoom along quite nicely, provided you have an index on
despatch(ordno) - presumably, its already there.
select ordno
from orders a
where not exists (
select 1 from despatch b
where a.ordno = b.ordno);
Rudy
>
Try changing the NOT IN clause to a NOT EXIST clause and a correllated
sub-query. Logic says that should be slower but it MAY not in 5.0x. I
always say there are at least three ways to write any non-trivial SQL
statement and if you have not tested all three you may be using the best
version. Unfortunately, ignoring the temp table solution, as requested,
which is the third version, the fourth version requires ANSI-92 OUTER JOIN
syntax which is not supported until 7.31+.
Art S. Kagel
Rudy Fernandes wrote:
>
> Richard wrote:
>
> > Using Informix Online 5.08, HP-UX 10.20
> >
> > I am trying to come up with a way of finding the null records from a sql
> > statement containing an outer clause, but without using an into temp and
> > then another statement to get the null values from that
>
> What's all this about nulls and outer? Aren't you just trying to find
> parent records which don't have children? :-)
>
> >
> >
> > eg
> >
> > select orders.ordno orders_ordno, despatch.ordno despatch_ordno
> > from orders, outer(despatch)
> > where orders.ordno = despatch.ordno
> > into temp a;
> >
> > select * from a
> > where despatch_ordno is null;> >
> > which will show all orders without despatch records.
> >
> > Another way is
> >
> > select ordno
> > from orders
> > where ordno not in (select ordno from despatch)>
> This should zoom along quite nicely, provided you have an index on
> despatch(ordno) - presumably, its already there.
>
> select ordno
> from orders a
> where not exists (
> select 1 from despatch b
> where a.ordno = b.ordno);>
> Rudy
>
> >
"Richard" <richard@disctronics.co.uk> wrote:
>Using Informix Online 5.08, HP-UX 10.20
>
>I am trying to come up with a way of finding the null records from a sql
>statement containing an outer clause, but without using an into temp and
>then another statement to get the null values from that
>
>eg
>
>select orders.ordno orders_ordno, despatch.ordno despatch_ordno
>from orders, outer(despatch)
>where orders.ordno = despatch.ordno
>into temp a;
>
>select * from a
>where despatch_ordno is null;>
>which will show all orders without despatch records.
>
>Another way is
>
>select ordno
>from orders
>where ordno not in (select ordno from despatch)>
>but on large tables this is slow.
>
>Can anyone help ?
>
>Richard.
I drafted up a little example which might be applicable:
create temp table a(b integer);
create temp table c(b integer,d integer);
insert into a values(1);
insert into a values(2);
insert into a values(3);
insert into c values(1,1);
insert into c values(1,2);
insert into c values(3,3);
select a.b,count(c.b) from a,outer c where a.b=c.b
group by 1
having count(c.b)=0
This would find all those with zero matches in the outer join, here 2!
This only works if you have "having" which I don't remember if
is the case in your version.
If you need more than one column from 'a' you would have to do
select a,b,c,d....,count(..) from table1,outer table2
group by 1,2,3,4,.... {all except last column}
having count(..)=0
I don't know if it is fast, but it doesn't use temp tables or
not-in-select as requested.
>
>
Finn E. Theodorsen///theodor@inet.uni2.dk///AtCbM///Legend#219605
TEN: Durax. Homepage: http://www.theodor.suite.dk/index.htm
All advertisments sent to the above address will be
treated as requests for computer support, and charged
accordingly. Sending these kind of messages equals an
acceptance of these terms. The minimum fee is $500.