Niv Cohen Niv Cohen - 5 months ago 6x
SQL Question

How to join two tables together and return all rows from both tables, and to merge some of their columns into a single column

I'm working with SQL Server 2012 and wish to query the following:

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.

  1. 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).

  2. The Category name are different and unique for each table.

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)

The complex thing here I believe is to have a single "Category_name" column.

I tried the following but when there is no null in both of the tables I'm getting only one record instead of both:

SELECT isnull(, AS [id]
,isnull(t1.[category], t2.[category_name]) AS [category name]
ON =;

Any suggestions on the correct way to have it done?

Thank you


Make your FULL JOIN ON 1=0

This will prevent rows from combining and ensure that you always get 1 copy of each row from each table.

Further explanation:

A FULL JOIN gets rows from both tables, whether they have a match or not, but when they do match, it combines them on one row.

You wanted a full join where you never combine the rows, because you wanted every row in both tables to appear one time, no matter what. 1 can never equal 0, so doing a FULL JOIN on 1=0 will give you a full join where none of the rows match each other.

And of course you're already doing the ISNULL to make sure the ID and Name columns always have a value.