Laravel Eloquent whereNull and whereNotNull Query Method

Laravel query builder provides simple way to write query and fetch data from database server. Laravel's where() method allows you to filter data with any field. Besides where() method, there is also few methods which you can simply use.

In this example, I will show you how you can fetch records based on field is NULL or NOT NULL.

whereNull() method

Laravel's whereNull() method only fetch records of null value of given column. Let's take example, suppose you only want to get records of users who doesn't have added their description.

$users = \DB::table('users')
    ->whereNull('description')
    ->get();

The above query will fetch only users which doesn't have description. The above query is the same as below sql query.

SELECT * from users WHERE description IS NULL;

whereNotNull() method

In the same way, whereNotNull() method is used to fetch only records with no null values for the given column. Here is the example for that.

$users = \DB::table('users')
    ->whereNotNull('description')
    ->get();

The above query will fetch only users which have description. The above query is the same as below sql query.

SELECT * from users WHERE description IS NOT NULL;

Multiple whereNotNull() method

Suppose now you want to fetch records where three of any columns have not null value. Then you can use advance query to get records. In the following example, all users records will fetch who has atleast one id added:

$users = \DB::table('users')
    ->where(function($query) {
       return $query->whereNotNull('github_id')
           ->orWhereNotNull('gitlab_id')
           ->orWhereNotNull('bitbucket_id');
    })->get();

The above query does the same as below SQL query.

SELECT * FROM users WHERE (github_id IS NOT NULL OR gitlab_id IS NOT NULL OR bitbucket_id IS NOT NULL);

I hope this will help you.