Skip to content
Related Articles

Related Articles

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:

My Personal Notes arrow_drop_up
Recommended Articles
Page :