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

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@lorenso.com

On Thu, Sep 04, 2008 at 03:43:33PM -0500, D. Dante Lorenso wrote:
> 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?

The "ON" clause is just a normal expression, so you can just put a
"TRUE" in there if you want a cross join.  I.e. the following is a
minimal full outer cross join:

  SELECT * FROM foo FULL OUTER JOIN bar ON TRUE;

This still seems a little nasty and I'd prefer to do something like:

  SELECT
    ((SELECT ROW(a,b) FROM foo)).*,
    ((SELECT ROW(c,d) FROM bar)).*;

And have it do the same thing (if you have more than one row returned
you'd get a nice error message and everything).  But I can't seem to get
the syntax right, anyone got a cluebat?


  Sam

On Thu, Sep 4, 2008 at 3:22 PM, Sam Mason <sam@samason.me.uk> wrote:
> On Thu, Sep 04, 2008 at 03:43:33PM -0500, D. Dante Lorenso wrote:
>> 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?
>
> The "ON" clause is just a normal expression, so you can just put a
> "TRUE" in there if you want a cross join.  I.e. the following is a
> minimal full outer cross join:
>
>  SELECT * FROM foo FULL OUTER JOIN bar ON TRUE;

can't you just drop the on clause altogether?

On Thu, Sep 4, 2008 at 2:22 PM, Sam Mason <sam@samason.me.uk> wrote:
> This still seems a little nasty and I'd prefer to do something like:

my idea seems nastiest of all:

SELECT *
     FROM ( SELECT COUNT(*) AS rownbr, A1.col1, A1.col2
              FROM Mytable AS A1
        INNER JOIN Mytable AS A2
                ON A1.pkey < A2.pkey
               AND A1.uid = A2.uid
             WHERE A1.uid = 'abc'
          GROUP BY A1.col1, A1.col2 ) AS Y( rownbr, col1, col2 )
FULL JOIN ( SELECT COUNT(*) AS rownbr, B1.col3, B1.col4
              FROM Mytable AS B1
        INNER JOIN Mytable AS B2
                ON B1.pkey < B2.pkey
               AND B1.uid = B2.uid
             WHERE A1.uid = 'def'
          GROUP BY B1.col3, B1.col4 ) AS Z( rownbr, col3, col4 )
       ON Y.rownbr = Z.rownbr;

but I am pretty sure that this air code works.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Thu, Sep 04, 2008 at 03:26:39PM -0600, Scott Marlowe wrote:
> On Thu, Sep 4, 2008 at 3:22 PM, Sam Mason <sam@samason.me.uk> wrote:
> > The "ON" clause is just a normal expression, so you can just put a
> > "TRUE" in there if you want a cross join.  I.e. the following is a
> > minimal full outer cross join:
> >
> >  SELECT * FROM foo FULL OUTER JOIN bar ON TRUE;
>
> can't you just drop the on clause altogether?

I'm pretty sure you can't, what would be the point of a "CROSS JOIN"
otherwise?  This does raise the point that the SQL standard's CROSS
JOIN is somewhat limited.  It seems to make sense to allow the CROSS
somewhere in any join type and meaning that an ON or USING isn't
expected, making it valid to say:

  SELECT * FROM foo FULL OUTER CROSS JOIN bar;

Admittedly, there aren't too many use cases for this!  But it would make
things a bit more regular.


  Sam

On Thu, Sep 4, 2008 at 2:54 PM, Sam Mason <sam@samason.me.uk> wrote:
>  SELECT * FROM foo FULL OUTER CROSS JOIN bar;
>
> Admittedly, there aren't too many use cases for this!  But it would make
> things a bit more regular.

 SELECT * FROM foo, bar;

Isn't this have the same results, but with out the cross join?

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Thu, Sep 04, 2008 at 02:58:39PM -0700, Richard Broersma wrote:
> On Thu, Sep 4, 2008 at 2:54 PM, Sam Mason <sam@samason.me.uk> wrote:
> >  SELECT * FROM foo FULL OUTER CROSS JOIN bar;
> >
> > Admittedly, there aren't too many use cases for this!  But it would make
> > things a bit more regular.
>
>  SELECT * FROM foo, bar;
>
> Isn't this have the same results, but with out the cross join?

I hope not!  Try running:

  SELECT * FROM
    (SELECT 1 AS a WHERE FALSE) f,
    (SELECT 2 AS b            ) b;

and then try:

  SELECT * FROM
    (SELECT 1 AS a WHERE FALSE) f FULL OUTER JOIN
    (SELECT 2 AS b            ) b ON TRUE;

I hope you get zero rows back from the first query, and a single row
back from the second with a NULL value in the "a" column.


  Sam