The NOT NULL condition is an important concept in SQL, but it is one that can be a little tricky to get your head around – particularly if you are still fairly new to the workings of SQL, and the commands and nuances that bring the magic to life.
To help you out, we have put together everything you need to know about “NOT NULL”, allowing you to make the most of all the SQL has to offer.
What Is SQL?
Before we take a closer look at more specific commands, it is important to establish what we mean by SQL, and to understand the basics – this ensures that a strong groundwork has been laid.
The acronym “SQL” stands for Structured Query Language, and simply put, this is a language that allows us to ask our database server questions, and have it give back some information based on the request you have made.
In many ways, SQL acts as a translator between you and your database: when you send it a question, it gives you back the answer.
One of the main advantages of SQL is that it is easy to use (and this simplicity is why people love it) – and so much so that there are many great tools to help you work with queries.
The most popular tool is typically Microsoft Excel; however, you can also run SQL directly from within your browser using Google Chrome or Firefox.
How Do I Run SQL Commands Within My Browser?
When you open up any program like Microsoft Excel, you may notice a menu bar along the top where you will find various options such as formatting tables, etc. We’re going to focus on two commands: ‘Insert’ and ‘Query’.
These commands both allow you to enter a query into your database and then retrieve that data, and we will explain this in greater detail below.
Using The Insert Command
In order to insert data into your database, you must first define exactly what it is you want to add. This could be anything from simple text, numbers, dates, images, videos, or even another table within the original table.
If you wish to add other content, your first step is to create a “table” that describes each piece of data and then populate each entry within the table. The next step is to add values into the table – this will be useful when we learn more about NULL values.
To begin, click the ‘query’ tab located at the bottom of your screen. Selecting this option will present you with a blank text box, which you can then type in whatever you like. As you begin typing, you can either just keep entering commas, or separate each value with a semicolon.
Once complete, hit the enter key on your keyboard.
This next step might seem trivial, but it is actually incredibly important. When you save your database file, you must ensure that you always select the correct encoding.
For example, if your computer uses UTF-8 encoding, then every single character will have a special code associated with it. Your database server is no different.
Therefore, you must tell it what encoding you are using. This means that your database does not receive invalid characters or ones that don’t belong to your particular encoding. This is why you need to specify the encoding when saving files.
If you do not include the proper coding, your database may not display properly and show garbled results. Fortunately, there is a simple way to check that you are correctly specifying your encoding; look for an icon that looks like a little padlock by the name Encrypt Database.
Clicking this icon will bring up a dialog box, asking you to confirm whether you wish to encrypt your database. Choose Yes, to proceed. Then press Okay to continue. After clicking okay, take note of the code displayed below.
This number represents the encoding used by your computer and cannot be modified by the user. Only your system administrator can modify this setting. It is safe to leave this value unchanged. The final step is to exit out of all programs before returning to your database.
Using The Query Command
Once you have entered data, you need to know how to view it – and this is achieved by using the Query command. To access your queries, click the ‘Queries’ tab at the very bottom of the window.
This allows you to see not only the queries you entered earlier, but also the ones currently stored in your database. To start viewing your current queries, click the word ‘Current’ found beneath this tab.
Next, select one of your pre-existing queries. Simply scroll through the list until you find yours, and double-click to open it. After opening the query, you have several options available to you:
Edit The Row
You can edit the entire row by double-clicking anywhere in the row. Upon selecting Edit Row, you are presented with two options. First, you can change the data you entered previously. While this is useful for editing small changes without having to reenter the information, it is less optimal than the second choice.
The second option is to remove any unwanted data from that field entirely. This is done via a button labeled Remove Value.
After removing an item or items from a field, you can immediately update the contents of that specific field using Update Field. Simply double-click inside the desired cell to initiate this process.
The final action available in the queries list is the ability to delete entries – this can be achieved by right-clicking on a cell containing the deleted record. A menu will appear allowing you to Delete Record(s) From Table.
Select Yes, to permanently remove that entry from the table. If you do so, make sure you close the query window before exiting out of your program.
So What Is A NULL Value?
Now that we have taken a closer look at how SQL works, and the basic commands, it is time to consider the question of NULL values. In the simplest terms, a NULL value is simply a field with no value.
If a field within a table in your database is optional, then users will be able to update an existing record, or add a brand-new record, without the need to add a specific value to the field – the field is therefore saved as a “NULL” value – there is nothing in it.
There is often some confusion between NULL values and fields with spaces or zero values. The former is a field that has been left intentionally blank when the record was created, rather than not yet having a value assigned to it.
What Is A Not NULL Condition?
A Not NULL Condition is a specific condition used within SQL to test for any non-NULL values. If a non-NULL value is found within the table, then the condition will return TRUE. If, however, there is no non-NULL value found in the table, the result will return as FALSE.
A Not NULL condition can be used in an INSERT, SELECT, DELETE, or UPDATE statement, depending on your experience, preferences, and requirements.
As an example, an IS NOT NULL condition can be used in a SELECT statement in the following manner. Imagine that you have a table filled with products, as follows:
- Banana 50
- Orange 50
- Pear 50
- Bread 75
- Apple 25
- Tissues NULL
If you enter a SELECT SQL statement – SELECT*FROM products WHERE category_id IS NOT NULL, your results will display:
- Banana 50
- Orange 50
- Pear 50
- Bread 75
- Apple 25
In other words, you will be shown all records in the table whose customer_id does not contain a “NULL” value.
IS NOT NULL is a good way to test if something is null or not null. This SQL statement will select all records from the product’s database, making it easier to find and access records.
Once you have tried this condition a few times, with a range of statement types and product tables, you will soon start to see and enjoy the benefits of the NOT NULL condition.