SELECT first value from 3 tables that is not null
There are couple ways of get what you want. From the top of my head I can think about version with UNION, FULL JOIN, and LEFT JOIN. The latter seems to me as most preferable from readability and performance point of view : 

SELECT NVL(t1.value,NVL(t2.value,t3.value)) as value
-- Or use coalesce instead of NVLs :
--SELECT COALESCE(t1.value,t2.value,t3.value) as value
FROM
(SELECT 'name_to_search' as name FROM DUAL)a
LEFT JOIN table1 t1 ON (t1.name= a.name)
LEFT JOIN table1 t2 ON (t2.name= a.name)
LEFT JOIN table1 t3 ON (t3.name= a.name)