Обсуждение: RE: [HACKERS] Outer Joins (and need CASE help)

> > Thomas, do you need help on outer joins?
> 
> Yes. I'm going slowly partly because I get distracted with other
> Postgres stuff like docs, and partly because I don't understand all of
> the pieces I'm working with.
> 
> I've identified the place in the MergeJoin code where the null filling
> for outer joins needs to happen, and have the "merge walk" code done.
> But I don't have the supporting code which actually would know how to
> null-fill a result tuple from the left or right. I thought 
> you might be
> interested in that?
> 
> I've done some work in the parser, and can now do things like:
> 
> postgres=> select * from t1 join t2 using (i);
> NOTICE:  JOIN not yet implemented
> i|j|i|k
> -+-+-+-
> 1|2|1|3
> (1 row)
> 
> But this is just an inner join, and the result isn't quite right since
> the second "i" column should probably be omitted. At the moment I
> transform it from the syntax above into existing parse nodes, and
> everything from there on works.

I could be wrong (I don't have a copy of the standard), but I don't
believe that the above syntax follows the standard.  Let me know if I'm
wrong, but my understanding of the syntax would be more like:SELECT * FROM t1 JOIN t2 ON (t1.i = t2.i);
with the same result set as you listed (t2.i isn't suppressed).
This would have a difference in approach from the above.  If I wanted to
join on columns with different names I couldn't use your syntax (as one
example).-DEJ

> I could be wrong (I don't have a copy of the standard), but I don't
> believe that the above syntax follows the standard.  Let me know if 
> I'm wrong, but my understanding of the syntax would be more like:
>  SELECT * FROM t1 JOIN t2 ON (t1.i = t2.i);
> with the same result set as you listed (t2.i isn't suppressed).
> This would have a difference in approach from the above.  If I wanted 
> to join on columns with different names I couldn't use your syntax (as 
> one example).

The standard allows both syntaxes; USING is simpler to type, and ON is
more general, as you point out.

In fact, the standard is annoyingly helpful in allowing multiple ways to
write the same query. Makes the parsing and parse tree transformation
more complicated :(
                         - Tom