NULL 最大,但即不大于1,也不小于1
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
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.
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.
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.
转载于:https://blog.51cto.com/alex888/822028