There is two types of syntax for writing SQL joins the ANSI way and the Oracle way. It has been said on many a website that they are interchangeable – really ?

All tests carried out on Oracle 10.2.0.1.0, Windows 2003

First the setup

DROP TABLE ROB_TEST1 PURGE;

DROP TABLE ROB_TEST2 PURGE;

CREATE TABLE ROB_TEST1 (ID1 NUMBER, ID2 NUMBER);

CREATE TABLE ROB_TEST2 (ID1 NUMBER, ID2 NUMBER);

INSERT INTO ROB_TEST1 VALUES (1,1);

INSERT INTO ROB_TEST1 VALUES (2,2);

INSERT INTO ROB_TEST1 VALUES (3,3);

INSERT INTO ROB_TEST1 VALUES (4,4);

INSERT INTO ROB_TEST1 VALUES (5,5);

INSERT INTO ROB_TEST2 VALUES (1,1);

INSERT INTO ROB_TEST2 VALUES (3,3);

If I wanted to return all results from ROB_TEST1 and either a matching 1 or a NULL from ROB_TEST2 (an outer join) then in Oracle’s syntax I would write

SELECT R1.ID2 A ,R2.ID2 B
FROM ROB_TEST1 R1, ROB_TEST2 R2
WHERE R1.ID1 = R2.ID2(+)
AND R2.ID2(+) = 1;

A B
———- ———-
1 1
5
4
3
2

And in ANSI syntax

SELECT R1.ID2 A, R2.ID2 B
FROM ROB_TEST1 R1 LEFT JOIN ROB_TEST2 R2 ON R1.ID1 = R2.ID2 AND R2.ID2 = 1;

A B
———- ———-
1 1
5
4
3
2

What however if my requirement changes and that I required a matching 1 or 3 from ROB_TEST2. ANSI syntax first this time

SELECT R1.ID2 A, R2.ID2 B
FROM ROB_TEST1 R1 LEFT JOIN ROB_TEST2 R2 ON R1.ID1 = R2.ID2 AND R2.ID2 IN (1,3);

A B
———- ———-
1 1
2
3 3
4
5

And in the Oracle syntax

SELECT R1.ID2 A ,R2.ID2 B
FROM ROB_TEST1 R1, ROB_TEST2 R2
WHERE R1.ID1 = R2.ID2(+)
AND R2.ID2(+) IN (1,3);

ORA-01719: outer join operator (+) not allowed in operand of OR or IN

What? Not the result I was expecting. So it appears that the syntax has some slight differences. I am fine with that, the joys of outer joins, but what was interesting is that I would have expected the Oracle syntax to work for the majority of requirements and ANSI to have a problem (support for ANSI syntax only started in version 9i I think).

After a few days I thought I try and find out why only the ANSI syntax produced a reault. The only way I could possibly find out what was happening was to dump the oracle optimizers thought processes.

A dump of the SQL can be obtained with the following

ALTER SESSION SET EVENTS ‘10053 trace name context forever, level 12’ ;

And then running the statement and exit the session of turn the event off. You will find the result in udump directory

Now I do not claim to be an expert in interpretting the output, there are many people better than me, but with a bit of ‘googling’ it is possible to try and work it out bits and pieces then hopefully glue something togetger.

I have cut the snippet it out that is important

