Today, I have found a funny problem when use "Union" in Oracle.
 Just like this,

 I have two tables, one calls "TB_STUDENT" and the other "TB_RESOURCE"

TB_STUDENT
{
    WORKER_ID NVARCHAR2(40),
    SEX INTEGER
}

TB_RESOURCE
{
    RESOURCE_ID NVARCHAR2(40),
     ...
}

for the two tables, "TB_STUDENT" has some records while "TB_RESOURCE" has no record!

then, I try the following SQL sentence:

SELECT WORKER_ID FROM TB_STUDENT
UNION
SELECT '' FROM TB_RESOURCE

it got an error "ORA-12704: 字符集不匹配";

I thought maybe it was caused by '', because '' (empty string) denotes the NULL value in Oracle.
So, changed it to ' ';

Unfortunately, it still didn't work!

...

oh, maybe it requires strictly type check!

SELECT WORKER_ID FROM TB_STUDENT
UNION
SELECT CAST('' AS NVARCHAR2(40)) FROM TB_RESOURCE

ok,run passed!

// new added, 05-09-27
oftenly, we think it would be "OK" when using Parameter.
for instance,
 
SELECT LMS_TEACHER.TEACHER_ID FROM LMS_TEACHER WHERE LMS_TEACHER.CODE='I0001' AND LMS_TEACHER.TEACHER_ID<>'';

I use this sentence to find whether there exist a record with "LMS_TEACHER.CODE='I0001'", because LMS_TEACHER.TEACHER_ID is a primary key, so there is no record accord with the condition "LMS_TEACHER.TEACHER_ID<>''". And in the program, I use a sentence with parameter instead the front sentence, just like:

SELECT LMS_TEACHER.TEACHER_ID FROM LMS_TEACHER WHERE LMS_TEACHER.CODE=@NewCode AND LMS_TEACHER.TEACHER_ID<>@TeacherID;

Unfortunately, it returns no record when there is a record with LMS_TEACHER.CODE='I0001' ;
Then I change [<>''] to [IS NOT NULL], it runs ok.

the same result appeared when I try the following setences:
SELECT * FROM LMS_TEACHER WHERE LMS_TEACHER.TEACHER_ID<>' '; (ok)
SELECT * FROM LMS_TEACHER WHERE LMS_TEACHER.TEACHER_ID<>'';  (no record)
SELECT * FROM LMS_TEACHER WHERE LMS_TEACHER.TEACHER_ID IS NOT NULL; (ok)

[ :( it scratches my head over ]