Pulling multiple values from select statement in SQL

Question

I am trying to achieve this in a more efficiently and elegantly

SELECT
  MD.*,
  (SELECT City FROM PostcodeData WHERE MD.Postcode = Postcode) [City],
  (SELECT State FROM PostcodeData WHERE MD.Postcode = Postcode) [State],
  (SELECT Areacode FROM PostcodeData WHERE MD.Postcode = Postcode) [Areacode]
FROM MemberDetails AS MD

I can obviously INNER JOIN the two tables ON Postcode but I am having issues when MemberDetail.postcode does not exist in PostcodeData.postcode. In such cases I would still like to SELECT those members but with NULL results for City, State, and Areacode. This is being achieved in the current query but it is very inefficient. Any ideas would be appreciated. Cheers!

Answer 1

Just use a LEFT JOIN:

SELECT
  MD.*,
  P.City,
  P.State,
  P.Areacode
FROM MemberDetails AS MD
   LEFT JOIN PostcodeData  P ON MD.Postcode = P.Postcode