Re: SQL puzzle

Brian,

as none of the docs of Db2 for i, Db2 for z or Db2 LUW are mentioning it, I would call it a defect - or an undocumented feature, that might change without notice in the future. 

https://www.ibm.com/docs/en/i/7.6.0?topic=clause-joined-table

https://www.ibm.com/docs/en/db2-for-zos/13.0.0?topic=clause-joined-table

https://www.ibm.com/docs/en/db2/12.1.x?topic=clause-joined-table

But you are right - just tried it on PUB400 and it works.

But as I said - it's neither SQL standard, nor documented anywhere for Db2 - so I wouldn't use that in production.

Regards,
Daniel



> Am 04.04.2026 um 00:36 schrieb Brian Parkins <[email protected]>:
> 
> And yet it works on V7.5 and V7.6!
> 
> Brian.
> 
>> On 03/04/2026 23:16, Daniel Gross wrote:
>> 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 <[email protected]>:
>>> 
>>> 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: [email protected]
>>> To subscribe, unsubscribe, or change list options,
>>> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
>>> or email: [email protected]
>>> Before posting, please take a moment to review the archives
>>> at https://archive.midrange.com/rpg400-l.
>>> 
>>> Please contact [email protected] for any subscription related questions.
>>> 
> 
-- 
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: [email protected]
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact [email protected] for any subscription related questions.