SELECT  ADD.address_uid
       ,ADD.unit_number
       ,ADD.street_number
       ,ADD.street_suffix
       ,ADD.street_name
       ,LOC.locality_uid
       ,LOC.locality_name
       ,LOC.state
       ,LOC.postcode
       ,ALS.locality_name alias_locality_name
       ,ALS.state         alias_state
       ,ALS.postcode      alias_postcode
FROM address_detail ADD
      LEFT JOIN locality LOC ON ADD.locality_uid = LOC.locality_uid AND 
LOC.locality_name = 'FREDS TOWN'
      LEFT JOIN locality_alias ALS ON ALS.primary_locality_uid = 
LOC.locality_uid AND ALS.locality_name = 'FREDS TOWN'
WHERE ADD.street_name LIKE 'MAIN%
AND (ALS.state IS NOT NULL OR LOC.state IS NOT NULL);

Moving the locality name check up into the join condition leaves NULL 
columns if no matching locality or alias, but if there is a matching 
ADDRESS_DETAIL.

The result set should contain the address, the locality, and the alias, if 
either one exists.  If neither exist, then the result set would be empty due 
to the last condition.

If more than one 'FREDS TOWN' alias exists for the address locality, then 
the above will have one row for each alias.  I don't think it would make 
sense that there'd be multiple exact matches on aliases, but if you were 
allowing partial match, then there could be FREDBURG and FREDTOWN, all 
matching FRED%.  But this is, I think, a different question.

--
Larry DiGiovanni