Full outer join? Cross product? How to blend two queries into single row?

From: "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com>
To: Postgres-General <pgsql-general(at)postgresql(dot)org>
Subject: Full outer join? Cross product? How to blend two queries into single row?
Date: 2008-09-04 20:43:33
Message-ID: 48C04875.1000708@lorenso.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

All,

I want to do something simple and the terminology is slipping me. I
want to execute two separate queries that should return 0 or 1 rows and
I want to join the results of those queries into a single row.

SELECT a.col1, a.col2
FROM mytable a
WHERE a.uid = 'abc';

SELECT b.col3, b.col4
FROM mytable b
WHERE b.uid = 'def';

But somehow get a result like this:

SELECT a.col1, a.col2, b.col3, b.col4
FROM mytable a, mytable b
WHERE a.uid = 'abc'
AND b.uid = 'def';

That query works when both a.uid and b.uid match but I want to get
results even when a.uid matches but b.uid does NOT match and vice versa.
Just make a.col1 and a.col2 NULL when a.uid does not match or make
b.col3 and b.col4 NULL when b.uid does not match. All 4 can be NULL or
no rows returned if no matches.

I came up with this query that works, but seems overly complicated:

SELECT a.col1, a.col2, b.col3, b.col4
FROM
(SELECT col1, col3, TRUE AS join_column
FROM mytable
WHERE uid = 'abc') a
FULL OUTER JOIN
(SELECT col3, col4, TRUE AS join_column
FROM mytable
WHERE uid = 'def') b
ON (a.join_column = b.join_column);

Is this how to do it, or is there a simpler syntax I'm missing? What's
the formal terminology for what I'm doing here? Is there a way to
specify a FULL OUTER JOIN without naming the join column since I don't
need one?

-- Dante

----------
D. Dante Lorenso
dante(at)lorenso(dot)com

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2008-09-04 21:00:23 Re: Postgres does not start, gives no error
Previous Message Richard Broersma 2008-09-04 20:43:32 Re: How to test something using ROLLBACK TRANSACTION