SQL Null Values

Introduction to SQL Null Values

SQL null values are nothing but the column field, which does not contain any value. If suppose one field is optional in an application, we are creating this field as null, accepting the null values. This empty field will save as null values in SQL. It is different from the zero or the field which contains the spaces; it does not have any values.

What are SQL Null Values?

• SQL null values keyword is used to represent the value that was missing. The SQL null value in the table contains the field as blank. We can say that if a field does not contain any value, this field has null values.
• In the RDBMS systems, a null value will indicate an unknown value. We often see in the SQL table which field does not contain any value; at the time of entry, we have not filled this field; after that, this field will appear as a null value in the database. SQL supports the null values.

How do SQL Null Values Works?

The null value is nothing but a field that does not contain any data.

Below syntax shows how we are working with select queries by using sql null values:

Syntax:

Select name_of_column1, name_of_column2, …name_of_columnN from name_of_table where name_of_column = NULL;

In the above syntax, we can see that we have defined all the columns from the table and are using the where condition on one column, which contains the null values.

Code:

select * from sql_insert1 where id = NULL;

Output:

While using the above syntax will return the empty resultset because all fields do not contain an empty value in our column.

We can use the null condition to return the rows which contain the null values.

Below is the syntax of SQL is null values as follows:

Syntax:

Select name_of_column1, name_of_column2, …name_of_columnN from name_of_table where name_of_column is NULL;

While using the above syntax in the below query will return rows whose column contains the null values.

In the below example, we have used the stud_grade column in the where condition to retrieve rows containing the null values from the stud_grade column.

Code:

select * from sql_insert1 where stud_grade is NULL;

Output:

We can also handle the null values by using functions like isnull. This function will take the two parameters as input.

Below is the syntax of IS null function as follows:

Syntax:

ISNULL(expr, replacement)

We can also use coalesce function while working with null values. This function takes the input of unlimited parameters and returns the first not null value as output.

Below is the syntax of coalesce function as follows:

Syntax:

Coalesce(val1, val2, vale3, …, valN)

Below is the example of coalesce function; while working with SQL, null values are as follows.

The below example will return five as output because it will come first as not null values. The 5 is not the null value from the list we have provided with the coalesce function.

Example:

Code:

select COALESCE(null, null, null, null, 5, null, 10, 15, null);

Output:

In the below example, we are using coalesce function on the stud_grade column. Hence, we can see that it will be returning the not null values as follows.

Code:

Select coalesce (stud_grade) from sql_insert1 where id = 1001;

Output:

If suppose we have not defined not a null constraint at the time of creating a table, by default table column is created with null constraints.

The below example shows by default table column is created with a null constraint.

Code:

Create table null_example (id int NOT NULL, name varchar(10));
Insert into null example values (101);

Output:

The above example shows that we have not used any constraint when creating the name column. But when inserting values into the table, we have not used any value to name columns; rows are inserted into the table because, by default, the table column is created with null constraints.

Below syntax shows how we are defining null constraint at the time of creating the table as follows:

Syntax:

Create table name_of_table (name_of_column datatype NULL, name_of_column datatype NULL, name_of_column datatype NULL);

The example below shows how we must create a table with a null constraint on the column.

In the example below, we created a null constraint on the stud_name and stud_addr column. We are not defining any constraint on the id column; this column will create a null constraint.

Code:

Create table null_example1 (id int, stud_name varchar(10) null, stud_addr varchar(10) null);

Output:

SQL Null Values Count and Column

As we know, that count function is used to count the number of rows from a table. Also, we can count the rows as per the specified condition we have used in the query.

We use the below query to count the number of rows from a table.

Code:

Select count(*) from sql_insert1;

Output:

The above example shows that it will retrieve the count of all the not null and null rows. Suppose we want to find the count of only null values; we use the below queries.

Below query is retrieving count of not null and a count of null values.

Code:

SELECT SUM (CASE WHEN stud_grade is null THEN 1 ELSE 0 END) AS Null_values , COUNT(stud_grade) AS Non_Null FROM sql_insert1;

Output:

The above example shows that it will retrieve the count of null values as seven and the count of not null values as 5.

While using a column with a null constraint, we retrieve all the values that contain the null.

In the below example, we can see that we are retrieving all the values from the stud_phone and stud_addr column from which the value of the stud_grade column is null.

Code:

SELECT stud_grade, stud_phone, stud_addr FROM sql_insert1 WHERE stud_grade IS NULL; 

Output:

Conclusion

SQL null values keyword is used to represent the value that was missing. SQL null values are nothing but column field which not contains any value. If suppose one field is optional in the application, we create this field as null, accepting the null values.

Recommended Articles

This is a guide to SQL Null Values. Here we discuss the introduction, working, and SQL null values count and column, respectively. You may also have a look at the following articles to learn more –

1. SQL ORDER BY DESC
2. SQL EXECUTE
3. SQL EXCLUDE
4. MySQL InnoDB Cluster