1

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?

Migs Isip
  • 1,450
  • 3
  • 23
  • 50
Suyash Vikram Agrawal
  • 21
  • 3
  • Very good and long explanation of the top answers below [here at this site](https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2668391900346844476) – JNevill Jan 11 '17 at 17:00
  • See this: http://stackoverflow.com/questions/20417845/what-is-the-major-difference-between-varchar2-and-char/20418849#20418849 – Wernfried Domscheit Jan 11 '17 at 17:42

4 Answers4

4

varchar2(10) occupied only required space.

char(20) will pad blank 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.

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
2

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

'a                   ' with 'a'

and they are not the same

Lord Peter
  • 3,433
  • 2
  • 33
  • 33
2

Straight from the Oracle Documentation...

https://docs.oracle.com/database/122/SQLRF/Data-Type-Comparison-Rules.htm#SQLRF30027

Blank-Padded and Nonpadded Comparison Semantics

With blank-padded semantics, if the two values have different lengths, then Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle uses blank-padded comparison semantics only when both values in the comparison are either expressions of data type CHAR, NCHAR, text literals, or values returned by the USER function.

With nonpadded semantics, Oracle compares two values character by character up to the first character that differs. The value with the greater character in that position is considered greater. If two values of different length are identical up to the end of the shorter one, then the longer value is considered greater. If two values of equal length have no differing characters, then the values are considered equal. Oracle uses nonpadded comparison semantics whenever one or both values in the comparison have the data type VARCHAR2 or NVARCHAR2.

The results of comparing two character values using different comparison semantics may vary. The table that follows shows the results of comparing five pairs of character values using each comparison semantic. Usually, the results of blank-padded and nonpadded comparisons are the same. The last comparison in the table illustrates the differences between the blank-padded and nonpadded comparison semantics.

BobC
  • 4,208
  • 1
  • 12
  • 15
0

Edit: After reading the other answers, you can know what is wrong. You can overcome it by using below syntax.

select tab1.*,tab2.*
from tab1 full join tab2
on trim(tab1.col1)=trim(tab2.col2);
Utsav
  • 7,914
  • 2
  • 17
  • 38