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     |

Answer 1

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)