Ora SQL Query: Joining Without References

I am trying to achieve some logic on Oracle by using simple query and feeling stuck on it. The thing is that I cannot use PL-SQL and this is giving me some headached. I have three

Solution 1:

If I'm understanding correctly, you need to use an outer join instead. You can then use COALESCE to return the value associated with "DEF" if T2.REF is NULL:

SELECT T1.CODE,
       T2.CODE, 
       T3.VALUE
FROM TABLE1 T1
    LEFT JOIN TABLE2 T2 ON T1.REF = T2.CODE
    LEFT JOIN TABLE3 T3 ON COALESCE(T2.REF,'DEF') = T3.CODE 
WHERE T1.CODE = XXXXX