title: Create a flag in a left join
body: <p>I am trying to create a new column (a sort of identifier flag) for the "Null" rows resulting of my following left join :</p>
  
  <pre><code>with CTE (...) as (
  ... unrelated code
  ) select * from CTE
  
  left join (select columnID from table1) Pu
  on CTE.columnID = Pu.columnID
  
  left join (select case when bz.column2 is null then 'null test is working' else columnID2, column2 end FROM table2) Bz
  ON CTE.columnID2 = Bz.columnID2
  </code></pre>
  
  <p>This code is working properly when I don't try to use a 'case when'. Actually, you could very well ignore the first left join.</p>
  
  <p>My purpose would be to be able <strong>test the left join result</strong> while doing it, and act depending on the result :</p>
  
  <p>If the left join result give <strong>a null row : creation of a flag column</strong> for the row,</p>
  
  <p>If the left join result give <strong>a normal row : the left join is done normally</strong>, and the flag column is empty (as I suspect it cant be un existent).</p>
  
  <p>I'd be glad if you could give me a hand!</p>
  
  <p>EDIT : tables example:</p>
  
  <pre><code>CTE
  | columnID | columnID2 | InformationsCTE | 
  |    ab    |    mp     |    randominfo1  |
  |    ac    |    ma     |    randominfo2  |
  |    ae    |    me     |    randominfo3  |
  |    ad    |    mb     |    randominfo4  |
  
  table2
  | columnID2 | InformationsTable2  | 
  |    mp     |     randominfo5     |
  |    ma     |     randominfo6     |
  |    me     |     randominfo7     |
  </code></pre>
  
  <p>Result after the second left join :</p>
  
  <pre><code>new CTE
  | columnID | columnID2 | InformationsCTE |  InformationsTable2|  FLAG |
  |    ab    |    mp     |    randominfo1  |     randominfo5    |   OK  |
  |    ac    |    ma     |    randominfo2  |     randominfo6    |   OK  |
  |    ae    |    me     |    randominfo3  |     randominfo7    |   OK  |
  |    ad    |    mb     |    randominfo4  |        NULL        |   NOK |
  </code></pre>