Re: SQL puzzle

Now this makes finally sense - I was about to try which kind of JOIN would be the result of that syntax, because no join direction was given.

But as I always use the optional AS keyword, I haven't seen, that OUTER can be interpreted as an correlation name.

Thanks for the clarification.

Am 04.04.2026 um 20:06 schrieb Sue Romano <slromano@xxxxxxxxxx>:

This is an interesting observation. The syntax is NOT doing what you intend. OUTER is being recognized as the table correlation name for TABLE1. The join is then interpreted as an INNER join.



As the SQL syntax evolves, situations like this can occur. I can't find anything specific that changed in IBM i 7.5 to trigger this new interpretation. While we could restrict OUTER as a table correlation name, that restriction would break existing applications that use it anywhere as a table correlation name. I will have a discussion with others on the Db2 for i development team to decide whether this merits an incompatible restriction.

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?

Sue Romano
Db2 for IBM i development

--
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.