title: Join two columns from two different tables
body: <p>I have a problem in joining the two columns from two different tables.</p>
  <p>The Scenario is: I have a table A with 11 columns and another table B with 6 columns.</p>
  <p>There is a column names SAMPLE1 which exists in both the tables.But, SAMPLE2 from 1st table A and ABC from 2nd table B are having the same value but with different column names. Same with SAMPLE3 and DEF. Now I would like to join that columns to a single column (which consists of data from both the tables) and rest of the columns also should appear on that final table.</p>
  <p>Example:</p>
  <p><strong>Table A</strong></p>
  <pre><code>SAMPLE1    SAMPLE2    SAMPLE3 .........SAMPLE 11 (Total 11 columns in this table)
   
   US          75.2       US1_US           NULL
  
   INDIA       71         I3_INDIA         NULL
  
   UK          1851.23    UK1_UK           NULL
  </code></pre>
  <p><strong>Table B</strong></p>
  <pre><code>SAMPLE1      ABC         DEF............. XYZ (Total 6 columns in this table)
  
  CHINA        123.2       C1_CHINA         2
  
  JAPAN        1.1         J1_JAPAN         2
  
  GERMANY      10.2314     G1_GERMANY       2
  
  SINGAPORE    100.22      S1_SINGAPORE     2
  </code></pre>
  <p>Now I would like to see the output like this:</p>
  <p><strong>SomeTable</strong></p>
  <pre><code>SAMPLE1      SOMENAME1    SOMENAME2..SAPMLE 11   ABC    DEF .....  SOMENAME3   
    
     US         75.2        US1_US      NULL       NULL    NULL      NULL
  
     INDIA      71          I3_INDIA    NULL       NULL    NULL      NULL 
  
     UK         1851.23     UK1_UK      NULL       NULL    NULL      NULL 
  
     CHINA      123.2       C1_CHINA    NULL       NULL    NULL       2   
  
     JAPAN      1.1         J1_JAPAN    NULL       NULL    NULL       2 
  
     GERMANY    10.2314     G1_GERMANY  NULL       NULL    NULL       2   
  
     SINGAPORE  100.22     S1_SINGAPORE NULL       NULL    NULL       2 
  </code></pre>
  <p>In short:</p>
  <p>SELECT
  (SAMPLE1(FROM TABLE A) + SAMPLE1(FROM TABLE B)) AS SAMPLE1,
  (SAMPLE2 + ABC) AS SOMENAME1,
  (SAMPLE3 + DEF) AS SOMENAME2,
  A.SAMPLE4, A.SAMPLE5,...,
  B.GHI, B.JKL,...
  (A.SAMPLE11 +B.XYZ) AS SOMENAME3</p>
  <p>I have used union but it didn't work.</p>
  <pre><code>select SAMPLE1,SAMPLE2,SAMPLE3,...,SAMPLE 11 from TABLE A
  UNION 
  SELECT SAMPLE1, ABC, DEF, ...., XYZ FROM TABLE B
  </code></pre>
  <p><strong>Now I am getting an error:</strong></p>
  <blockquote>
  <p>Msg 205, Level 16, State 1, Line 1</p>
  <p>All queries combined using a UNION,
  INTERSECT or EXCEPT operator must have
  an equal number of expressions in
  their target lists.</p>
  </blockquote>
  <p>I have used union, coalesce, full outer join (all the suggestions or answers below)</p>
  <p>The final code will be used in a <code>select</code> statement. How?</p>