Not Null in CockroachDB

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

In this document, we will explain the use of Not Null in CockroachDB from the following perspectives:

  • What? What does the NOT NULL operator do for us?
  • Why? In what situations do we want to use this operator in our Cockroach database work?
  • How? How do we use the NULL operator, combined with NOT, in our Cockroach SQL?

Prerequisites

  • Have a basic understanding of how to utilize the most commonly used SQL statements, either with CockroachDB alone, using Dbeaver or some other database administration tool, or by using server-side languages – like .Net or Python – that provide a way to connect to your database and send it SQL commands, whether that be to insert, update, or view records in your database. Note about Dbeaver: If you don’t immediately see support for Cockroach, please look deeper because it is a new development for Dbeaver to support Cockroach. The driver MAY be in beta still.
  • Basic understanding of the meaning of “NULL”.

What is NOT NULL?

In Cockroach, the NOT NULL operator is usually found in the WHERE clause; returns a true or false value, depending on the value a NULL comparison returns.

NOT NULL syntax

If we want to return all the records in our “users” table indiscriminately, we execute the following SQL statement:

1
2
SELECT txtNameFirst, txtNameLast, b_approved
FROM tblUsers

A NOT NULL use case

Notice the data returned in the view below. Pay particular attention to the field titled “b_approved”.

txtNameFirsttxtNameLastb_approved
BrunhildaJohnsontrue
JasmineFredericks
CarmineSmithsontrue
KimmyBerimy

Now let us say we want to return only the records where the user is marked in our database as “approved” (b_approved = true). But first, before we do that:

  • Give your Database Administrator some recognition and appreciation for his planet-sized brain.
  • Study the SQL statement above and the representation above of records returned from tblUsers.

Can you guess what new code we need to add to that SQL statement and how it will be written? It begins with the SQL “WHERE” clause. I’ll give you 10 seconds, so you can figure it out… or not.

OK. Assuming you didn’t peek. Let us look now at the SQL that filters our data so we see only users marked as “hawt”:

1
2
3
SELECT txtNameFirst, txtNameLast
FROM tblUsers
WHERE b_approved IS NOT NULL

The above SQL statement yields the following results:

txtNameFirsttxtNameLastb_approved
JasmineFrederickstrue
KimmyBerimytrue

Can you see why our results dropped from four rows to two?

As you most likely figured out, because you are a CockroachDB database genius with a solar system-sized brain, the difference is that we are testing the boolean value – which returns either a true or false (or NULL) value – from the “b_approved” field. If the value returned is NULL, the statement shows up for that row as false because it is not the same value as “NOT NULL” that we had tested for. The condition not being met, no record is returned for that row. Sort of like if you ask a woman to go out and she does not reply. She didn’t say yes or no. She didn’t reply with anything.

What about “false”?

You might inquire, “What if we get some “false” values in a row or rows and we don’t want to retrieve those records, either? Let us examine another example to increase clarity.

1
2
SELECT txtNameFirst, txtNameLast, b_approved
FROM tblUsers

Notice the data returned in the visual representation below. Of particular interest is the field called “b_approved”.

txtNameFirsttxtNameLastb_approved
BrunhildyJohnson
JasmineFrederickstrue
CarminaSmithsonfalse
KimmyBerimytrue

Do you see the difference? Carmina’s record – specifically the “b_approved” field – is no longer NULL. Take your DB Administrator – or whoever added these records – to dinner for being accurate with this. We now know that Carmina’s “b_approved” rating is “false”. But also notice that Brunhilda still hasn’t been updated. This is where we can start to get a deeper understanding of why we want to use NOT NULL in our WHERE clauses:

When a value is NULL.

This situation is common. So we are being good programmers who consider the future and protect ourselves as much as possible from future bugs by checking for NULL. That said, let us look at a tiny change we can make to our SQL so that we look for “false” values as well as NULL.

With the data we see above, if we use our previous SQL query that checks only for NOT NULL, we get the following three records:

txtNameFirsttxtNameLastb_approved
JasmineFrederickstrue
CarminaSmithsonfalse
KimmyBerimytrue

Close but not quite there: We want to still eliminate the row that has a “false” in it’s b_approved column.

1
2
3
4
SELECT txtNameFirst, txtNameLast, b_approved
FROM tblUsers
WHERE b_approved IS NOT NULL
AND b_approved = TRUE

With that change to the SQL, we are now seeing the results we wanted:

txtNameFirsttxtNameLastb_approved
JasmineFrederickstrue
KimmyBerimytrue

Can you see why our results dropped from three rows down to two? It’s because of the additional line of SQL code we tacked on to the WHERE clause via the AND operator:

1
AND b_approved = TRUE

That “AND b_approved = true” line instructs CockroachDB, “Hey Roachy Roacheson – we filtered the rows returned to make sure no rows with NULL are returned and we also want to be sure to leave out any records that have “false” in the “b_approved” field.

Conclusion

Here in this document we learned how to use the NOT NULL operator in CockroachDB queries to refine our data retrieval. We also explored a real world use case of why we may want to use the NOT NULL SQL operator. Did you understand and learn from the examples of eliminating some rows from returned recordsets using the WHERE clause?

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.