Clever Geek Handbook

"less" in ORACLE for two varchars?

I had a question asked:

Find the celebs that have been in relationship with the same celeb. 
The result should be (celeb1, celeb2, celeb3) triples, 
meaning that celeb1 and celeb2 have been in relationship with celeb3.

The Relationship table now has fields celeb1 and celeb2. Everywhere is VARCHAR.

My solution to the problem:

  CREATE VIEW Celeb1Rels AS
SELECT celeb1 AS c1, celeb2 AS c2 FROM relationships;

CREATE VIEW Celeb2Rels AS
SELECT celeb1 AS c2, celeb2 AS c3 FROM relationships;

SELECT * FROM Celeb1Rels NATURAL JOIN Celeb2Rels;

and it works great. However, the instructor posted his solution and he:

SELECT X.celeb1, Y.celeb1, X.celeb2
FROM Relationships X, Relationships Y
WHERE X.celeb2=Y.celeb2 AND X.celeb1<Y.celeb1;

I don't understand why it uses X.celeb1 <Y.celeb1 It works and gives the correct result, but I thought "<" was used to compare numbers?

Can anyone explain what the "<" does in this case? and how does it behave when comparing VARCHARS?

+3


source to share


3 answers


This is so that you don't get duplicate relationships. Take for example 2 hypothetical relationships between celeb1 and celeb2, and celeb1 and celeb3, you want the result

celeb2, celeb3, celeb1

You need a second inequality operator to make sure the table is not concatenated in the same ratio (i.e. celeb1 and Celeb2 are concatenated back to celeb1 and celeb2). If you were to use your instructors' query and fix it, <

you should have used not equals instead <>

, but you would get the result:



celeb2, celeb3, celeb1
celeb3, celeb2, celeb1

But these rows show the same thing but in a different order, the inequality operator >

just makes sure that the second column is always the alphabetical name after the 1st column.

So, to summarize, the operator >

when applied to varchars works alphabetically, so 'a' < 'b'

, 'abc' > 'aaa'

etc.

+3


source


I think to eliminate duplicate entries, for example:

If (A,B)

and (B,C)

in this table, then if the request is without AND X.celeb1<Y.celeb1

, we get



(A,B,C)

and (B,C,A)

at the exit. By adding this condition, we only output one record (A,B,C)

at a time A < C

.

+2


source


The instructor solution creates triplets where CELEB2 is in two respects. This part of the WHERE clause ...

X.celeb1<Y.celeb1

... ensures that you only get rows for three different celebrities (i.e. avoid matching the same record with yourself) and you only get one row for each triple.

Less than working as you think it is sorted alphabetically. So it is 'ANDY GARCIA' < 'ANDY KAUFMAN'

true.

Beware:

  • it uses ASCII values ​​and is therefore case sensitive. This means it 'ANDY GARCIA' < 'ANDY KAUFMAN'

    is false.
  • the numbers are sorted alphabetically, so is '11' < '2'

    true.

"I had no idea about this sorting problem."

You can randomize Oracle's output by setting the NLS_SORT parameter. However, this will not change the comparison; for this we need to change the NLS_COMP parameter to LINGUISTIC

. This is not the default behavior simply because there is too much code in there that could rely on case-senstive sorting. Find out more .

+1


source






More articles:


All Articles