Suyash Vikram Agrawal 5 days ago

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 5 days ago

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 5 days ago

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

'a                   ' with 'a'

and they are not the same


Gurwinder 5 days ago

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 5 days ago
Viewed 3,367 times
Voted 5
Answered 3 times

Search




Leave an answer