[SQL] Outer Joins

[Marc Rohloff]

I've been looking at the open-source databases for a project I am working on and while 
reading about Postgres I saw that they do not support outer joins yet. I was intrigued 
by their solution of using a union query.

Something Like:
select a.col1, b.col2 from a,b where a.col1 = b.col2
union
select a.col1, NULL from a where a.col1 not in (select b.col2 from b)

But I was wondering if the following would work (it does in some other databases)

select a.col1, b.col2 from a,b 
where a.col1 = b.col2
   or  b.col2 is null

or maybe even

select a.col1, b.col2 from a,b 
where a.col1 = b.col2
   or  a.col1 not in (select b.col2 from b)

These would seem to be far more efficient than a union query
(I would try this but I don't have a Unix box at the moment to install PostgreSQL on!)

Marc Rohloff

Re: [SQL] Outer Joins

[Marc Rohloff]

>> select a.col1, b.col2 from a,b
>> where a.col1 = b.col2
>>or  a.col1 not in (select b.col2 from b)

>This would work, but it would be *much* slower than a UNION query.  "Not
>In" queries are perhaps the slowest you can run; see the earlier thread
>"Query Problem" for a discussion.  UNION queries are, in fact, very fast
>... just awkward to code and manipulate.

Why should this be slower since the UNION Query still has an identical not in clause?
This is far easier (for me) to read.

Marc

[SQL] Returning Recordsets from Stored-procs

[Marc Rohloff]

Is there anyway to return a recordset from a Stored Procedure in Postgres so that it 
can be used as a type of view or select?
I know that you can do this in Interbase or MS-SQL.

I have seen that you can return a complete record but that's not really the same thing.

Marc Rohloff