DB2 Join shortcuts - isn't there a special way you can join if the column name is the SAME in the 2 tables being joined

That's called a Natural Join, but I can't recall any popular DBMS supporting that. And it is always an Inner Join by definition.

whats the specific syntax because it's not the typical ...
from t1 left join t2 on t2.mycol = t1.mycol

I don't get what you ask for.

Left Join is one kind of Outer Join, allowing one side (the right in this case) to be not matched and still return rows.

The Natural Join has theoretical syntax descriptions, but no implementation in DB2 and many more DBMS. It is "implemented" manually by enumerating the disjunct columns of at least one of the tables, so no change for you. And as said, Natural Join cannot be an Outer Join (left or right or full).

"Using" is what I couldn't remember!  Thanks Dave :-)

Thanks for the effort Qlemo.

I realize this question is already answered, but here's another neat trick for shortening your queries that works beautifully on DB2 for i (v6.1) :

Instead of this:

SELECT someStuff
  FROM Table1 a
  left outer join Table2 b
    on b.column1 = a.column1
   and b.column2 = a.column2
  left outer join Table3 c 
    on c.column1 = b.column1
   and c.column2 = b.column2
   and c.column3 = b.column3
   and c.column4 = b.column4

Select allOpen in new window

You can do this:

SELECT someStuff
  FROM Table1 a
  left outer join Table2 b 
    on (b.column1,b.column2) = (a.column1,a.column2)
  left outer join Table3 c
    on  (c.column1,c.column2,c.column3,c.column4) =
        (b.column1,b.column2,b.column3,b.column4)

Select allOpen in new window

HTH,
DaveSlash

VERY nice Dave!!!  I hope I remember this long enough to use it and turn it into a habit.  Thanks for the extra 'tip'!