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

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          <null>    f                 e           a           f
o          <null>    q                 e           b           f             
h          i         j                 h           i           k
l          <null>    m                 l           a           m
o          <null>    q                 o           <null>      q


results(show table 2 values...):
D:   E:     F:
a    b      c
e    a      f
e    b      f
o   <null>  q
l    a      m
JNevill
2#
JNevill Reply to 2017-12-07 21:01:39Z

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)
Katherine Elizabeth Lightsey
3#
Katherine Elizabeth Lightsey Reply to 2017-12-07 21:08:55Z

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
        )
; 
Tab Alleman
4#
Tab Alleman Reply to 2017-12-07 22:18:24Z

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)
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.315726 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO