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.
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.