Home Ask Login Register

Developers Planet

Your answer is one click away!

Suyash Vikram Agrawal January 2017

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?


Utsav January 2017

You probably have slightly different strings.

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


Lord Peter January 2017

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

'a                   ' with 'a'

and they are not the same

Gurwinder January 2017

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


Leave an answer

Quote of the day: live life