Join tables without primary key, foreign key that will also return the null values when joining the table

user_17280732 0
Viewed 131

I have these tables:

Table 1:

Code1  Code2  Code3  Code4  ISCode5
-----------------------------------
 xx     NULL  TEST1  TEST1    1
 yy     zzz   TEST2  TEST2    1
NULL     ss   TEST3  TEST3    1
NULL    aaa   TEST4  TEST4    0

Table 2:

ID    ColTest1   ColTest2   Code1  Code2
-----------------------------------------
 1      2            3        xx     NULL  
 1      3            4        yy     zzz
 2      5            6       NULL    ss
 2      5            6       NULL    aaa

Expected output:

 ID    ColTest1   ColTest2   Code1  Code2
------------------------------------------
 1      2            3        xx     NULL  
 1      3            4        yy     zzz
 2      5            6        NULL    ss

I need to join table1 and table 2 where IsCode5 = 1. But there is no primary key and foreign key for both tables. The only common columns for both tables is Code1 and Code2.

I have tried this:

SELECT  
    T2.ID, T2.ColTest1, T2.ColTest2, T2.Code1, T2.Code2 
FROM 
    [dbo].[table2] T2 
INNER JOIN 
    [dbo].[table1] T1 ON T1.Code1 = T2.Code1
                      AND T1.Code1 = T2.Code2
WHERE 
    T1.ISCode5 = 1

But it only returns:

ID    ColTest1   ColTest2   Code1  Code2  
-----------------------------------------
 1      3            4        yy     zzz

Do you have any idea to return the expected result? Thanks.

tsql sql-server sql
2 Answers

I think you just need OR instead of AND

-- DDL
declare @Table1 table (Code1 varchar(2), Code2 varchar(3), Code3 varchar(5), Code4 varchar(5), ISCode5 bit);
declare @Table2 table (ID int, ColTest1 int, ColTest2 int, Code1 varchar(2), Code2 varchar(3));

-- DML
insert into @Table1 (Code1, Code2, Code3, Code4, ISCode5)
values
('xx', NULL, 'TEST1', 'TEST1', 1),
('yy', 'zzz', 'TEST2', 'TEST2', 1),
(NULL, 'ss', 'TEST3', 'TEST3', 1),
(NULL, 'aaa', 'TEST4', 'TEST4', 0);

insert into @Table2 (ID, ColTest1, ColTest2, Code1, Code2)
values
(1, 2, 3, 'xx', NULL),  
(1, 3, 4, 'yy', 'zzz'),
(2, 5, 6, NULL, 'ss'),
(2, 5, 6, NULL, 'aaa');

-- Query
select T2.ID, T2.ColTest1, T2.ColTest2, T1.Code1, T1.Code2
from @Table1 T1
inner join @Table2 T2 on t1.code1 = t2.code1 or t1.code2 = t2.code2 -- <= OR not AND
where ISCode5 = 1;

Results:

ID ColTest1 ColTest2 Code1 Code2
1 2 3 xx NULL
1 3 4 yy zzz
2 5 6 NULL ss

Note: if you add the DDL+DML as shown here you make it much easier for people to answer.

user_11274280

It seems your real problem is that NULL = NULL never returns TRUE, it returns UNKNOWN, which means the join fails.

There are a number of ways around this, primarily using OR ... IS NULL AND ... IS NULL, however a much neater solution involving INTERSECT is available

SELECT *
FROM Table2 t2
WHERE EXISTS (
    SELECT t2.Code1, t2.Code2
    INTERSECT
    SELECT t1.Code1, t1.Code2
    FROM Table1 t1
    WHERE t1.ISCode5 = 1
);
-- alternately
SELECT t1.*
FROM Table2 t2
JOIN Table1 t1 ON EXISTS (
    SELECT t2.Code1, t2.Code2
    INTERSECT
    SELECT t1.Code1, t1.Code2
)
WHERE t1.ISCode5 = 1;

db<>fiddle

This should optimize pretty well, as the compiler recognizes this construct and transforms it into an IS equality check, as documented in various places.

user_148689970
Related