Merge one table to another

Question

I have a table lets say Table1 with columns Point_ID, Size, Age, Dist, Treatment and Table 2 with columns Point_ID, Size, Age, Dist, Treatment, Path, Location. Table 2 has similar data for Point_ID, Size, Age, Dist, Treatment as Table1 for some values.

I want to merge extra columns of Table 2 in Table1 such that there is no duplication and the resulted table looks likes "Point_ID, Size, Age, Dist, Treatment, Path, Location." There is data in Table1 in columns Point_ID, Size, Age, Dist, Treatment which does not have path and location, so the path and location should be null for them. What SQL query to be performed?.

Answer 1

Assuming Point_ID is a primary key or unique key shared by the two tables, then you can do this to create a new table Table3 with the data you specified:

CREATE TABLE Table3 AS 
  SELECT t1.Point_ID, t1.Size, t1.Age, t1.Dist, t1.Treatment, t2.Path, t2.Location
  FROM Table1 t1
    LEFT OUTER JOIN Table2 t2 ON t1.Point_ID = t2.Point_ID
  UNION 
  SELECT Point_ID, Size, Age, Dist, Treatment, Path, Location 
  FROM Table2;

For any row with Point_ID in Table1 that doesn't exist in Table2 the Path and Location columns will be NULL. If it does exist, the Path and Location columns will have the values from Table2. For any row with Point_ID in Table2 that doesn't exist in Table1, the whole row will be added to the new table Table3.