Select all data from two tables with partial common data
Check out this db&lt;>fiddle for an example. When running your example SELECT statement, it returned an error because the second and third SELECT in the UNIONs reference aliases from the other SELECT statements. 

I'm not sure if this is is something you can do in PL/SQL (I have an MSSQL background), but the code in the fiddle returns the expected result set.