Check a Column Contains NULL or Empty using WHERE Clause in SQL

What is NULL?

NULL represents an undefined. It cannot be counted as an empty string (‘ ‘) or zero(0), it’s way different from these.
Sometimes, It’s not necessary to fill up each and every field with data/information, whenever be a record is inserted or updated. If a record is inserted only with targeted fields only (leaving other fields ), then automatically that particular field/column would get NULL value.

It can be avoided by using NOT NULL constraint that would prevent from entering NULL value.

Moreover, we cannot compare two NULL values with one other using operators.

How to know which field contain NULL value ?

For this, SQL provides us two operators , ‘IS NULL’ and ‘IS NOT NULL’ to know whether a particular column consists a NULL Value or not.

IS NULL

It will return all the records which has NULL values in its fields.

Syntax: Select Col1,Col2,… FROM table WHERE Col IS NULL;

IS NOT NULL

It will return all the records which hasn’t NULL values in its fields.

Syntax: Select Col1,Col2,… FROM table WHERE Col IS NOT NULL;

Check a column Contains NULL or Empty String using WHERE Clause in SQL?

Suppose there is a table named ‘customer’ and we want to find all the records of customers having “lastname” column containing null or empty string in the table.

Query :

SELECT * FROM   customers WHERE  customer_lastname IS NULL OR customer_lastname = ''

Output:

About Where Clause

In SQL, WHERE Clause is used to specify a condition and fetching the data from the given table. whether it is single table or by joining of multiple ones. If the condition is satisfied, the result comes out with specific values. So we should use the WHERE clause to filter the records and extract the results that fulfilling specific condition.
In SQL the WHERE clause not only used with the SELECT statements but also with the UPDATE, DELETE statements etc. for getting small clues, some examples/syntax are here to get through but for gaining more about it, we would examine these in next subsequent posts.

Syntax:

SELECT column1, column2, columnN
FROM table_name
WHERE condition;

For Example:

From an Extract of the table data which shows the List of Customers of different Countries and we filter out for some let’s say “India”. So, the Problems become is “List of Customers in India” and there columns contains ID, Name, Product, City, and Country. let’s find out how it filters:

1. SELECT ID, Name, Product, City, Country
2. FROM Customers
3. WHERE Country = ‘India’

Output:

There you go “Result” in before your eyes.

There are many Operators that can be used in WHERE clause as:
< > not equal
= equal
> grater than
< less than
LIKE, BETWEEN & IN are also the Operators which can be used in WHERE clause for searching a pattern, Between an inclusive range & to specify the multiple values for a column respectively.

This post was last modified on September 25, 2020

Sandeep Verma