nothing but null - sql · null values are by default omitted in all aggregate functions select...
TRANSCRIPT
![Page 1: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/1.jpg)
Nothing But NULLMadhivanan
www.sql-articles.com - www.sql-articles.com/cssug/
![Page 2: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/2.jpg)
NULL can be defined as
Absence of value
Undefined
The value which is unknown at this point of time.
www.sql-articles.com - www.sql-articles.com/cssug/
![Page 3: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/3.jpg)
All data types can be defined with NULL constraint
www.sql-articles.com - www.sql-articles.com/cssug/
![Page 4: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/4.jpg)
Direct usage of arithmetic or logical operations on NULL will not work as expected
declare @test table(data int)
insert into @test
select 1 union all
select 4 union all
select null
select data from @test
where data<10
union all
select data from @test
where data>=10
www.sql-articles.com - www.sql-articles.com/cssug/
![Page 5: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/5.jpg)
The system functions ISNULL, COALESE and NULLIF are used only to deal with NULL
www.sql-articles.com - www.sql-articles.com/cssug/
![Page 6: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/6.jpg)
NOT NULL constraint cannot be defined for a computed column until it is also PERSISTED
www.sql-articles.com - www.sql-articles.com/cssug/
![Page 7: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/7.jpg)
The only data types that will interpret NULL differently are row version and timestamp
declare @rv rowversion , @ts timestamp
select @rv=null, @ts=null
select @rv as rowversion,@ts as timestamp
www.sql-articles.com - www.sql-articles.com/cssug/
![Page 8: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/8.jpg)
NULL=NULL will never be true unless SET ANSI_NULLS is OFF‐‐Query 1
set ansi_nulls on
select 6
where null=null
‐‐Query 2
set ansi_nulls off
select 6
where null=null
www.sql-articles.com - www.sql-articles.com/cssug/
![Page 9: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/9.jpg)
ANSI_NULLS do not affect IS NULL condition
declare @t table(i int)
insert into @t
select 1 union all
select null
‐‐Query 1
set ansi_nulls on
select i from @t
where i is null
‐‐Query 2
set ansi_nulls off
select i from @t
where i is null
www.sql-articles.com - www.sql-articles.com/cssug/
![Page 10: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/10.jpg)
Typed NULL and non-Typed NULL
Select sum(null)
Declare @i intSet @i=nullSelect sum(@i)
www.sql-articles.com - www.sql-articles.com/cssug/
![Page 11: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/11.jpg)
Implicit conversion
Select sum(null+0)
www.sql-articles.com - www.sql-articles.com/cssug/
![Page 12: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/12.jpg)
Column with UNIQUE constraint will allow only one NULL value in SQL Server
(But not true in other RDBMSs)
www.sql-articles.com - www.sql-articles.com/cssug/
![Page 13: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/13.jpg)
NULL will make SQL Server to use short circuit logic in some cases
select 6/0/null
select null/6/0
www.sql-articles.com - www.sql-articles.com/cssug/
![Page 14: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/14.jpg)
The value NULL is not equal to string value 'NULL'
www.sql-articles.com - www.sql-articles.com/cssug/
![Page 15: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/15.jpg)
By default NULL values come first when a column is ordered in ascending order and come last when ordered in descending order
www.sql-articles.com - www.sql-articles.com/cssug/
![Page 16: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/16.jpg)
If you don't use GROUP BY clause, the aggregate functions will always return single value (NULL) when the condition is false
select sum(col) as col from(select 45 as col) as twhere 1=0
www.sql-articles.com - www.sql-articles.com/cssug/
![Page 17: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/17.jpg)
Some aggregates return NULL even when no data in the table
declare @t table(i int)select i from @t‐‐No rows affected
select sum(i) from @t
www.sql-articles.com - www.sql-articles.com/cssug/
![Page 18: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/18.jpg)
NULL values are by default omitted in all aggregate functionsselect sum(col) as col_sum,count(col)as col_count,avg(col*1.0) as col_avgfrom(select 1 as col union allselect null as col union allselect 2 as col union allselect 3 as col) as t
www.sql-articles.com - www.sql-articles.com/cssug/
![Page 19: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/19.jpg)
Aggregate functions cannot be directly applied over NULL value
SELECT SUM(NULL),COUNT(NULL)
www.sql-articles.com - www.sql-articles.com/cssug/
![Page 20: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/20.jpg)
NULL is not an empty string
(Oracle treats them same)
www.sql-articles.com - www.sql-articles.com/cssug/
![Page 21: Nothing But NULL - SQL · NULL values are by default omitted in all aggregate functions select sum(col) as col_sum,count(col) as col_count,avg(col*1.0) as col_avg from (select 1 as](https://reader030.vdocuments.us/reader030/viewer/2022011921/6030f5bb962f9b56661ef531/html5/thumbnails/21.jpg)
Contact me
Blog http://beyondrelational.com/blogs/madhivanan
Twitter : https://twitter.com/madhivanan2001
www.sql-articles.com - www.sql-articles.com/cssug/