Return all values, including NULL

Question

I have two tables in SQL Server 2008, and by doing JOIN I want to get all the values, however I only get the values ​​in which the records are stored, although I need fields with NULL records.

Here is an example of how tableA looks like

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

tableB stores the following entries

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

My request to return the fields I need is as follows

 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 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 SerialNo NULL case. With your actual state, this case deviates 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) 

Answer 2

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

Answer 3

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: INTERACTION

Greetings.

Answer 4

try it

 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)