Learning SQL - Nulls and Nothings and Zeroes

These tips are mostly applicable to all forms of SQL, some are specific to SQLite and versions of SQLite like RealSQL Databases and RealSQL Server databases.

Null is not zero

Null is not zero, null is not spaces, null is literally nothing - it is an absence of data. Sounds quite spiritual doesn't it? Would a Zen database be empty or full of Null?

When you create a column in a table you can state that the column is not null, that means that a value must be entered in that column or the insert (or update) will fail.

If you've been playing along with the lessons you will have a QiSQL_Parents table. We have not populated the Title or Middle_Name columns, so they are both NULL. So let's try a query:

select * from QiSQL_Parents where title=null;

What? No Rows Returned??? OK, this is because null doesn't exist so title can not be equal to it. You can not add anything to null. Null will not allow itself to be multiplied by anything - the result will always be Null.

This query will work:

select * from QiSQL_Parents where title IS NULL;

We are not comparing anything to Null we are asking a simple question - is the value Null? We could also use this query for a Not Null value:

select * from QiSQL_Parents where title NOTNULL;

Seeing Nulls

How can we see Null if it doesn't exist? We can ask SQLite to convert null values to a specified value using 'coalesce'. Lets first insert a new Parent with a title and middle name into our QiSQL_Parents table:

insert into QiSQL_Parents ( first_name , last_name , title, middle_name ) values (
'Albert' , 'Henderson' , 'Reverend' , 'Bartholomew' );

Try this query:

select last_name ,
coalesce ( title ,'No Title' ) title ,
coalesce ( middle_name,'No Middle Name' ) middle_name
from QiSQL_Parents ;

This is what we see:

last_name No Title middle_name
Smith No Title No Middle Name
Jones No Title No Middle Name
Brown No Title No Middle Name
Brown No Title No Middle Name
Henderson Reverend Bartholomew

So we can see that coalesce converts null into our chosen value. Using coalesce can be very important when we want to calculate values in queries. Let's create another table and insert some test values:

drop table if exists QiSQL_Calc_Test;

create table QiSQL_Calc_Test (
value1 numeric , value2 numeric , value3 numeric );

insert into QiSQL_Calc_Test ( value1 ) values ( 10 );
insert into QiSQL_Calc_Test ( value1 ) values ( 20 );
insert into QiSQL_Calc_Test ( value1 ) values ( 30 );
insert into QiSQL_Calc_Test ( value1 , value2 ) values ( 100, 200 );
insert into QiSQL_Calc_Test ( value1 , value2 ) values ( 300, 400 );
insert into QiSQL_Calc_Test ( value3 ) values ( 10 );
insert into QiSQL_Calc_Test ( value3 ) values ( 20 );

Now, say we want to add the two values in these columns a simple query should work:

select value1 + value2 total_value from QiSQL_Calc_Test;

total_value
 
 
 
300
700

Notice that first three rows are empty (they are actually Null) - adding anything to Null returns Null! To get useful data back from this query we have to use coalesce again:

select coalesce( value1 ,0 ) + coalesce ( value2,0) total_value from QiSQL_Calc_Test;

total_value
10
20
30
300
700

Coalesce can be much more clever than a simple substitution see if you can work out what this query does:

select coalesce( value1 ,value2 , value3,0 ) +
coalesce ( value2,value1, value3,0) total_value
from QiSQL_Calc_Test ;

Coalesce will use the first Not Null value it encounters in the list- so in this example if value1 is Null it will check value2, if that is null it will check value3, if that is null it will use the default zero.