SQL Query to Exclude Null Values
Last Updated :
23 Sep, 2021
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:
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...