Suyash Vikram Agrawal last month

Please explain why oracle is char is not equal to varchar2 in the following query.The table structure and data is as follows:

I have a table named tab1 with a column as col1 and data type as varchar2(10) and another table named tab2 with a single column as col2 and data type as char(20)

with following data:

tab1    tab2
a        a
b        b
c        c

when I run the following query

select tab1.*,tab2.*
from tab1 full join tab2
on tab1.col1 = tab2.col2;

I get the following output:

col1   col2
null   a
null   b
null   c
a      null
b      null
c      null

I know that char occupies fixed memory but should oracle not join on string comparison?

Answers


Utsav last month

You probably have slightly different strings.

Check weather the strings are same. You might have a leading or trailing spaces. Use

trim(tab1.col1)=trim(tab2.col2)


Lord Peter last month

Char is blank padded to its full width, so you are comparing

'a                   ' with 'a'

and they are not the same


Gurwinder last month

varchar2(10) occupied only required space.

char(20) will pad space at end if the text is of smaller length.

Hence, in tab1 col1, value a is stored as a but in tab2 col2, value a is stored as a and hence no match.

Post Status

Asked in last month
Viewed 3,367 times
Voted 5
Answered 3 times

Search




Leave an answer