Core SAP HANA SQL script concepts- SQL NULL and NOT

Welcome to the sixth installment of this SAP HANA SQL Scripts core concepts section where we try to understand the concept of nothing – SQL NULL and also later dwell into negating WHERE conditions with the NOT operator.

Welcome to nothingness – SQL NULL

NULL is nothing. NULL is not zero or a blank space. It is just the absence of anything or in simpler terms – nothing. Sometimes, for some rows of data, we don’t have the corresponding information. Databases fill these cells of missing information with a symbol for NULL. In SAP HANA, NULL is symbolized in data preview as ? by default. This can be changed in the HANA Studio preferences but you don’t need to do so. The ? is not a question mark. It is just a symbolic representation of NULL.

To demonstrate this, I created a new table which shows the employee ID and the corresponding years of experience he/she has in another column called YRS_EXP. But while filling the data, I filled in a few nulls which you can see in the data preview below marked by arrows. The ? here means that the data for these employee IDs is simply missing in the database.

If you wish to do some SELECT operation to check which of these employees have years of experience missing in their data, you just have to filter on YRS_EXP to be equal to  NULL. NULL is the keyword used to denote the null values in SQL SCRIPT codes. Using this logic, we draft the below code.

On execution, we get nothing. Something’s wrong. YRS_EXP = NULL doesn’t seem to be the correct statement.

In these cases, you have to use IS NULL keyword instead of = NULL. This is one of the mistakes beginners tend to make.

Upon execution, we get the results we require correctly this time.

1

NOT KEYWORD

The NOT is an interesting keyword. It negates every filter just by being placed in front of it. For example, instead of IS NULL in the previous example, we write IS NOT NULL in the filter and execute it.

The result shows 46 entries thereby excluding the 4 null values that our previous SELECT showed.

I hope this was easy to understand and follow. Please read the next part of these tutorials to learn how to update, delete data in a table and at the end, we learn how to delete the tables too.

Make sure to share this content on social media using the share buttons below to show your support to this website and to keep it alive.

<<Previous Tutorial                                                                                                              Next Tutorial>>