Re: how to deteck empty tables in outer joins

From: Francisco Reyes <fran(at)reyes(dot)somos(dot)net>
To: Einar Karttunen <ekarttun(at)cs(dot)Helsinki(dot)FI>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: how to deteck empty tables in outer joins
Date: 2001-02-02 05:23:12
Message-ID: Pine.BSF.4.32.0102020014290.1877-100000@zoraida.reyes.somos.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

> 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?

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Francisco Reyes 2001-02-02 05:25:45 RE: Importing Excel Spreadsheets into PostgreSQL
Previous Message Francisco Reyes 2001-02-02 05:13:39 Re: explain plan