oracle中null的影响,数据库中NULL对排序和选择条件的影响（Oracle and DB2）

NULL 最大，但即不大于1，也不小于1

-----------ORACLE

SQL> create table a(i integer,j integer);

Table created.

SQL> insert into a values(1,2);

1 row created.

SQL> insert into a(i) values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from a;

I          J

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

1          2

2

SQL> select * from a order by j desc;

I          J

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

2

1          2

SQL> c/desc/asc

1* select * from a order by j asc

SQL> /

I          J

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

1          2

2

SQL> select * from a where j>1;

I          J

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

1          2

SQL> c/>/<

1* select * from a where j<1

SQL> /

no rows selected

关于大于小于order by的试验：----------DB2

db2 =>

db2 => create table a(i integer,j integer);

create table a(i integer,j integer)

DB20000I  The SQL command completed successfully.

db2 =>  insert into a values(1,2);

insert into a values(1,2)

DB20000I  The SQL command completed successfully.

db2 =>  insert into a(i) values(2);

insert into a(i) values(2)

DB20000I  The SQL command completed successfully.

db2 =>  select * from a order by j desc;

select * from a order by j desc

I           J

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

2           -

1           2

2 record(s) selected.

db2 => select * from a order by j asc

db2 (cont.) => ;

select * from a order by j asc

I           J

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

1           2

2           -

2 record(s) selected.

db2 => select * from a where j>1;

select * from a where j>1

I           J

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

1           2

1 record(s) selected.

db2 => select * from a where j<1;

select * from a where j<1

I           J

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

0 record(s) selected.

ORACLE 与DB2的处理是一模一样的。

---------------------------关于NULL 与 ''  -------------------DB2------------

db2 => create table b(i integer,j char(10));

create table b(i integer,j char(10))

DB20000I  The SQL command completed successfully.

db2 => insert into b values (1,'111');

insert into b values (1,'111')

DB20000I  The SQL command completed successfully.

db2 => insert into b values (2,'');

insert into b values (2,'')

DB20000I  The SQL command completed successfully.

db2 => insert into b(i) values (3);

insert into b(i) values (3)

DB20000I  The SQL command completed successfully.

db2 => commit;

commit

DB20000I  The SQL command completed successfully.

db2 => select * from b;

select * from b

I           J

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

1 111

2

3 -

3 record(s) selected.

db2 => select * from b where j is null;

select * from b where j is null

I           J

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

3 -

1 record(s) selected.

db2 => select * from b where j='';

select * from b where j=''

I           J

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

2

1 record(s) selected.