Sybase and Nulls

If you have not read about how PowerBuilder treats nulls, you may want to read  nulls first.

Unlike PowerBuilder, Sybase SQL treats variables set to null just like null is a particular value.  In other words, Sybase SQL resolves 2 different variables, both set to null, as being equal to each other.  In the PB script, li_null = li_null is neither TRUE or FALSE, it is null.  But in Sybase SQL, @li_null = @li_null is TRUE.

declare @li_null int,
@li_null2 int

select @li_null = null
select @li_null2 = null
select @li_null
select @li_null2

-- Returns 1
-- variable with null value = itself
select 1
where @li_null = @li_null

-- Returns 2
-- variable with null value = another variable = null
select 2
where @li_null = @li_null2

-- No rows returned
select 3
where @li_null <> @li_null

-- No rows returned
select 4
where @li_null <> @li_null2

Things get trickier when dealing with tables with null columns.  A column with a value of null works like PowerBuilder.  That is, if #TEMP_TABLE contains one row and its KEY column is null, #TEMP_TABLE.KEY = #TEMP_TABLE.KEY resolves to null, not TRUE.

But if you compare the same column to a literal null or a variable with the value of null, it works like Sybase SQL variable.  #TEMP_TABLE.KEY = null resolves to TRUE.

--  Fun with nulls and joins
create table #temp1
(key1 int null,
data1 char(1))

Insert into #temp1 (key1, data1) Values (null, "A")
Insert into #temp1 (key1, data1) Values (2, "B")
Insert into #temp1 (key1, data1) Values (3, "C")

create table #temp2
(key2 int null,
data2 char(1))

Insert into #temp2 (key2, data2) Values (null, "X")
Insert into #temp2 (key2, data2) Values (2, "Y")
Insert into #temp2 (key2, data2) Values (999, "Z")

select * from #temp1
select * from #temp2

-- limit retrieve to one row
select * from #temp1, #temp2
where data1 = "A"
and   data2 = "X"

-- -- one row retrieved
-- select * from #temp1, #temp2
-- where data1 = "A"
-- and   data2 = "X"
-- and   data2 = data2

-- no rows retrieved
-- null column does not join to null column
select * from #temp1, #temp2
where data1 = "A"
and   data2 = "X"
and   key1  = key2   -- join

-- no rows retrieved
-- null column excluded if it tries to = ietself
select * from #temp1, #temp2
where data1 = "A"
and   data2 = "X"
and   key1  = key1

-- 1 rows retrieved
-- not null null column does not excluded its row,  if it tries to = ietself
select * from #temp1, #temp2
where data1 = "B"
and   data2 = "Y"
and   key1  = key1

-- retrieves 1 row
-- null column =  variable with null value
select * from #temp1, #temp2
where data1 = "A"
and   data2 = "X"
and   key1  = @li_null

-- returns 1 row
-- null column = null literal
select * from #temp1, #temp2
where data1 = "A"
and   data2 = "X"
and   data2 = data2
and   key1  = null

-- returns no row
-- null column = itself (same column)
select * from #temp1, #temp2
where data1 = "A"
and   data2 = "X"
and   key1  = key1

-- returns no row
-- join on null to same table
select * from #temp1 t1a, #temp1 t1b
where t1a.data1 = "A"
and   t1b.data1 = "A"
and   t1a.key1  = t1b.key1

drop table  #temp1
drop table  #temp2

About these ads