retrieve data from left table when condition is not met

Question

I have a scenario where I have to join two tables and want to retrieve data from left table when condition does not meet.

Example: table1:

ID   code  itemsequence
0001  1      1
0001  1      2
0001  2      1
0001  2      2
0001  3      1
0002  null   1

table 2:

ID   code  outcomeID sequence   itemsequence
0001  1      0001     1             1
0001  1      0004     4             2
0001  2      0002     2             1
0001  2      0005     5             2
0001  3      0003     3             1
0002  null   0001     1             1

When I do below select ,I get 6 rows but has t2.sequence which is NULL for ID==002.this is correct

  select * from table1 t1
   LEFT join table2 t2
    on t2.Id=t1.ID and t1.itemsequence=t2.itemsequence and t2.code=t1.code

but I would like to have have sequence value in spite the condition fails. I want this sequence value for respective ID for further calculations in my project. Can we do this ? any help is appreciated!

Answer 1

You can use ISNULL

Replaces NULL with the specified replacement value.

and ISNULL(t2.code,-1)=ISNULL(t1.code,-1)

Or COALESCE

Evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.

and COALESCE(t2.code,-1)=COALESCE(t1.code,-1)

To work this out.

NOTE: Both statement will change NULL to -1 so you can do proper join. If you got few columns with same ID with NULL in code there will be effect of CROSS JOIN on this rows.