please try this

SELECT A.BRANCH_ID,A.ADDRESS_NUMBER,A.ADDRESS_SHIP_TO,A.PRODUCT_ID_KEY,A.ISO_LANGUAGE
FROM TABLEA A LEFT OUTER JOIN TABLEB B
ON A.BRANCH_ID=B.BRANCH_ID
AND A.ADDRESS_NUMBER=B.ADDRESS_NUMBER
AND A.ADDRESS_SHIP_TO=B.ADDRESS_SHIP_TO
AND A.PRODUCT_ID_KEY=B.PRODUCT_ID_KEY
AND A.ISO_LANGUAGE=B.ISO_LANGUAGE

 Hi Hemant ,

This wont work.. and I have tried

This works though I'm not sure it will always return what you want (replace the @_A and @_B w/ appropriate table name):

FROM @_A a

ON a.BRANCH_ID=b.BRANCH_ID

AND A.ADDRESS_SHIP_TO=B.ADDRESS_SHIP_TO

AND A.ISO_LANGUAGE=B.ISO_LANGUAGE

You know the part of the join that must always be true - the first column. You handle the second column outside of the join, in the WHERE clause:

SELECT A.col1, B.col3
FROM A
INNER JOIN B ON A.col1 = B.col1
WHERE (A.col2 = B.col2)
OR ((B.col2 IS NULL) AND
NOT EXISTS (SELECT x.* FROM B x WHERE x.col1 = A.col1 AND x.col2 = A.col2))