Re: how to deteck empty tables in outer joins

> Im trying to do an outerjoin of two tables. The second one might be
> empty. Normally I would use a query like:
>
> CREATE TABLE a ( id INTEGER );
> CREATE TABLE b ( id INTEGER );
>
> SELECT * FROM a,b
> WHERE a.id=b.id
> UNION ALL
> SELECT * FROM a,b
> WHERE a.id NOT IN (b.id)
> ;
>
> If the seconf table is empty the result is null, because the cartesian
> product of table and null is null. What I want is to include a condition
> that if b has no rows then just add null for the value of b ie.
> SELECT *,NULL FROM a; How can I implement this?

There probably are a couple of ways of doing. One is for both queries
to add an "AS" field which return a constant. The constants would be
different for each. This way you can look for those values to know if you
have rows from B or not.

test=# create table t1 (f1 integer);
CREATE
test=# create table t2 (f1 integer);
CREATE

test=# insert into t1 values (1);
INSERT 21565 1

test=# insert into t2 values (2);
INSERT 21566 1

test=# select f1, 't1' as f2 from t1
test-# union
test-# select f1, 't2' as f2 from t2;
 f1 | f2
----+----
  1 | t1
  2 | t2
(2 rows)


Does that help?