Return all values including NULL

Question

I have two tables in SQL Server 2008, and by doing a JOIN I want to get all values, however I get only the values where records exist even though I need the fields with NULL records.

Here is the example on how tableA looks like

|IPAddress  |DNSRecord|
|192.168.1.1|Test     |
|192.168.0.1|Test1    |

tableB stores the following records

|NetworkAddress|SerialNo |
|192.168.1.1   |1Z5A789DS|
|192.168.0.1   |NULL     |

My query to return the fields I need is the following

SELECT 
    t1.IPAddress,
    t1.DNSRecord,
    t2.SerialNo,
    t2.IPAddress
FROM tableA t1
JOIN tableB t2 ON t1.IPAddress = t2.NetworkAddress
WHERE
   IPAddress LIKE '%' +@IPAddress + '%'
   AND SerialNo LIKE '%' +@SerialNo +'%'

The problem with this query is that I get the following result

|IPAddress  |DNSRecord|SerialNo |
|192.168.1.1|Test     |1Z5A789DS|

And I would like to get returned the following result instead

|IPAddress  |DNSRecord|SerialNo |
|192.168.1.1|Test     |1Z5A789DS|
|192.168.0.1|Test1    |NULL     |

score 15 · Accepted Answer · answered Apr 08 '13 at 09:43

15

Just add a condition for the case of SerialNo is NULL. With your actual condition, this case is rejected from selection

SELECT t1.IPAddress,
t1.DNSRecord,
t2.SerialNo,
t2.IPAddress
FROM tableA t1
JOIN tableB t2 ON t1.IPAddress = t2.NetworkAddress
WHERE
IPAddress LIKE '%' +@IPAddress + '%'
AND ( SerialNo LIKE '%' +@SerialNo +'%' OR SerialNo is NULL)

answered Apr 08 '13 at 09:43

bAN

13,375
16
60
93

Awesome :D it works :) in 8 minutes I will accept it :) I tried this before but without the brackets and it didnt worked :) thanks a lot for the help :) – Farkiba Apr 08 '13 at 09:47
1

happy to help, the brackets enclose the two OR conditions. And the result of the OR is evaluated and applied on the AND global condition. – bAN Apr 08 '13 at 09:50

score 4 · Answer 2 · answered Apr 08 '13 at 09:50

One alternative:

SELECT t1.IPAddress,
t1.DNSRecord,
t2.SerialNo,
t2.IPAddress
FROM tableA t1
JOIN tableB t2 ON t1.IPAddress = t2.NetworkAddress
WHERE
IPAddress LIKE '%' +@IPAddress + '%'
AND coalesce(SerialNo, @SerialNo) LIKE '%' +@SerialNo +'%'

score 1 · Answer 3 · answered Apr 08 '13 at 09:42

1

Try using this instead:

SELECT t1.IPAddress,
t1.DNSRecord,
t2.SerialNo,
t2.IPAddress
FROM tableA t1
FULL OUTER JOIN tableB t2 ON t1.IPAddress = t2.NetworkAddress
WHERE
IPAddress LIKE '%' +@IPAddress + '%'
AND SerialNo LIKE '%' +@SerialNo +'%'

See: OUTER JOIN

Cheers.

answered Apr 08 '13 at 09:42

d'alar'cop

2,357
1
14
18

For some reason it still gives me the same result, thank you for your time :) – Farkiba Apr 08 '13 at 09:44

score 1 · Answer 4 · answered Apr 08 '13 at 09:44

1

Try this

SELECT t1.IPAddress,
t1.DNSRecord,
t2.SerialNo,
t2.IPAddress
FROM tableA t1
JOIN tableB t2 ON t1.IPAddress = t2.NetworkAddress
WHERE
IPAddress LIKE '%' +@IPAddress + '%' 
AND (SerialNo LIKE '%' +@SerialNo +'%' OR SerialNo IS NULL)

answered Apr 08 '13 at 09:44

Harshil

403
2
7

score 0 · Answer 5 · edited Oct 19 '20 at 18:28

0

You can try this, I always use this INCLUDE_NULL_VALUES in my query or procedure, when I need the null values

  SELECT 
        t1.IPAddress,
        t1.DNSRecord,
        t2.SerialNo,
        t2.IPAddress
    FROM tableA t1
    JOIN tableB t2 ON t1.IPAddress = t2.NetworkAddress
    WHERE
       IPAddress LIKE '%' +@IPAddress + '%'
       AND SerialNo LIKE '%' +@SerialNo +'%'
    INCLUDE_NULL_VALUES

edited Oct 19 '20 at 18:28

peterh

11,875
18
85
108

answered Feb 12 '20 at 14:45

Mihai Cristian

115
2
10

What is INCLUDE_NULL_VALUES? – Hicham May 18 '20 at 22:26
This is actually the correct answer for sql server 2016+. Although, it is syntactically incorrect. You need to add `FOR JSON AUTO, INCLUDE_NULL_VALUES` or `FOR JSON PATH, INCLUDE_NULL_VALUES` at the end of your query (after the where clause). – Ken Hadden Dec 09 '22 at 22:10

Return all values including NULL

5 Answers5