Right outer join with multiple where conditions

Question

I'm having some issues returning any results from my SQL query. I'm pretty sure it's got something to do with the WHERE condition.

I want only certain RULE_ID's to be returned. I identify the RULE_ID by it's RULE_NAME in TABLE1. I then join TABLE1 and TABLE2 and say if the column DATE for the RULE_ID is equal to 05-MAR-20 and the column COLUMN3 is not null, return the RULE_ID.

At the moment I'm not getting any results with this query and I'm not entirely sure why. Any help would be much appreciated!

EDIT: COLUMN3 is in TABLE2 but not in TABLE1. I'm not getting an error, just no results are showing when I think I should be getting some. If I remove the AND (TABLE2.DATE = '05-MAR-20') I get several results. Not sure why this is...

sql_query = """
            SELECT TABLE1.RULE_ID, TABLE2.RULE_ID
            FROM TABLE1
            RIGHT OUTER JOIN TABLE2
            ON (TABLE1.RULE_ID = TABLE2.RULE_ID)
            WHERE (TABLE1.RULE_NAME = 'TEST1' OR TABLE1.RULE_NAME = 'TEST2')  
            AND (TABLE2.DATE = '05-MAR-20')
            AND COLUMN3 IS NOT NULL
            """

Answer 1

Use LEFT JOIN. Much easier to follow the logic ("keep all rows in the first table". Then move appropriate filtering to the ON clause):

SELECT TABLE1.RULE_ID, TABLE2.RULE_ID
FROM TABLE2 LEFT JOIN
     TABLE1
      ON TABLE1.RULE_ID = TABLE2.RULE_ID AND
         TABLE1.RULE_NAME IN ('TEST1', 'TEST2')   
WHERE TABLE2.DATE = DATE '2020-03-05' AND TABLE2.COLUMN3 IS NOT NULL