How to perform a SQL query for fields that are null (or not null)

For some reason I can never remember how to search for database table fields that are either NULL or NOT NULL. I always try to use the = operator or something else.

So, for myself, here’s an example of how to perform a SQL query and find all records where a field in a database table is NULL:

SELECT * FROM foo WHERE bar IS NULL;

And here’s how to perform a SQL query showing all records in a database table where a column is NOT NULL:

SELECT * FROM foo WHERE bar IS NOT NULL;

This is standard SQL, and works with every database I’ve tried, including MySQL, Oracle, Postgres, and SQL Server.

Add new comment

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.