How to join on two fields where one could be null

Question

This query may seem basic, but i'm at a fairly basic level.

So here is my data - Sorry about the formatting, i've tried following the help but the table formatting is obviously not working for me (Can someone please advise?):

Table 1

ID |Country
---| ------- 
1  | UK
1  | IE 
1  | US   
2  | UK 
2  | FR

Table 2

ID |Country
---| ------- 
1  | UK
1  | IE 
2  | UK

The result i want is this

Table 1-----  | ----Table 2

ID |Country   |-----ID |Country
---| -------  |--------|--------
1  | UK       | 1      | UK
1  | IE       | 1      | IE
1  | US       | 1      | NULL
2  | UK       | 2      | UK
2  | FR       | 2      | NULL

But more specifically i want to identify the NULL's so that i get this result

Table 1-----  | ----Table 2

ID |Country   |-----ID |Country
---| -------  |--------|--------
1  | US       | 1      | NULL
2  | FR       | 2      | NULL

The code i have used so far is:

select *
from table1 t1
left outer join table2 t2 on t1.id = t2.id and t1.country = t2.country
where t1.id is not null
and t2.country is null

Thanks Paul. @ GuidoG - Sorry that was a typo, i've updated it now — bobtastic, Apr 06 '17 at 16:00

score 1 · Answer 1 · 2017-04-06T16:40:04.503

1

Try this

select t1.id, t1.country, isnull(t2.id, t1.id) AS T2_ID, t2.country
from table1 t1
left outer join table2 t2 on t1.id = t2.upc and t1.country = t2.country

if you want to only show the ones where you have nulls in t2, you can add

where t2.id is null

But if you want to show all the records, just leave it without the WHERE condition

edited Apr 06 '17 at 16:40

answered Apr 06 '17 at 16:02

Hi Agapwlesu, i've tried to translate your code to my tables but i keep getting a error stating "The isnull function requires 2 argument(s).". Can you help please? select st.upc, st.RELEASE_STATUS, st.OWNING_COUNTRY, st.GRS_GRANDFATHER, st.GRCS_DATA_RECEIVED, st.LEGAL_RIGHT_COUNTRY, iif(isnull(rs.id), st.id, rs.id) AS T2_ID, iif(isnull(rs.id), "NULL", rs.country) AS T2_Country from dbo.[UATinGRS rows] st left outer join dbo.UATBatchOneResult rs on st.upc = rs.upc and st.LEGAL_RIGHT_COUNTRY = rs.LEGAL_RIGHT_COUNTRY – bobtastic Apr 06 '17 at 16:14
That's not how you use [**`isnull()`**](https://msdn.microsoft.com/en-us/library/ms184325.aspx) in sql server. – SqlZim Apr 06 '17 at 16:17
It's been a while since I used sqlserver, but I was hoping you got the idea (need to test for null t2.id and substitute from t1.id or the word "NULL" for the country) and translated. I will edit to fix it. – Apr 06 '17 at 16:33

score 0 · Accepted Answer · edited May 23 '17 at 10:30

You were close, you just need to use isnull() or coalesce().

select 
    t1.id
  , t1.country
  , t2_Id = isnull(t2.id,t1.id)
  , t2_country = t2.country 
from table1 t1
left outer join table2 t2 on t1.id = t2.id and t1.country = t2.country
where t1.id is not null
  --and t2.country is null

rextester demo: http://rextester.com/XCNH52338

returns:

+----+---------+-------+------------+
| id | country | t2_Id | t2_country |
+----+---------+-------+------------+
|  1 | UK      |     1 | UK         |
|  1 | IE      |     1 | IE         |
|  1 | US      |     1 | NULL       |
|  2 | UK      |     2 | UK         |
|  2 | FR      |     2 | NULL       |
+----+---------+-------+------------+

with the additional filter of t2.country is null

returns:

+----+---------+-------+------------+
| id | country | t2_Id | t2_country |
+----+---------+-------+------------+
|  1 | US      |     1 | NULL       |
|  2 | FR      |     2 | NULL       |
+----+---------+-------+------------+

The main difference between the two is that coalesce() can support more than 2 parameters, and it selects the first one that is not null. More differences between the two are answered here.

coalesce() is standard ANSI sql, so it is available in most RDBMS. isnull() is specific to sql server.

Reference:

How to join on two fields where one could be null

2 Answers2