Signup/Sign In
JUNE 6, 2023

How to check for NULL value in SQL WHERE clause in MySQL

Technology #howto#mysql#sql

    If you have a column that gets a NULL default value in your SQL database, then you should be careful how you define your SQL query to match the column value against NULL.

    • You should not use equal to (=) operator or not equal to (!=) operator for comparison with a NULL value

    • You should also not consider the NULL value as an empty value.

    • The NULL value and None are different in SQL database tables.

    • The NULL value and the empty value are also different.

    A NULL value is not a zero value or an empty string, or a column that contains just spaces. A NULL value means the column was not assigned any value at the time of data INSERT.

    Using IS NULL in the WHERE clause

    • The easiest way to check for a NULL value in a column in the WHERE clause is by using IS NULL operator.

    • You can use it in MySQL and in SQLServer you can use the ISNULL() function.

    Let's see a simple SQL query in which we will use IS NULL in the WHERE clause.

    SELECT * FROM my_table WHERE my_column IS NULL;

    In the query above,

    • my_table is the name of the table.

    • my_column is the name of the column that can have a NULL value.

    You can also use IS NULL operator with multiple columns in the WHERE clause by using AND.

    Here is an example,

    SELECT * FROM my_table WHERE my_column1 IS NULL AND my_column2 IS NULL;

    Using IS NOT NULL in the WHERE clause

    • If you want to check if the value in a column is not equal to NULL then you can use IS NOT NULL operator.

    • You can use IS NOT NULL operator for checking for non-zero values or non-empty/empty strings, in simple words anything that is not null.

    Let's see a simple SQL query in which we will use IS NOT NULL in the WHERE clause.

    SELECT * FROM my_table WHERE my_column IS NOT NULL;

    In the query above,

    • my_table is the name of the table.

    • my_column is the name of the column.

    Using IS NULL and IS NOT NULL in the same query

    Let's see a simple SQL query where we will use both IS NULL and IS NOT NULL operators,

    SELECT * FROM my_table WHERE my_column1 IS NULL AND my_column2 IS NOT NULL;

    That's it. So next time if you have to compare a column value for NULL, don't use = or != operators with empty quotes ' ' or zero, instead use the IS NULL or IS NOT NULL operators in SQL.

    I like writing content about C/C++, DBMS, Java, Docker, general How-tos, Linux, PHP, Java, Go lang, Cloud, and Web development. I have 10 years of diverse experience in software development. Founder @ Studytonight
    IF YOU LIKE IT, THEN SHARE IT
    Advertisement

    RELATED POSTS