title: What's the efficient way to select correct data from this table?
body: <p>Here is the table I have</p>
  <pre><code>ID  Key     Value   Lang 
  1   key1    text1   en
  2   key2    text2   en
  3   key3    text3   en
  4   key1    chu1    vi
  5   key2    chu2    vi
  </code></pre>
  <p>When select the &quot;vi&quot; lang, missing keys will be selected from the &quot;en&quot; lang
  Basically the result should be</p>
  <pre><code>4   key1    chu1    vi
  5   key2    chu2    vi
  3   key3    text3   en
  </code></pre>
  <p>Is there an efficient way to get the result without complex query like this?</p>
  <pre><code>WITH cte_data
  AS (
      SELECT ID
          ,Key
          ,Value
          ,Lang
      FROM tblSysText
      WHERE Lang = 'vi'
      )
  SELECT ID
      ,Key
      ,Value
      ,Lang
  FROM cte_data
  
  UNION
  
  SELECT ID
      ,Key
      ,Value
      ,Lang
  FROM tblSysText
  WHERE Lang = 'en'
      AND Key NOT IN (
          SELECT Key
          FROM cte_data
          )
  </code></pre>
  <p>Thanks.</p>
  <hr />
  <p>Update the data set
  Here is what similar to the real data
  Basically the table contains the text of several different languages</p>
  <p>What I want is to select all the keys and values of a specific language.
  Then select the missing keys and values from the &quot;en&quot; language</p>
  <pre><code>ID  Key     Value   Lang 
  1   key1    text1   en
  2   key2    text2   en
  3   key3    text3   en
  4   key1    chu1    vi
  5   key2    chu2    vi
  6   key4    chu4    vi
  7   key4    text4   en
  8   key5    text5   en
  9   key5    s5      ye
  10  key6    s6      ZW
  </code></pre>
  <p>Here is the expected output when select for &quot;vi&quot; language</p>
  <pre><code>ID  Key     Value   Lang
  4   key1    chu1    vi
  5   key2    chu2    vi
  6   key4    chu4    vi
  3   key3    text3   en
  8   key5    text5   en
  </code></pre>