title: How to join two tables together and return all rows from both tables, and to merge some of their columns into a single column
body: <p>I'm working with SQL Server 2012 and wish to query the following:</p>
  
  <p>I've got  2 tables with mostly different columns. (1 table has 10 columns the other has 6 columns).
  however they both contains a column with ID number and another column of category_name.</p>
  
  <ol>
  <li>The ID numbers may be overlap between the tables (e.g. 1 table may have 200 distinct IDs and the other 900 but only 120 of the IDs are in both).</li>
  <li>The Category name are different and unique for each table.</li>
  </ol>
  
  <p>Now I wish to have a single table that will include all the rows of both tables, with a single ID column and a single Category_name column (total of 14 columns).
  So in case the same ID has 3 records in table 1 and another 5 records in table 2 I wish to have all 8 records (8 rows)</p>
  
  <p>The complex thing here I believe is to have a single "Category_name" column.</p>
  
  <p>I tried the following but when there is no null in both of the tables I'm getting only one record instead of both:</p>
  
  <pre><code>SELECT isnull(t1.id, t2.id) AS [id]
      ,isnull(t1.[category], t2.[category_name]) AS [category name]
  FROM t1
  FULL JOIN t2
      ON t1.id = t2.id;
  </code></pre>
  
  <p>Any suggestions on the correct way to have it done?</p>