SQL IS NULL and IS NOT NULL

The IS NULL and IS NOT NULL operators in SQL are used to test for the presence or absence of a value in a column. In relational databases, it is common for columns to have missing values, which are represented as NULL. The IS NULL and IS NOT NULL operators provide a convenient way to filter data based on whether or not a value is present in a column.

IS NULL operator

The IS NULL operator is used to test if a column contains a NULL value. The syntax for using the IS NULL operator is as follows:

1SELECT column1, column2, ...
2FROM table_name
3WHERE column_name IS NULL;

For example, consider a table named employees with a column named middle_name. If you want to retrieve all employees who do not have a middle name, you could use the following query:

1SELECT *
2FROM employees
3WHERE middle_name IS NULL;

IS NOT NULL operator

The IS NOT NULL operator is used to test if a column does not contain a NULL value. The syntax for using the IS NOT NULL operator is as follows:

1SELECT column1, column2, ...
2FROM table_name
3WHERE column_name IS NOT NULL;

For example, consider the same employees table as above. If you want to retrieve all employees who have a middle name, you could use the following query:

1SELECT *
2FROM employees
3WHERE middle_name IS NOT NULL;