Home I am trying to join two tables I want null values to be equivalent to ALL

# I am trying to join two tables I want null values to be equivalent to ALL

Darth Veder
1#
Darth Veder Published in 2017-12-07 20:49:58Z
 Lets say that I have two tables: table 1 columns: A,B,C table 2 columns: D,E,F JOIN Conditions table1.A = table2.D, table1.B=table2.E, table1.C = table2.F  Any column can have a null value. I want null values to be treated as a match for any value. (i.e if A=D and B=E, but C is null. F has two values for A=D and B=E. Both rows should be included in the resultant table) Table1: Table2: A: B: C: D: E: F: a b c a b c e f e a f o q e b f h i j h i k l m l a m o q o q results(show table 2 values...): D: E: F: a b c e a f e b f o q l a m 
JNevill
2#
 You could just write out this condition. I feel like there may be some way to do this a bit more sneaky/less verbose, but this should get you in the ballpark: ON (table1.A = table2.D AND table1.B = table2.E AND (table1.C = table2.F OR table1.C IS NULL)) OR (table1.A = table2.D AND (table1.B = table2.E OR table1.B IS NULL) AND table1.C = table2.F) OR ((table1.A = table2.D OR table1.A IS NULL) AND table1.B = table2.E AND table1.C = table2.F) 
 I think this does what you want. SQL will actually optimize this rather efficiently. DECLARE @t1 AS TABLE ( [A] NVARCHAR(25) , [B] NVARCHAR(25) , [C] NVARCHAR(25) ); DECLARE @t2 AS TABLE ( [D] NVARCHAR(25) , [E] NVARCHAR(25) , [F] NVARCHAR(25) ); INSERT INTO @t1 ([A] , [B] , [C]) VALUES (N'one',N'one',N'one'), (N'two',N'two',NULL), (N'three',N'three',N'three'); INSERT INTO @t2 ([D] , [E] , [F]) VALUES (N'one',N'one',N'one'), (N'two',N'two',NULL), (N'three',N'three',NULL); SELECT * FROM @t1 AS [t1] JOIN @t2 AS [t2] ON [t1].[A] = [t2].[D] AND [t1].[B] = [t2].[E] AND [t1].[C] = [t2].[F]; SELECT * FROM @t1 AS [t1] JOIN @t2 AS [t2] ON ( [t1].[A] = [t2].[D] OR [t1].[A] IS NULL OR [t2].[D] IS NULL ) AND ( [t1].[B] = [t2].[E] OR [t1].[B] IS NULL OR [t2].[E] IS NULL ) AND ( [t1].[C] = [t2].[F] OR [t1].[C] IS NULL OR [t2].[F] IS NULL ) ; 
 I would use something similar to JNevill's approach, but I would find writing it this way to make more sense to me: ON (Table1.A=Table2.D OR Table1.A IS NULL OR Table2.D IS NULL) AND (Table1.B=Table2.E OR Table1.B IS NULL OR Table2.E IS NULL) AND (Table1.C=Table2.F OR Table1.C IS NULL OR Table2.F IS NULL)