To add to Vikash answer:
Don't use * in the SELECT clause. For example if a column is added to one of the tables, your query would fail since in UNION all the select statements must return exactly same number of columns and same data types
Another thing. Don't use literals in the query if you're putting it in a program, use bind variables instead. If you're using ODP.NET, see:
OracleParameter[
^]. Your query would then look something like:
Select col1, col2 from SPOHON where GEOID=:geoid
UNION
Select col1, col2 from APOHON where GEOID=:geoid
UNION
Select col1, col2 from LPOHON where GEOID=:geoid