Как отметил Виктор, проблема связана с псевдонимом. Этого можно избежать, поставив выражение непосредственно в предложение WHERE x IN y:
SELECT `users`.`first_name`,`users`.`last_name`,`users`.`email`,SUBSTRING(`locations`.`raw`,-6,4) AS `guaranteed_postcode`
FROM `users` LEFT OUTER JOIN `locations`
ON `users`.`id` = `locations`.`user_id`
WHERE SUBSTRING(`locations`.`raw`,-6,4) NOT IN #this is where the fake col is being used
(
SELECT `postcode` FROM `postcodes` WHERE `region` IN
(
'australia'
)
)
Однако, я думаю, это очень неэффективно, так как подзапрос должен выполняться для каждой строки внешний запрос.