Laravel: whereNull & whereNotNull Eloquent Query with Example

By Parth Patel on Oct 07, 2020

Laravel Database query builder is a very powerful and convenient interface for building SQL queries directly in Laravel. It uses PDO binding behind the scenes you won't have to worry about SQL Injection attacks.

Here, we will talk two of many methods provided by Laravel Database query builder and can be used both in Database query builder as well as Eloquent Query - whereNull() and whereNotNull() and same methods but in OR conditions - orWhereNull() and orWhereNotNull().

These methods are very simple actually and easy to use. Let's review the syntax:

Syntax:

->whereNull('columnname')

->whereNotNull('columnname')

->orWhereNull('columnname')

->orWhereNotNull('columnname')

whereNull() & orWhereNull() Query Example:

whereNull() method will put conditions that column must be empty

orWhereNull() method will too mean that column must be empty but it's with OR connector.

Let's review example for both whereNull() and orWhereNull() query builder methods:

//Let's find users with null (empty) names:

$users = User::whereNull('name')->get();
//select * from users where name is null;

//oh wait! I also want to check non-verified users
//hmm.. so basically I want to get all users whose name is empty OR who are not email verified yet
//Let's try orWhereNull()!

$users = User::whereNull('name')->orWhereNull('email_verified_at')->get();
//select * from users where name is null or email_verified_at is null;

//hurray!! Got my results

This is simple example to explain the usage of whereNull() and orWhereNull(). Ofcourse, you can and will use it in bit more complex eloquent queries.

whereNotNull() & orWhereNotNull() Query Example:

whereNotNull() method means column must be not null

orWhereNotNull() method is same as whereNotNull() except it uses OR connector.

Let's review example:

//Let's find users whose email is not null (we need emails, right?)

$users = User::whereNotNull('email')->get();
//(select * from users where email is not null;)

//Eh! Though Email is not that necessary for me! I can live with names if emails are not present!

//Okay, let's use orWhereNotNull() too then

$users = User::whereNotNull('email')->orWhereNotNull('name')->get();
//(select * from users where email is not null or name is not null;)

//Result? Users must have atleast one of two columns - name or Email. Both can be present, no issues there

So, this is how you can use null shorthand for Laravel Query where command in various ways to check against null values in database.