Re: SQL puzzle

[Daniel Gross <daniel@xxxxxxxx>]

The SQL standard AND the IBM Db2 references define that for an OUTER JOIN the direction LEFT, RIGHT or FULL is mandatory - but the keyword OUTER is optional. 

So there might be RDBMSs where you can write this syntax - but IBM Db2 isn't one - and never was. And the SQL standard doesn't define such syntax. 

HTH 
Daniel


> Am 03.04.2026 um 23:59 schrieb Brian Parkins <goodprophet.bp@xxxxxxxxx>:
>
> Here's a conundrum. Consider the following SQL statement:
>
> select t1_key, t1_value, t1_foreign, t2_key, t2_value
>   from table1 outer join table2
>     on t1_foreign = t2_key
>   order by t1_key;
>
> If I read the SQL Reference (syntax diagram) correctly, this is wrong. It must be coded as LEFT OUTER JOIN, (or LEFT JOIN). And yet, my memory tells this has always been acceptable coding, with LEFT as the default. I fact, I tried it on V7.5 and V7.6 systems - and it works just fine
>
> I also tried it on a V7.4 system - and it fails with:
> SQL0199 Keyword OUTER not expected. Valid tokens: FOR USE SKIP WAIT WITH FETCH LIMIT ORDER UNION EXCEPT OFFSET.
>
> So, my conundrums are:
> 1). It is OK to code OUTER JOIN, (and LEFT is assumed)?
> 2). Is there a PTF required on the V7.4 system - or are there problems on the V7.5 and V7.6 systems?
> 3). Is the SQL Reference incorrect?
>
> (If anyone wishes to check this out I have a script I can paste here.)
>
> TIA,
> Brian.
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.