简单规则:从不在FROM子句中使用逗号. 始终使用明确的，正确的JOIN语法.

Simple rule: Never use commas in the FROM clause. Always use explicit, proper JOIN syntax.

这可能不会改变查询的性能，但这是一种更为典型的编写方式.我很确定目的是:

This probably doesn't change the performance of the query, but it is a much more typical way to write it. I'm pretty sure the intention is:

SELECT l.*, tg.*
FROM RefTable tg JOIN
     InputTbl l
     ON tg.areascheme = l.areascheme AND tg.countrycode = l.strareabriefnamel1  
WHERE (l.strareabriefnamel2 IS NULL OR tg.areacode = l.strareabriefnamel2) AND
      (l.strareabriefnamel3 IS NULL OR tg.subareaname  = l.strareabriefnamel3) AND
      (l.strareabriefnamel4 IS NULL OR tg.postalname = l.strareabriefnamel4)
  option( MAXDOP 0 ); 

从优化此查询开始的地方是索引.我建议:RefTable(areascheme, countrycode)和InputTbl(areascheme, strareabriefnamel1).

The place to start with optimizing this query is with indexes. I would suggest: RefTable(areascheme, countrycode) and InputTbl(areascheme, strareabriefnamel1).