Full Outer Join Mismatching

Question

new to this type of request in Microsoft SQL Server. I'm using a full outer join on two tables looking for records that are not matching in the right table (New) vs left table (Old). I'm trying to find the new scores from the new table so I can update a production table with the most recent scores, while still holding onto old scores that have not been updated yet. This is my set up

Select
a.customer
,a.date
,a.Cat score
,a.Dog score
,a.Mouse score
,b.customer
,b.date
,b.Cat score
,b.Dog score
,b.Mouse score

From Old Table a

Full Outer Join New Table b
ON a.customer = b.customer
AND a.date = b.date
AND a.Cat score = Cast(b.Cat score as Varchar)
AND a.Dog score = Cast(b.Dog score as Varchar)
AND a.Mouse score = Cast(b.Mouse score as Varchar)

Note--- Have to cast the scores as Varchar or else I could not get the join to work. "Conversion failed when converting the varchar value '9.0000' to data type int."

Results: Both lists are 100% different without any matches

This can't be true because I can search the records in both tables manually and find the exact same result in both tables. Maybe there is a better way to do this type of update?

Could be the cast isn't working as you had expected. If a.Cat_score = 9 and b.Cat_score = 9.0000 then these won't match. If you want to add some sample records from tables a and b we can take a further look. Include the fields types as well. — David Rushton, Feb 10 '16 at 17:31
So I believe the issue is coming from the field types not matching. I am trying to cast them as int but continue to get errors. So trying to cast them as Varchar still gets me 100% different. — Michael Tsu, Feb 10 '16 at 17:31
I just tried casting the a.date as date which worked. I tried casting a.cat score to int but it did not and the Mouse score still gave a false positive on differences. — Michael Tsu, Feb 10 '16 at 17:49
Into the question? Take a look at [this](http://meta.stackexchange.com/questions/73566/is-there-any-markdown-to-create-tables), from the Meta site. — David Rushton, Feb 10 '16 at 17:50

David Rushton · Accepted Answer · 2016-02-10T18:15:53.830

Your problem is the strings 9 and 9.0000 are not equal. They do not join. These table variables will be used to demo this:

DECLARE @TableA TABLE
    (
        CatScore Int
    )
;

DECLARE @TableB TABLE
    (
        CatScore VARCHAR(10)
    )
;

INSERT INTO @TableA (CatScore) VALUES (9);
INSERT INTO @TableB (CatScore) VALUES ('9.0000');

The first example highlights the mismatch.

Mismatching Join Example

SELECT
    *
FROM
    @TableA AS a
        FULL OUTER JOIN @TableB AS b    ON b.CatScore = CAST(a.CatScore AS VARCHAR(50))

Returned Value

CatScore    CatScore
9           NULL
NULL        9.0000

What you need to do is match the data types and then the values. This example assumes:

Table A stores cat score as a integer.
Table B stores the same as a varchar.
Table B always includes 4 zeros after a full stop.

Matching Example

SELECT
    *
FROM
    @TableA AS a
        FULL OUTER JOIN @TableB AS b    ON b.CatScore = CAST(a.CatScore AS VARCHAR(50)) + '.0000'

Returns

CatScore    CatScore
9           9.0000

Here the integer 9 has been cast into a varchar. The full stop and trailing zeros have then been added. It's not a decimal place, as these aren't really numbers.

The lesson to takeaway from this exercise is; always use the correct data type. Storing numbers in strings will cause problems further down the line.

UPDATE

It would make more sense to CAST both fields into a DECIMAL. Both integers and varchars, containing numeric like data, can be converted into decimals. When casting fields for matching you want to find the smallest data type that will hold all input from both source fields.

Thanks, casting as a decimal helped on the 9.0000 vs 9 but my Mouse score is still causing a problem even though they are both int type fields. Any suggestions? — Michael Tsu, Feb 10 '16 at 18:52
The problem here is with NULL values. NULL doesn't equal anything, including itself. That means NULL = NULL is false. Or to put it another way MouseScore NULL is not equal to MouseScore NULL. You need to replace the NULLs. [ISNULL](https://msdn.microsoft.com/en-GB/library/ms184325.aspx) can do this. ISNULL(MouseScore, -1) will replace all NULLS with -1. Make sure you pick a value that isn't already in use, or you'll get unexpected joins! — David Rushton, Feb 10 '16 at 18:57
Thank you. I just figured that out some sort of Safe Null thing. I'm reading some more forums about how to work around it. Thanks again for the help destination-data — Michael Tsu, Feb 10 '16 at 18:59
Not a problem. I try to avoid allowing NULLs into my tables. Even the inventure of [NULL isn't a fan](https://en.wikipedia.org/wiki/Tony_Hoare). — David Rushton, Feb 10 '16 at 19:04

Full Outer Join Mismatching

1 Answers1