How to Filter Rows Without Null in a Column in SQL?
Last Updated :
21 Apr, 2021
Here we will see, how to filter rows without null in a column of an MS SQL Server’s database table with the help of a SQL query using IS NOT NULL operator.
For the purpose of demonstration, we will be creating a demo_orders table in a database called “geeks“.
Creating the Database:
Use the below SQL statement to create a database called geeks:
CREATE DATABASE geeks;
Using the Database:
Use the below SQL statement to switch the database context to geeks:
USE geeks;
Table Definition:
We have the following demo table in our geeks database.
CREATE TABLE demo_orders(
ORDER_ID INT IDENTITY(1,1) PRIMARY KEY, --IDENTITY(1,1) is same as AUTO_INCREMENT in MySQL.
--Starts from 1 and increases by 1 with each inserted row.
ITEM_NAME VARCHAR(30) NOT NULL,
ORDER_DATE DATE
);
You can use the below statement to query the description of the created table:
EXEC SP_COLUMNS demo_orders;
Adding data to the table:
Use the below statement to add data to the demo_orders table:
INSERT INTO demo_orders --no need to mention columns explicitly as we
--are inserting into all columns and ID gets
--automatically incremented.
VALUES
('Maserati', '2007-10-03'),
('BMW', '2010-07-23'),
('Mercedes Benz', '2012-11-12'),
('Ferrari', '2016-05-09'),
('Lamborghini', '2020-10-20');
To verify the contents of the table use the below statement:
SELECT * FROM demo_orders;
Now let’s insert some rows with no values ( or null values) in order_date column.
INSERT INTO demo_orders(ITEM_NAME)
VALUES
('NullRowOne'),
('NullRowTwo'),
('NullRowThree');
The table after the newly inserted data would be as:
Below is the syntax to filter the rows without a null value in a specified column.
Syntax:
SELECT * FROM <table_name>
WHERE <column_name> IS NOT NULL;
Example:
SELECT * FROM demo_orders
WHERE ORDER_DATE IS NOT NULL;
--Will output the rows consisting of non null order_date values.
Output:
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...