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.
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.
Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course by GeeksforGeeks.
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
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, ....);
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.
Step 2: Insert Data into the Table
INSERT INTO table_name VALUES (value1, value2, value3, ...);
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.
Step 3: View Table Data
We can print the data in the table using the SELECT query as below.
SELECT * FROM table_name
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.
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.
SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
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.