Table 1 (records):

ORDER_NUMBER | Email Address
-------------|--------------
FX0422	     | JUNK@what.com
GF2304	     | [null]
0Z3204       | [null]
AS0234       | junk@improper.com
AO3240       | [null]

table 2 (master list):
ORDER_NUMBER | SSN
-------------|-----
FX0422       | 123456789
GF2304	     | 123451234
0Z3204       | 098765432
AS0234       | 098760987
A03240       | 456745678

table 3 (personnel list d):
SSN          |   EMAIL_ADDRESS
-------------|-----------------
123456789    | BOB@accounting.com
123451234    | jane@company.com

table 4 (personnel list l):
SSN 	    |  EMAIL_ADDRESS
------------|----------------
098760987   |  JOHN@company.com
456745678   |  DILDO@company.com
098765432   |  WOMBAT@company.com

Desired Output:
ORDER NUMBER | EMAIL ADDRESS
-------------|-----------------
FX0422	     | 	BOB@accounting.com
GF2304	     | 	jane@company.com
0Z3204       |  WOMBAT@company.com
AS0234       |  JOHN@company.com
AO3240       | 	DILDO@company.com


select r.order_number, m.ssn
from table1 r left outer join table2 m
	on m.ssn = (select SSN from table3
		    UNION
		    select SSN from table4)