table_reference RIGHT [OUTER] JOIN table_reference [join_condition]

table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

Where 

table_reference 

is defined as:

table_name [[AS] alias] [[USE INDEX (key_list)] | [IGNORE INDEX (key_list)] | [FORCE 

IN 

and 

join_condition 

is defined as:

ON conditional_expr |

USING (column_list)

The 

STRAIGHT_JOIN 

keyword forces the MySQL optimizer to join tables in the 

specified order. In DB2 the join order is always determined by the optimizer. The 

optimizer choices can be limited by changing the default query optimization class 

using SET CURRENT QUERY OPTIMIZATION.

A 

NATURAL

 join, as its name implies, can be invoked when two or more tables

share exactly the same columns needed for a successful equijoin. It is 

semantically equivalent to DB2 INNER JOIN or LEFT OUTER JOIN with the 

respective join criteria specified in the ON clause.

According to the SQL ANSI standard when you need to join tables that share 

more than one column naturally, the JOIN ... USING syntax needs to be used. An 

equivalent join can be composed using the DB2 supported join syntax in the ON 

clause.

Everybody in application and database query development spends a lot of time 

trying to avoid them; however, cartesian products do happen from time to time, 

usually as the result of an equijoin condition that has been missed in a query 

using DB2 syntax. However, one of the advantages of the 

CROSS JOIN

 syntax is 

that a specific keyword is required to create a Cartesian product. Therefore, 

when the 

CROSS JOIN

 syntax is used in your migration project, just code a regular 

join in DB2 with a no join condition in the WHERE clause.

7.1.3  UNION Syntax

In MySQL Version 4.0.0 the newly implemented UNION feature (shown in 

Example 7 5) is very similar to the DB2 syntax, and therefore does not require 

additional discussion.

Example 7 5   UNION syntax in MySQL and DB2

SELECT ...

UNION [ALL | DISTINCT]

SELECT ...

[UNION [ALL | DISTINCT]

SELECT ...]

158 

MySQL to DB2 UDB Conversion Guide