How to Retrieve Records containing Null Values: SQL Programming

In Sql programming, when programmer want to retrieve records that have null values in their columns. To get such type of records, sql queries must have NULL operator in the where clause.

A NULL value in a column implies that the data value for the column is not available. You might be required to find records that contain null values or records that do not contain NULL values in a particular column. In such a case, you can use the unknown_value_operator in your sql queries.

The syntax of using the unknown_value_operator in the SELECT query is:

SELECT column_list
FROM table_name
WHERE column_name unknown_value_operator

Where

• Column_list: list of fields to be shown in output.
• Table_name: from the records are to be retrieved.
• unknown_value_operator is either the keyword IS NULL or IS NOT NULL.

The following SQL query retrieves only those rows from the EmployeeDepartmentHistory table for which value in the EndDate column is NULL.

SELECT BusinessEntityID, EndDate FROM
HumanResources.EmployeeDepartmentHistory WHERE EndDate IS NULL

There are few records falling in this category, shown in the output.

Consider its opposite case, where some records have some date in the EndDate column.

SELECT BusinessEntityID, EndDate FROM
HumanResources.EmployeeDepartmentHistory WHERE EndDate IS NOT NULL

It will shows few records as shown in the output.