Left Join with NULL values in column

Database

Left Join with NULL values in column

I have two tables as beow

Table1

 

A    B    C
a    1    2
?    2    3
c    4    4

Table 2
A    D    E
b    1    2

 a    4    4

When I left outer join Table1 and Table2, I should get,

Select T1.*, T2.* FROM Table1 T1

LEFT OUTER JOIN Table2 T2 ON (T1.A = T2.A)


Output

A    B    C    D    E
 a    1    2    4    4
?    2    3    ?    ?
 c    4    4    ?    ?

 

But I get

A    B    C    D    E
 a    1    2    4    4
 c    4    4    ?    ?

 

 

  • Left Join
  • NULL
3 REPLIES
Junior Contributor

Re: Left Join with NULL values in column

If this is your actual Select and the NULL row is missing it would be a bug.

 

But you probably added a WHERE-condition either on Table1.A or a column from Table2 (changing the result to an Inner Join).

 

Re: Left Join with NULL values in column

you are right. I did add a where condition on Table2. Is there a work around for that?

Junior Contributor

Re: Left Join with NULL values in column

It depends, in most cases you must AND the condition to the join.

 

See the Outer Join Case Study.