Merging two tables

Question

I have two tables with the following columns (identical columns in both tables):

1. FunctionName,
2. FrequencyCount

I want to merge these two tables into a final table with columns:

1. Function Name
2. Base Frequency count
3. Compared Frequency count
4. Delta of Frequency count

Merge operation should happen as follows:

• If [FunctionName] is in Table1 and NOT in Table2,

  [Base Frequency Count] = Table1.[FrequencyCount]
  [Compared Frequency Count] = 0
  [Delta of Frequency Count] = Table1.[FrequencyCount]
  

• If [FunctionName] is in Table2 and NOT in Table1,

  [Base Frequency Count] = 0         
  [Compared Frequency Count] = Table2.[FrequencyCount]
  [Delta of Frequency Count] = Table2.[FrequencyCount]
  

• If [FunctionName] is both in Table1 and Table2,

  [Base Frequency Count] = Table1.[FrequencyCount]         
  [Compared Frequency Count] = Table2.[FrequencyCount]
  [Delta of Frequency Count] = Table1.[FrequencyCount]-Table2.[FrequencyCount]
  

It is desirable that the query has good performance with minimum no. of joins. It would be great if someone can give good pointers.

Answer 1

SELECT *
  INTO new_table_name 
  FROM (SELECT t.frequencycount 'Base Frequency Count',
               0 'Compared Frequency Count',
               t.frequencycount 'Delta of Frequency Count'
          FROM TABLE1 t
          JOIN TABLE2 t2 ON t2.functionname != t.functionname
        UNION
        SELECT 0 'Base Frequency Count',
               t2.frequencycount 'Compared Frequency Count',
               t2.frequencycount 'Delta of Frequency Count'
          FROM TABLE2 t2
          JOIN TABLE1 t ON t.functionname != t2.functionname
        UNION
        SELECT t.frequencycount 'Base Frequency Count',
               t2.frequencycount 'Compared Frequency Count',
               t.frequencycount - t2.frequencycount 'Delta of Frequency Count'
          FROM TABLE1 t
          JOIN TABLE2 t2 ON t.functionname = t2.functionname)

Answer 2

Not sure if this would be slower than rexem's solution (I didn't benchmark it). But this might be easier for you to read. Performance could be significantly improved by Selecting based on Primary Keys instead of FunctionName as well.

--TABLE 1
SELECT FunctionName, FrequencyCount AS Base, 0 AS Compared, FrequencyCount AS Delta
FROM TableOne
WHERE FunctionName NOT IN (SELECT FunctionName FROM TableTwo)
UNION

--TABLE 2
SELECT FunctionName, 0 AS Base, FrequencyCount AS Compared, FrequencyCount AS Delta
FROM TableTwo
WHERE FunctionName NOT IN (SELECT FunctionName FROM TableOne)
UNION

--DELTA
SELECT FunctionName, 
(SELECT FrequencyCount FROM TableOne WHERE FunctionName = DeltaTable.FunctionName) AS Base,
(SELECT FrequencyCount FROM TableTwo WHERE FunctionName = DeltaTable.FunctionName) AS Compared,
(SELECT FrequencyCount FROM TableOne WHERE FunctionName = DeltaTable.FunctionName) - (SELECT FrequencyCount FROM TableTwo WHERE FunctionName = DeltaTable.FunctionName) AS Delta
FROM TableOne DeltaTable
WHERE FunctionName IN (SELECT FunctionName FROM TableOne)
AND FunctionName IN (SELECT FunctionName FROM TableTwo)

Modified Delta

--DELTA
SELECT One.FunctionName, 
One.FrequencyCount AS Base,
Two.FrequencyCount AS Compared,
One.FrequencyCount - Two.FrequencyCount AS Delta
FROM TableOne One
INNER JOIN TableTwo Two
    ON One.FunctionName = Two.FunctionName

Answer 3

SELECT ISNULL(fn.FunctionName, fc.FunctionName) AS FunctionName, 
       ISNULL(fn.FrequencyCount, 0) AS BaseFrequency,
       ISNULL(fc.FrequencyCount, 0) AS ComparedFrequencyCount,
       COALESCE((fn.FrequencyCount - fc.FrequencyCount), fn.FrequencyCount, fc.FrequencyCount) AS DeltaOfFrequencyCount
INTO FinalTable
FROM FunctionName fn FULL OUTER JOIN FrequencyCount fc ON fn.FunctionName = fc.FunctionName

Note that the COALESCE will result in a null in the first expression (moving on to the next in the chain) if either fn.FrequencyCount or fc.FrequencyCount is null (in SQL, value - null = null).