Join two tables

Question

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

• FunctionName
• Frequency count

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

• Function name
• Base frequency
• Comparative frequency
• Delta reference frequency

The merge operation should be performed as follows:

• If [FunctionName] is in table 1 and NOT in table 2,

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

• If [FunctionName] is in table2 and NOT in table 1,

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

• If [FunctionName] is in table 1 and table 2,

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

It is desirable that the request has good performance with a minimum value. associations. It would be great if someone could give good directions.

Answer 1

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

, COALESCE ( ), fn.FrequencyCount, fc.FrequencyCount null ( SQL, - null = null).

Answer 2

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 3

Not sure if this will be slower than rexem solution (I have not tested it). But it may be easier for you to read. Efficiency can be greatly improved by choosing based on primary keys instead of FunctionName.

--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 p>

--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