Non equi join Options

[thiyagusham]

SQL> select * from x;

NAME MARKS

sam 91
chris 72
rose 78
sona 97
maya 55

-------------------


SQL> select * from y;

GRADE MIN MAX
A 96 100
B 91 95
C 76 90
D 60 75
E 40 59

----------

see the output reocord sona not disclosed. I tried several times couldn't find out.
what's the specific reason why oracle ignored specific record.???


SQL> select x.name,x.marks, y.grade
2 from x ,y where
3 x.marks between y.min and y.max;

NAME MARKS GRADE

sam 91 B
rose 78 C
chris 72 D
maya 55 E

-----------------------

SQL> desc x;

Name Null? Type

NAME VARCHAR2(15)
MARKS VARCHAR2(10)

SQL> desc y;

Name Null? Type

GRADE VARCHAR2(15)
MIN VARCHAR2(10)
MAX VARCHAR2(5)


All are varchar data type. Too much confusions.
IS there any specific reason to ignore any record ??? (Sona RECORD)
How oracle handles between number and varchar2 data type.
Clearly It indicates data type is problem
can any one explain this ???

[HAL9000]

"Clearly It indicates data type is problem"

So, why guess?

Redo your test, defining the data types as number, as they should be.

Why are you defining these varchar as different sizes?

CODE

MIN VARCHAR2(10)
MAX VARCHAR2(5)

Are you on drugs?

[paulogervasio]

Try do it:

select max from y order by max DESC;

the result will be:

99
88
200
10000
100
10
1


As the column is VARCHAR, the sort will be done like a text sort ( 0,1,2,3..,9,A,B...Z)