Joins and Relationship Existence Queries in Laravel: join, leftJoin, and whereHas

Laravel offers two genuinely different mechanisms for querying across related tables — an explicit SQL join (join(), leftJoin()), and whereHas(), which filters by relationship existence without a literal join at all.

A basic inner join

$orders = DB::table('orders')
    ->join('customers', 'orders.customer_id', '=', 'customers.id')
    ->select('orders.*', 'customers.name as customer_name')
    ->get();

An inner join only returns rows that have a genuine match in both tables — an order whose customer_id doesn't match any row in customers (an orphaned record) would be silently excluded entirely from these results.

A left join, to include unmatched rows

$customers = DB::table('customers')
    ->leftJoin('orders', 'customers.id', '=', 'orders.customer_id')
    ->select('customers.name', 'orders.total')
    ->get();

leftJoin() includes every row from the left table (customers) regardless of whether a match exists in orders — a customer with no orders yet still appears in the results, with null for the order-related columns, which a plain inner join() would have excluded entirely.

Joining with Eloquent relationships, as an alternative to a raw join

$orders = Order::with('customer')->get();

foreach ($orders as $order) {
    echo $order->customer->name;
}

Using an Eloquent relationship with eager loading, rather than a manual join(), is generally the simpler and more common approach in a typical Laravel app — reaching for a raw join is usually done specifically for performance in a case needing to filter or aggregate across both tables in a single query, not just to display related data.

whereHas(): filtering by relationship existence, without a join

$customersWithOrders = Customer::whereHas('orders')->get();

This returns every customer who has at least one order — genuinely different from a join, since it doesn't duplicate the customer row per matching order, and it doesn't require selecting any columns from the related table at all.

whereHas() with an additional condition on the related model

$customersWithLargeOrders = Customer::whereHas('orders', function ($query) {
    $query->where('total', '>', 500);
})->get();

This finds customers who have at least one order over $500 — the closure's condition applies specifically to the related orders table, and a customer appears in the results at most once, regardless of how many qualifying orders they actually have.

whereDoesntHave(): the inverse — records with no matching relationship

$customersWithNoOrders = Customer::whereDoesntHave('orders')->get();

This is a genuinely awkward query to express with a plain join — it would need a leftJoin() combined with a whereNull() check on the joined table's key column — while whereDoesntHave() expresses the same intent directly and more readably.

Counting related records with withCount()

$customers = Customer::withCount('orders')->get();

foreach ($customers as $customer) {
    echo "{$customer->name}: {$customer->orders_count} orders";
}

withCount() adds a computed {relation}_count attribute to each model via a single efficient subquery — this is generally more efficient than eager loading the entire orders relationship and then calling count() on the loaded collection in PHP, since it avoids fetching the actual order rows at all when only a count is needed.

When to reach for a join instead of whereHas/withCount

A raw join() is the right tool specifically when actual columns from the related table need to appear in the result set, or when aggregating across both tables together in one query (like summing order totals grouped by customer) — whereHas() and withCount() cover the more common cases of filtering or counting by a relationship without needing the related data directly in the output.