Jeff Moden (9/7/2012)

---

Dwain,

You made the same mistake that I originally made. Try your code on the following test data and marvel at the pain of an accidental CROSS JOIN.

DECLARE @table1 TABLE (id INT, a INT)

DECLARE @table2 TABLE (id INT, b INT)

INSERT INTO @table1

SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL

SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL

SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL

SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL

SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL

SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL

SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL

SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL

SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9 UNION ALL

SELECT 1,6 UNION ALL SELECT 2,7 UNION ALL SELECT 3,8 UNION ALL SELECT 4,9

INSERT INTO @Table1 SELECT * FROM @Table1

INSERT INTO @Table1 SELECT * FROM @Table1

INSERT INTO @Table1 SELECT * FROM @Table1

INSERT INTO @Table1 SELECT * FROM @Table1

INSERT INTO @Table1 SELECT * FROM @Table1

INSERT INTO @Table1 SELECT * FROM @Table1

INSERT INTO @table2

SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL

SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL

SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL

SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL

SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL

SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL

SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL

SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL

SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4 UNION ALL

SELECT 1,1 UNION ALL SELECT 2,2 UNION ALL SELECT 3,3 UNION ALL SELECT 5,4

INSERT INTO @Table2 SELECT * FROM @Table2

INSERT INTO @Table2 SELECT * FROM @Table2

INSERT INTO @Table2 SELECT * FROM @Table2

INSERT INTO @Table2 SELECT * FROM @Table2

INSERT INTO @Table2 SELECT * FROM @Table2

INSERT INTO @Table2 SELECT * FROM @Table2

The problem is when you have more than 1 of the same ID. The t1.ID=t2.ID makes a many-to-many join (CROSS JOIN) between identical ID's.

To fix the problem, we have to calculate the averages first and then do a join on the results. Here's one way...

WITH

cteT1 AS (SELECT ID, T1Avg = AVG(a) FROM @Table1 GROUP BY ID),

cteT2 AS (SELECT ID, T2Avg = AVG(b) FROM @Table2 GROUP BY ID)

SELECT ID = ISNULL(t1.ID,t2.ID),

T1MinusT2 = ISNULL(t1.T1Avg,0) - ISNULL(t2.T2Avg,0)

FROM cteT1 t1

FULL JOIN cteT2 t2

ON t1.ID = t2.ID

;