Skip site navigation (1) Skip section navigation (2)

how to deteck empty tables in outer joins

From: Einar Karttunen <ekarttun(at)cs(dot)Helsinki(dot)FI>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: how to deteck empty tables in outer joins
Date: 2001-02-01 11:58:58
Message-ID: Pine.LNX.4.30.0102011347430.24468-100000@melkinpaasi.cs.Helsinki.FI (view raw or flat)
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?

- Einar Karttunen




In response to

Responses

pgsql-novice by date

Next:From: Thomas WeholtDate: 2001-02-01 12:12:46
Subject: How can I find table by object-id
Previous:From: Einar KarttunenDate: 2001-02-01 10:40:16
Subject: Re: list of data types

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group