I have data that looks like this:
Table_A:
Source tableName systemid
A table_1 123abcA2
B table_1 222DEFD3
C table_1 369CCCB3
Table_B:
Source tableName systemid
Q table_2 123abc
R table_2 222DEF
C table_2 369CCC
I ran the following query:
select a.Source, a.tableName, a.systemid as a_systemid, b.systemid as b_systemid
from table_a as a
inner join table_b as b on a.systemid = b.systemid
Here is what came back:
Source tableName a_systemid b_systemid
A table_1 123abcA2 123abc
B table_1 222DEFD3 222def
C table_1 369CCCB3 369CCC
Shouldn't I get nothing returned? As nothing matches.
Table A system id = nvarchar data type
Table b systemid = uniqueidentifier data type
Implicit casting is occuring and essentially truncating your string data which causes a match. You need to explicitly cast the uniqueidentifier to an nvarchar(max).
Please see this question and answer.
DECLARE @t1 TABLE([Source] CHAR(1),tableName VARCHAR(10),systemid nvarchar(max))
DECLARE @t2 TABLE([Source] CHAR(1),tableName VARCHAR(10),systemid uniqueidentifier)
INSERT INTO @t1 SELECT 'A','table_1','15b993cc-e8be-405d-bb9f-0c58b66dcdfe_1'
INSERT INTO @t1 SELECT 'B','table_1','4cffe724-3f68-4710-b785-30afde5d52f8_1'
INSERT INTO @t1 SELECT 'C','table_1','7ad22838-ddee-4043-8d1f-6656d2953545_1'
INSERT INTO @t2 SELECT 'Q','table_2','15b993cc-e8be-405d-bb9f-0c58b66dcdfe'
INSERT INTO @t2 SELECT 'R','table_2','4cffe724-3f68-4710-b785-30afde5d52f8'
INSERT INTO @t2 SELECT 'C','table_2','7ad22838-ddee-4043-8d1f-6656d2953545'
select a.Source, a.tableName, a.systemid as a_systemid, b.systemid as b_systemid
from @t1 as a
inner join @t2 as b on a.systemid = CONVERT(NVARCHAR(MAX),b.systemid)
As a practice, you should always explicitly cast mismatched datatypes for clarity as well as prevent weird "what is going on?!?!" stuff.