title: Issue with full outer join in SQL Server
body: <p>When I use below 2 queries separately I get output successfully </p>
  
  <p>Query #1</p>
  
  <pre><code>DECLARE @ID VARCHAR(1000) 
  SET @ID = '' 
  
  SELECT @ID = @ID + CAST(int_ID AS VARCHAR(1000)) 
  FROM MATRIX_RELATIONSHIP 
  
  SELECT COUNT(int_ID) [Count], YEAR(dt_5841_OF) [date] 
  FROM TRANS_IDENTIFICATIONS 
  WHERE smallint_209_ME = 4 
    AND CHARINDEX(CONVERT(VARCHAR, int_ID), ('''' + Replace((@ID),',',''',''') + '''')) &gt; 0 
  GROUP BY YEAR(dt_5841_OF);
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>        Count | year
            02    2016
  </code></pre>
  
  <p>Query #2</p>
  
  <pre><code>SELECT 
      COUNT(*) [Count1], YEAR(dt_modifiedOn) [date]
  FROM 
      MATRIX_RELATIONSHIP 
  GROUP BY 
      YEAR(dt_modifiedOn);
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>        Count | year
            02    2016
  </code></pre>
  
  <p>But when I use both queries to form a <code>full outer join</code> something like below:</p>
  
  <pre><code>SELECT 
      tab1.Count, tab2.Count1, tab1.date 
  FROM
      (DECLARE @ID VARCHAR(1000) 
       SET @ID = '' 
  
       SELECT @ID = @ID+CAST(int_ID AS VARCHAR(1000)) 
       FROM MATRIX_RELATIONSHIP 
  
       SELECT COUNT(int_ID) [Count], YEAR(dt_5841_OF) [date] 
       FROM TRANS_IDENTIFICATIONS 
       WHERE smallint_209_ME = 4 
         AND CHARINDEX(convert(varchar,int_ID),  ('''' + Replace((@ID),',',''',''') + '''')) &gt; 0 
       GROUP BY YEAR(dt_5841_OF)) tab1
  FULL OUTER JOIN 
      (SELECT COUNT(*) [Count1], YEAR(dt_modifiedOn) [date]
       FROM MATRIX_RELATIONSHIP 
       GROUP BY YEAR(dt_modifiedOn)) tab2 ON tab1.date = tab2.date;
  </code></pre>
  
  <p>I get these errors:</p>
  
  <blockquote>
    <p>Msg 156, Level 15, State 1, Line 2<br>
    Incorrect syntax near the keyword 'DECLARE'.</p>
    
    <p>Msg 102, Level 15, State 1, Line 4<br>
    Incorrect syntax near ')'.</p>
    
    <p>Msg 102, Level 15, State 1, Line 7<br>
    Incorrect syntax near 'tab2'.</p>
  </blockquote>
  
  <p>Expected result</p>
  
  <pre><code>Count | Count1 | year 
    02      02     2016
  </code></pre>
  
  <p><strong>EDIT</strong></p>
  
  <p>I have tried placing declare and set outside the query too but still there the issue persists</p>
  
  <pre><code>DECLARE  @ID VARCHAR(1000) SET @ID=''
   SELECT tab1.Count,tab2.Count1,tab1.date from
  ( SELECT @ID= @ID+CAST(int_ID AS VARCHAR(1000)) 
  from MATRIX_RELATIONSHIP SELECT COUNT(int_ID)[Count],YEAR(dt_5841_OF)[date] FROM TRANS_IDENTIFICATIONS WHERE smallint_209_ME=4 and CHARINDEX(convert(varchar,int_ID),  ('''' + Replace((@ID),',',''',''') + '''')) &gt; 
  0 GROUP BY YEAR(dt_5841_OF)) tab1
  FULL OUTER JOIN 
  (SELECT COUNT(*)[Count1],YEAR(dt_modifiedOn)[date]
   FROM MATRIX_RELATIONSHIP GROUP BY YEAR(dt_modifiedOn)) tab2
   ON tab1.date=tab2.date;
  </code></pre>
  
  <p>Which leads to another set of errors:</p>
  
  <blockquote>
    <p>Msg 102, Level 15, State 1, Line 3<br>
    Incorrect syntax near '='.</p>
    
    <p>Msg 102, Level 15, State 1, Line 5<br>
    Incorrect syntax near ')'.</p>
    
    <p>Msg 102, Level 15, State 1, Line 8<br>
    Incorrect syntax near 'tab2'.</p>
  </blockquote>