MySQL LEFT JOIN .. ON .. CLAUSE

Im currently porting a MySQL db to another DBMS. I encountered the following query with the following structure.

SELECT ... FROM table1 AS tb1 
      LEFT JOIN Table2 AS tb2 
             ON tb1.x = tb2.x 
             AND tb2.y = 2 AND tb2.z = 3 ...

My target DBMS (DB2) "AND tb2.y =开发者_如何学编程 2 AND tb2.z = 3" does not allow the following structure so i moved it to the WHERE clause. Unfortunately after moving it, the query doesn't seem to return the same rows from MySQL.

In an outer join, when moving predicates from the ON clause to the WHERE clause, you have to allow for records where the join produces no match, and therefore columns from the outer table will all be NULL.

I have no DB2 experience so there may be other subtleties that I'm not aware of, but this is how I would rewrite the query. Checking for a NULL in the join column is a direct indicator of whether the join found a match.

SELECT ... FROM table1 AS tb1 
      LEFT JOIN Table2 AS tb2 
             ON tb1.x = tb2.x 
      WHERE tb2.x IS NULL
         OR (tb2.y = 2 AND tb2.z = 3)

Try this:

SELECT ... 
    FROM table1 AS tb1 LEFT JOIN Table2 AS tb2 
        ON tb1.x = tb2.x ,
        tb2.y = 2 ,
        tb2.z = 3 ... 

Have you tried converting it to a RIGHT JOIN?

SELECT ... 
FROM Table2 AS tb2 
RIGHT JOIN ON table1 AS tb1 tb1.x = tb2.x 
WHERE tb2.y = 2 AND tb2.z = 3 ...

I think DB2 does not allow a table alias with as (but I don't have a DB2 at hand right now)

Try

SELECT ... 
FROM table1 tb1 
    LEFT JOIN Table2 tb2 ON tb1.x = tb2.x AND tb2.y = 2 AND tb2.z = 3 

(Note the missing as after the table names)

Edit: I just checked the DB2 manual, and the AS seems to be valid....
(But I'll leave the post for now)

apparently DB2 will only use column names in comparisons. Perhaps you could somehow artificially create columns that have these values in the joining table? Are you getting error -338?