Last active
August 29, 2015 14:02
-
-
Save ax4413/d850a0ed86b1b0a79516 to your computer and use it in GitHub Desktop.
FULL OUTER JOIN EXCEPT when it doesn't work blog post. Solution 1 - the full outer join
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT 'SELECT ' + | |
'CASE WHEN ' + Ord.Predicate + ' != ' + cast(t.max_column_id_used as varchar(50)) + ' THEN 1 ELSE 0 END AS [NoMatch], ' + -- identify where left and right columns do not join | |
'* ' + -- select * | |
'FROM ' + @Left +'.'+ QUOTENAME(t.name) + ' src ' + -- from T1 | |
'FULL OUTER JOIN ' + @Right + '.' + QUOTENAME(t.name) + ' tgt ' + -- join T2 | |
' ON COALESCE(tgt.' + QUOTENAME(c.name) + ', ' + tyd.Value + ') = COALESCE(src.' + QUOTENAME(c.name) + ', ' + TYD.Value + ') ' + -- If joining on a null use a default type value | |
Cols.Predicate + ' ' + -- the rest of teh join syntax. If joining on a null use a default type value | |
'WHERE CASE WHEN ' + Ord.Predicate + ' != ' + cast(t.max_column_id_used as varchar(50)) + ' THEN 1 ELSE 0 END = 1 ' + -- exclude rows where there is a match | |
'ORDER BY CASE WHEN ' + Ord.Predicate + ' != ' + cast(t.max_column_id_used as varchar(50)) + ' THEN 1 ELSE 0 END, src.' + c.name + ', tgt.' + c.name -- Order by NoMatch, then T1.Id, T2.Id | |
FROM sys.tables t | |
INNER JOIN sys.columns c | |
ON c.object_id = t.object_id | |
AND c.column_id = 1 | |
INNER JOIN sys.types ty | |
ON ty.user_type_id = c.user_type_id | |
INNER JOIN @Type tyd | |
ON ty.name = tyd.Name | |
CROSS APPLY ( SELECT DISTINCT STUFF( ( SELECT 'AND COALESCE(tgt.' + QUOTENAME(c1.name) + ',''' + tyd1.Value + ''') = COALESCE(src.' + QUOTENAME(c1.name) + ',''' + tyd1.Value + ''') ' | |
FROM sys.columns c1 | |
INNER JOIN sys.types ty1 | |
ON ty1.user_type_id = c1.user_type_id | |
INNER JOIN @Type tyd1 | |
on ty1.name = tyd1.Name | |
WHERE c1.object_id = t.object_id | |
AND c1.column_id > 1 | |
ORDER BY c1.column_id | |
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,0,'') AS Predicate | |
)Cols | |
CROSS APPLY ( SELECT DISTINCT STUFF( (SELECT '+ CASE WHEN COALESCE(tgt.' + QUOTENAME(c2.name) + ',''' + tyd2.Value + ''') = COALESCE(src.' + QUOTENAME(c2.name) + ',''' + tyd2.Value + ''') THEN 1 ELSE 0 END ' | |
FROM sys.columns c2 | |
INNER JOIN sys.types ty2 | |
ON ty2.user_type_id = c2.user_type_id | |
INNER JOIN @Type tyd2 | |
on ty2.name = tyd2.Name | |
WHERE c2.object_id = t.object_id | |
ORDER BY c2.column_id | |
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,1,'') AS Predicate | |
)Ord | |
WHERE t.name in (SELECT Name FROM @T) | |
; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment