comparisons.sql

derby database source code.good for you.

-- create a table with char columns of different lengths

create table chartab (c1 char(1), c2 char(5));

-- insert some values

insert into chartab values (' ', '     ');
insert into chartab values ('a', 'a    ');
insert into chartab values ('b', 'bcdef');
insert into chartab values (null, null);

-- select each one in turn
select c1 from chartab where c1 = ' ';
select c2 from chartab where c2 = '     ';
select c1 from chartab where c1 = 'a';
select c2 from chartab where c2 = 'a    ';
select c1 from chartab where c1 = 'b';
select c2 from chartab where c2 = 'bcdef';

-- now check for end-of-string blank semantics
select c1 from chartab where c1 = '';
select c1 from chartab where c1 = '                      ';
select c2 from chartab where c2 = '';
select c2 from chartab where c2 = ' ';
select c2 from chartab where c2 = '                           ';
select c1 from chartab where c1 = 'a        ';
select c2 from chartab where c2 = 'a ';
select c1 from chartab where c1 = 'b             ';
select c2 from chartab where c2 = 'bcdef                ';
select c2 from chartab where c2 = 'bcde       ';

-- now check null = null semantics
select c1, c2 from chartab where c1 = c2;

-- test is null semantics
select c1 from chartab where c1 is null;
select c1 from chartab where c1 is not null;
select c1 from chartab where not c1 is null;

-- Now test <>
select c1 from chartab where c1 <> ' ';
select c2 from chartab where c2 <> '     ';
select c1 from chartab where c1 <> 'a';
select c2 from chartab where c2 <> 'a    ';
select c1 from chartab where c1 <> 'b';
select c2 from chartab where c2 <> 'bcdef';

select c1 from chartab where c1 != ' ';
select c2 from chartab where c2 != '     ';
select c1 from chartab where c1 != 'a';
select c2 from chartab where c2 != 'a    ';
select c1 from chartab where c1 != 'b';
select c2 from chartab where c2 != 'bcdef';

-- now check for end-of-string blank semantics
select c1 from chartab where c1 <> '';
select c1 from chartab where c1 <> '                      ';
select c2 from chartab where c2 <> '';
select c2 from chartab where c2 <> ' ';
select c2 from chartab where c2 <> '                           ';
select c1 from chartab where c1 <> 'a        ';
select c2 from chartab where c2 <> 'a ';
select c1 from chartab where c1 <> 'b             ';
select c2 from chartab where c2 <> 'bcdef                ';
select c2 from chartab where c2 <> 'bcde       ';

-- now check null <> null semantics
select c1, c2 from chartab where c1 <> c2;

-- Now test <
select c1 from chartab where c1 < ' ';
select c2 from chartab where c2 < '     ';
select c1 from chartab where c1 < 'a';
select c2 from chartab where c2 < 'a    ';
select c1 from chartab where c1 < 'b';
select c2 from chartab where c2 < 'bcdef';

-- now check for end-of-string blank semantics
select c1 from chartab where c1 < '';
select c1 from chartab where c1 < '                      ';
select c2 from chartab where c2 < '';
select c2 from chartab where c2 < ' ';
select c2 from chartab where c2 < '                           ';
select c1 from chartab where c1 < 'a        ';
select c2 from chartab where c2 < 'a ';
select c1 from chartab where c1 < 'b             ';
select c2 from chartab where c2 < 'bcdef                ';
select c2 from chartab where c2 < 'bcde       ';

-- now check null < null semantics
select c1, c2 from chartab where c1 < c2;

-- Now test >
select c1 from chartab where c1 > ' ';
select c2 from chartab where c2 > '     ';
select c1 from chartab where c1 > 'a';
select c2 from chartab where c2 > 'a    ';
select c1 from chartab where c1 > 'b';
select c2 from chartab where c2 > 'bcdef';

-- now check for end-of-string blank semantics
select c1 from chartab where c1 > '';
select c1 from chartab where c1 > '                      ';
select c2 from chartab where c2 > '';
select c2 from chartab where c2 > ' ';
select c2 from chartab where c2 > '                           ';
select c1 from chartab where c1 > 'a        ';
select c2 from chartab where c2 > 'a ';
select c1 from chartab where c1 > 'b             ';
select c2 from chartab where c2 > 'bcdef                ';
select c2 from chartab where c2 > 'bcde       ';

-- now check null > null semantics
select c1, c2 from chartab where c1 > c2;

-- Now test <=
select c1 from chartab where c1 <= ' ';
select c2 from chartab where c2 <= '     ';
select c1 from chartab where c1 <= 'a';
select c2 from chartab where c2 <= 'a    ';
select c1 from chartab where c1 <= 'b';
select c2 from chartab where c2 <= 'bcdef';

-- now check for end-of-string blank semantics
select c1 from chartab where c1 <= '';
select c1 from chartab where c1 <= '                      ';
select c2 from chartab where c2 <= '';
select c2 from chartab where c2 <= ' ';
select c2 from chartab where c2 <= '                           ';
select c1 from chartab where c1 <= 'a        ';
select c2 from chartab where c2 <= 'a ';
select c1 from chartab where c1 <= 'b             ';
select c2 from chartab where c2 <= 'bcdef                ';
select c2 from chartab where c2 <= 'bcde       ';

-- now check null <= null semantics
select c1, c2 from chartab where c1 <= c2;

-- Now test >=
select c1 from chartab where c1 >= ' ';
select c2 from chartab where c2 >= '     ';
select c1 from chartab where c1 >= 'a';
select c2 from chartab where c2 >= 'a    ';
select c1 from chartab where c1 >= 'b';
select c2 from chartab where c2 >= 'bcdef';

-- now check for end-of-string blank semantics
select c1 from chartab where c1 >= '';
select c1 from chartab where c1 >= '                      ';
select c2 from chartab where c2 >= '';
select c2 from chartab where c2 >= ' ';
select c2 from chartab where c2 >= '                           ';
select c1 from chartab where c1 >= 'a        ';
select c2 from chartab where c2 >= 'a ';
select c1 from chartab where c1 >= 'b             ';
select c2 from chartab where c2 >= 'bcdef                ';
select c2 from chartab where c2 >= 'bcde       ';

-- now check null >= null semantics
select c1, c2 from chartab where c1 >= c2;

-- create a table with a few varchar columns.  All varchar vs. varchar
-- comparisons must be done between columns, because there are no varchar
-- constants in the language

create table varchartab (c1 varchar(1), c2 varchar(1), c3 varchar(5),
			 c4 varchar(5));

-- insert some values

insert into varchartab values ('', '', '', '');
insert into varchartab values ('a', 'a', 'a', 'a');
insert into varchartab values ('b', 'b', 'bcdef', 'bcdef');
insert into varchartab values (null, null, null, null);
insert into varchartab values ('', null, '', null);
insert into varchartab values ('a', 'b', 'a', 'b');
insert into varchartab values ('b', '', 'b', 'bcdef');

-- select the ones where the columns are equal
select c1 from varchartab where c1 = c2;
select c3 from varchartab where c3 = c4;

-- test varchar = char semantics.  Test with trailing blanks.
select c1 from varchartab where c1 = '                 ';
select c1 from varchartab where c1 = '';
select c1 from varchartab where c1 = 'a ';
select c1 from varchartab where c1 = 'b                               ';
select c1 from varchartab where c1 = 'bb';
select c3 from varchartab where c3 = ' ';
select c3 from varchartab where c3 = '';
select c3 from varchartab where c3 = 'a    ';
select c3 from varchartab where c3 = 'bcdef                   ';
select c3 from varchartab where c3 = 'bbbb';

-- test char = varchar semantics.  Test with trailing blanks.
select c1 from varchartab where '                 ' = c1;
select c1 from varchartab where '' = c1;
select c1 from varchartab where 'a ' = c1;
select c1 from varchartab where 'b                               ' = c1;
select c1 from varchartab where 'bb' = c1;
select c3 from varchartab where ' ' = c3;
select c3 from varchartab where '' = c3;
select c3 from varchartab where 'a    ' = c3;
select c3 from varchartab where 'bcdef                   ' = c3;
select c3 from varchartab where 'bbbb' = c3;

-- Now test <>
select c1 from varchartab where c1 <> c2;
select c3 from varchartab where c3 <> c4;

-- test varchar <> char semantics.  Test with trailing blanks.
select c1 from varchartab where c1 <> '                 ';
select c1 from varchartab where c1 <> '';
select c1 from varchartab where c1 <> 'a ';
select c1 from varchartab where c1 <> 'b                               ';
select c1 from varchartab where c1 <> 'bb';
select c3 from varchartab where c3 <> ' ';
select c3 from varchartab where c3 <> '';
select c3 from varchartab where c3 <> 'a    ';
select c3 from varchartab where c3 <> 'bcdef                   ';
select c3 from varchartab where c3 <> 'bbbb';

select c1 from varchartab where c1 != '                 ';
select c1 from varchartab where c1 != '';
select c1 from varchartab where c1 != 'a ';
select c1 from varchartab where c1 != 'b                               ';
select c1 from varchartab where c1 != 'bb';
select c3 from varchartab where c3 != ' ';
select c3 from varchartab where c3 != '';
select c3 from varchartab where c3 != 'a    ';
select c3 from varchartab where c3 != 'bcdef                   ';
select c3 from varchartab where c3 != 'bbbb';

-- test char <> varchar semantics.  Test with trailing blanks.
select c1 from varchartab where '                 ' <> c1;
select c1 from varchartab where '' <> c1;
select c1 from varchartab where 'a ' <> c1;
select c1 from varchartab where 'b                               ' <> c1;
select c1 from varchartab where 'bb' <> c1;
select c3 from varchartab where ' ' <> c3;
select c3 from varchartab where '' <> c3;
select c3 from varchartab where 'a    ' <> c3;
select c3 from varchartab where 'bcdef                   ' <> c3;
select c3 from varchartab where 'bbbb' <> c3;

-- Now test <
select c1 from varchartab where c1 < c2;
select c3 from varchartab where c3 < c4;

-- test varchar < char semantics.  Test with trailing blanks.
select c1 from varchartab where c1 < '                 ';
select c1 from varchartab where c1 < '';
select c1 from varchartab where c1 < 'a ';
select c1 from varchartab where c1 < 'b                               ';
select c1 from varchartab where c1 < 'bb';
select c3 from varchartab where c3 < ' ';
select c3 from varchartab where c3 < '';
select c3 from varchartab where c3 < 'a    ';
select c3 from varchartab where c3 < 'bcdef                   ';
select c3 from varchartab where c3 < 'bbbb';

-- test char < varchar semantics.  Test with trailing blanks.
select c1 from varchartab where '                 ' < c1;
select c1 from varchartab where '' < c1;
select c1 from varchartab where 'a ' < c1;
select c1 from varchartab where 'b                               ' < c1;
select c1 from varchartab where 'bb' < c1;
select c3 from varchartab where ' ' < c3;
select c3 from varchartab where '' < c3;
select c3 from varchartab where 'a    ' < c3;
select c3 from varchartab where 'bcdef                   ' < c3;
select c3 from varchartab where 'bbbb' < c3;

-- Now test >
select c1 from varchartab where c1 > c2;
select c3 from varchartab where c3 > c4;

-- test varchar > char semantics.  Test with trailing blanks.
select c1 from varchartab where c1 > '                 ';
select c1 from varchartab where c1 > '';
select c1 from varchartab where c1 > 'a ';
select c1 from varchartab where c1 > 'b                               ';
select c1 from varchartab where c1 > 'bb';
select c3 from varchartab where c3 > ' ';
select c3 from varchartab where c3 > '';
select c3 from varchartab where c3 > 'a    ';
select c3 from varchartab where c3 > 'bcdef                   ';
select c3 from varchartab where c3 > 'bbbb';

-- test char > varchar semantics.  Test with trailing blanks.
select c1 from varchartab where '                 ' > c1;
select c1 from varchartab where '' > c1;
select c1 from varchartab where 'a ' > c1;
select c1 from varchartab where 'b                               ' > c1;
select c1 from varchartab where 'bb' > c1;
select c3 from varchartab where ' ' > c3;
select c3 from varchartab where '' > c3;
select c3 from varchartab where 'a    ' > c3;
select c3 from varchartab where 'bcdef                   ' > c3;
select c3 from varchartab where 'bbbb' > c3;

-- Now test <=
select c1 from varchartab where c1 <= c2;
select c3 from varchartab where c3 <= c4;

-- test varchar <= char semantics.  Test with trailing blanks.
select c1 from varchartab where c1 <= '                 ';
select c1 from varchartab where c1 <= '';
select c1 from varchartab where c1 <= 'a ';
select c1 from varchartab where c1 <= 'b                               ';
select c1 from varchartab where c1 <= 'bb';
select c3 from varchartab where c3 <= ' ';
select c3 from varchartab where c3 <= '';
select c3 from varchartab where c3 <= 'a    ';
select c3 from varchartab where c3 <= 'bcdef                   ';
select c3 from varchartab where c3 <= 'bbbb';

-- test char <= varchar semantics.  Test with trailing blanks.
select c1 from varchartab where '                 ' <= c1;
select c1 from varchartab where '' <= c1;
select c1 from varchartab where 'a ' <= c1;
select c1 from varchartab where 'b                               ' <= c1;
select c1 from varchartab where 'bb' <= c1;
select c3 from varchartab where ' ' <= c3;
select c3 from varchartab where '' <= c3;
select c3 from varchartab where 'a    ' <= c3;
select c3 from varchartab where 'bcdef                   ' <= c3;
select c3 from varchartab where 'bbbb' <= c3;

-- Now test >=
select c1 from varchartab where c1 >= c2;
select c3 from varchartab where c3 >= c4;

-- test varchar >= char semantics.  Test with trailing blanks.
select c1 from varchartab where c1 >= '                 ';
select c1 from varchartab where c1 >= '';
select c1 from varchartab where c1 >= 'a ';
select c1 from varchartab where c1 >= 'b                               ';
select c1 from varchartab where c1 >= 'bb';
select c3 from varchartab where c3 >= ' ';
select c3 from varchartab where c3 >= '';
select c3 from varchartab where c3 >= 'a    ';
select c3 from varchartab where c3 >= 'bcdef                   ';
select c3 from varchartab where c3 >= 'bbbb';

-- test char >= varchar semantics.  Test with trailing blanks.
select c1 from varchartab where '                 ' >= c1;
select c1 from varchartab where '' >= c1;
select c1 from varchartab where 'a ' >= c1;
select c1 from varchartab where 'b                               ' >= c1;
select c1 from varchartab where 'bb' >= c1;
select c3 from varchartab where ' ' >= c3;
select c3 from varchartab where '' >= c3;
select c3 from varchartab where 'a    ' >= c3;
select c3 from varchartab where 'bcdef                   ' >= c3;
select c3 from varchartab where 'bbbb' >= c3;

-- test is null semantics
select c1 from varchartab where c1 is null;
select c1 from varchartab where c1 is not null;
select c1 from varchartab where not c1 is null;

-- clean up
drop table inttab;
drop table smallinttab;
drop table biginttab;
drop table chartab;
drop table varchartab;