CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
Farkiba
Farkiba

Reputation: 376

Return all values including NULL

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     |

Upvotes: 15

Views: 76770

Answers (5)

Mihai Cristian
Mihai Cristian

Reputation: 125

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

Upvotes: 0

user359040
user359040

Reputation:

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 +'%'

Upvotes: 4

Harshil
Harshil

Reputation: 411

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)

Upvotes: 1

bAN
bAN

Reputation: 13815

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)

Upvotes: 16

d'alar'cop
d'alar'cop

Reputation: 2365

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.

Upvotes: 1

Related Questions