Laravel Eloquent LEFT JOIN WHERE NULL

In Laravel, you can use the leftJoin method on a query builder instance to perform a left join, and then use the whereNull method to only include records where a column from the right-hand table is null:

Example of Laravel Query Builder LEFT JOIN WHERE NULL

<?php

use Illuminate\Support\Facades\DB;

$users = DB::table('users')
  ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
  ->whereNull('posts.id')
  ->get();

This will select all users, and any matching posts. If a user does not have any posts, the columns from the posts table will be null. The whereNull condition will only include users for which the id column from the posts table is null, meaning they do not have any posts.

The resulting collection will contain all columns from the users table, and any columns from the posts table that match the leftJoin condition, with null values for the columns from the posts table for users that do not have any posts.

Note on column selection: To avoid fetching unnecessary data, you can specify only the columns you need using select():

$users = DB::table('users')
  ->select('users.*')
  ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
  ->whereNull('posts.id')
  ->get();

Idiomatic Eloquent Alternative: If you are working with Eloquent models, the recommended approach is to use doesntHave():

$users = App\Models\User::doesntHave('posts')->get();