**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$3 (#0).
PM: Checking validity of predicate move-around in SEL$3 (#0).
CBQT: Validity checks failed for 26wqg6rfzhm9p.
CVM: Considering view merge in query block SEL$3 (#0)
CVM: Checking validity of merging SEL$2 (#0)
CVM: Considering view merge in query block SEL$2 (#0)
CVM: Checking validity of merging SEL$1 (#0)
CVM: Considering view merge in query block SEL$1 (#0)
Query block (000000000FA34988) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT “R1”.”ID1″ “QCSJ_C000000000300000″,”R1”.”ID2″ “QCSJ_C000000000300002″,”from$_subquery$_004”.”ID1_1″ “ID1″,”from$_subquery$_004”.”ID2_0″ “ID2” FROM “ROB”.”ROB_TEST1″ “R1”, LATERAL( (SELECT “R2”.”ID2″ “ID2_0″,”R2”.”ID1″ “ID1_1” FROM “ROB”.”ROB_TEST2″ “R2” WHERE “R1″.”ID1″=”R2”.”ID2″ AND (“R2”.”ID2″=1 OR “R2”.”ID2″=3)))(+) “from$_subquery$_004”
Query block (000000000FA34988) unchanged
CVM: Merging SPJ view SEL$2 (#0) into SEL$3 (#0)
Query block (000000000FA3BF58) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT “R1”.”ID2″ “A”,”from$_subquery$_004″.”ID2_0″ “B” FROM “ROB”.”ROB_TEST1″ “R1”, LATERAL( (SELECT “R2”.”ID2″ “ID2_0″,”R2”.”ID1″ “ID1_1” FROM “ROB”.”ROB_TEST2″ “R2” WHERE “R1″.”ID1″=”R2”.”ID2″ AND (“R2”.”ID2″=1 OR “R2”.”ID2″=3)))(+) “from$_subquery$_004″
Query block (000000000FA3BF58) unchanged
Registered qb: SEL$64EAE176 0xfa3bf58 (VIEW MERGE SEL$3; SEL$2)
signature (): qb_name=SEL$64EAE176 nbfros=2 flg=0
fro(0): flg=0 objn=60322 hint_alias=”R1″@”SEL$2″
fro(1): flg=1 objn=0 hint_alias=”from$_subquery$_004″@”SEL$2”
CVM: Considering view merge in query block SEL$64EAE176 (#0)
CVM: Checking validity of merging SEL$1 (#0)
CVM: Considering view merge in query block SEL$1 (#0)
Query block (000000000FA3BF58) before join elimination:
SQL:******* UNPARSED QUERY IS *******
SELECT “R1”.”ID2″ “A”,”from$_subquery$_004″.”ID2_0″ “B” FROM “ROB”.”ROB_TEST1″ “R1”, LATERAL( (SELECT “R2”.”ID2″ “ID2_0″,”R2”.”ID1″ “ID1_1” FROM “ROB”.”ROB_TEST2″ “R2” WHERE “R1″.”ID1″=”R2”.”ID2″ AND (“R2”.”ID2″=1 OR “R2”.”ID2″=3)))(+) “from$_subquery$_004”
Query block (000000000FA3BF58) unchanged
CBQT: Validity checks failed for 26wqg6rfzhm9p.

On first apprearance it looks a crazy messy but there are intersting bits

SELECT “R1”.”ID2″ “A”,”from$_subquery$_004″.”ID2_0″ “B” FROM “ROB”.”ROB_TEST1″ “R1”, LATERAL( (SELECT “R2”.”ID2″ “ID2_0″,”R2”.”ID1″ “ID1_1” FROM “ROB”.”ROB_TEST2″ “R2” WHERE “R1″.”ID1″=”R2”.”ID2″ AND (“R2”.”ID2″=1 OR “R2”.”ID2″=3)))(+) “from$_subquery$_004”

I have never seen LATERAL before . A quick look up in the docs reveals that ‘The Oracle TABLE operator in the FROM clause is equivalent to the LATERAL operator in the standard.’

Oracle is creating a table with what appears to be some sort of alias “from$_subquery$_004” which is reference in the intial select “from$_subquery$_004”.”ID2_0″ “B”

After a bit of a play and a bit of reorganising I can up with this

SELECT R1.ID2 A, (SELECT R2.ID2 FROM ROB_TEST2 R2 WHERE R1.ID1 = R2.ID1 AND R2.ID2 IN (1,3) AND R1.ID2 IN (1,3)) B
FROM ROB_TEST1 R1

A B
———- ———-
1 1
2
3 3
4
5

There you go another way of constructing an outer join in Oracle’s syntax – something new, SQL can be very flexible. After a bit of googling I found out that this technique is called scalar subqueries. What I guess is interesting is that the optimizer could rewrite the ANSI syntax version but not the Oracle syntax.

First topic the letter A. Recently I have become more involved with different languages, which means considering database character sets. It is an area I know about but I would not say I knew too much about.

So first thing first what language do I need to handle – Greek. Being based in the UK, and normally handling characters from the english language on the installation of oracle I have the ‘WE8MSWIN1252’ character set

SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER = ‘NLS_CHARACTERSET’

NLS_CHARACTERSET WE8MSWIN1252

Can this handle Greek? A quick scan of the oracle documents at

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14225/applocaledata.htm#g683100

looking down and finding greek it appears not. Hmmm well what about the national character set

SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER IN = ‘NLS_NCHAR_CHARACTERSET’;

NLS_CHARACTERSET AL16UTF16

Checking again it seems that greek is ok for AL16UTF16 character set, which means the use of NVARCHAR2 for greek characters.

Onto the problem at hand. The requirments for our process use a case statement on a place field to put places into specific groups- for example

SELECT
CASE
WHEN PLACE LIKE ‘TO_NCHAR(‘AA%’) THEN 1
WHEN PLACE LIKE ‘TO_NCHAR(‘AB%’) THEN 2
ELSE -1
END
FROM TBL_NAMES

Looks easy enough however one place seem to always be put in the -1 group even though it ‘appears’ to begin with the letter ‘A’. Well once I checked the SQL, no mistakes, the next step is to take a closer look at the mysterious ‘AB’.

If anytime you get a problem with a string the command of choice is dump()

In the default character set:

select DUMP(‘AB’,1016) from dual;

DUMP(‘AB’,1016)
———————————————
Typ=96 Len=2 CharacterSet=WE8MSWIN1252: 41,42

In the national character set:

select DUMP(TO_NCHAR(‘AB’),1016) from dual

DUMP(TO_NCHAR(‘AB’),1016)
———————————————
Typ=1 Len=4 CharacterSet=AL16UTF16: 0,41,0,42

Hex for Capitial ‘A’ is 41. So what about dumping the mysterious ‘AB’

select dump(PLACE,1016) from places

DUMP(PLACE,1016)
——————————————————————————–
Typ=1 Len=4 CharacterSet=AL16UTF16: 3,91,3,92

Converting both to Unicode:

select UPPER(UNISTR(󠭇󠭈’\0391\0392′)) Test1 ,UPPER(UNISTR(‘\0041\0042’))Test2 from dual;

TEST1 TEST2
———————–
AB AB

There you go, visually the same, encoded they are different. A quick google for codes 0391 and 0392 reveil

0391 is Greek Capital Letter Alpha
0392 is Greek Capital Letter Beta