SQL Query to Exclude Null Values

In this article, we will look at how to exclude Null Values from the table using a SQL query.  

Null Value:

A null value indicates no value. It means that the column value is absent in a row. A null value is not the same as a blank space or a zero value. A zero value is an integer and a blank space is a character while a null value is the one that has been left blank.

To exclude the null values from a table we have to create a table with null values. So, let us create a table.

Step 1: Creating table

Syntax:

CREATE TABLE table_name (
   column1 datatype,
   column2 datatype,
   column3 datatype,
  ....);

Query:

CREATE TABLE Student(Name varchar(40),
Department varchar(30),Roll_No int, );

Using the above query student table is created in our database. The student table has three fields Name, Department, and Roll Number of a student. To insert values in the table we have to use the INSERT query.

Output:

Step 2: Insert  Data into the Table

Syntax:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Query:

INSERT INTO Student
VALUES ('Rahul Sharma','Electronics',15),
      ('Soha Shaikh','Computer Science',NULL),
      ('Vivek Rao',NULL,31),
      ('Sonali Rane','Electronics',20);

Using the above query we have added the data to our table. We used the NULL keyword to insert NULL values.

Output:

Step 3: View Table Data

We can print the data in the table using the SELECT query as below.

Syntax:

SELECT * FROM table_name

Query:

SELECT * FROM Student

The output will show the table with all the fields because we used ‘*’ in the query. It means that select all fields within the table.

Output:

Step 4: Exclude Null Values

To exclude the null values from the table we need to use IS NOT NULL operator with the WHERE clause.

• WHERE Clause:
  • The WHERE clause is used to filter the records.
  • It will extract those records that fulfill the condition.
  • It can be used with SELECT, UPDATE, DELETE queries.
• IS NOT NULL Operator: 
  • This operator is used to test for non-empty values.

Syntax:

SELECT column_names
 FROM table_name
WHERE column_name IS NOT NULL;

Query:

SELECT * FROM Student
WHERE Name IS NOT NULL
AND Department IS NOT NULL
AND Roll_No IS NOT NULL;

To exclude the null values from all the columns we used AND operator. AND operator shows the record if all the conditions are true.

Output: