Use Laravel's leftJoin() when every row from the left table must remain even if the right table has no match. The most important pitfall is filter placement: a normal WHERE condition on a right-side column usually rejects the null-extended rows created by the left join, so that part of the result behaves like an inner join. Put match criteria in the join's ON clause when unmatched left rows must still survive.
Basic leftJoin()
use Illuminate\Support\Facades\DB;
$users = DB::table('users')
->leftJoin('orders', 'users.id', '=', 'orders.user_id')
->select([
'users.id',
'users.name',
'orders.id as order_id',
'orders.total as order_total',
])
->get();Users without orders remain in the result; their right-side columns are null.
The WHERE trap
$users = DB::table('users')
->leftJoin('orders', 'users.id', '=', 'orders.user_id')
->where('orders.status', 'paid')
->select('users.id', 'users.name', 'orders.id as order_id')
->get();An unmatched user has orders.status = null, so WHERE orders.status = 'paid' removes that row. If the requirement is “all users, plus a paid order when one exists,” put the status condition in the join.
Filter the right table in the ON clause
use Illuminate\Database\Query\JoinClause;
$users = DB::table('users')
->leftJoin('orders', function (JoinClause $join) {
$join->on('users.id', '=', 'orders.user_id')
->where('orders.status', '=', 'paid');
})
->select([
'users.id',
'users.name',
'orders.id as paid_order_id',
])
->get();SELECT users.id, users.name, orders.id AS paid_order_id
FROM users
LEFT JOIN orders
ON users.id = orders.user_id
AND orders.status = 'paid';Now every user remains, while only paid orders are eligible to match.
Join closures can express multiple ON conditions
$rows = DB::table('users as u')
->leftJoin('orders as o', function (JoinClause $join) {
$join->on('u.id', '=', 'o.user_id')
->where('o.status', '=', 'paid')
->whereNull('o.deleted_at');
})
->select([
'u.id',
'u.email',
'o.id as order_id',
'o.total as order_total',
])
->get();Laravel's JoinClause value conditions stay inside the join expression. That is different from adding an outer query where() after the join.
Use aliases and explicit columns
Both tables may have id, status, created_at, and updated_at. Avoid an unqualified * when duplicate names matter to the returned object/array. Make the result contract explicit with qualified columns and aliases.
Bindings and SQL injection
Normal Query Builder conditions bind values. Keep request values in binding-aware methods. If raw SQL is necessary, use a raw method that accepts bindings rather than concatenating request strings. Dynamic identifiers such as column names cannot be value-bound; validate them against a fixed allowlist.
Counting matching rows while preserving zero
$rows = DB::table('users')
->leftJoin('orders', 'users.id', '=', 'orders.user_id')
->select('users.id', 'users.name')
->selectRaw('COUNT(orders.id) AS order_count')
->groupBy('users.id', 'users.name')
->get();COUNT(orders.id) ignores the null right-side ID for unmatched users, producing zero. COUNT(*) counts the joined output row itself and can produce 1 for an unmatched user, which is usually wrong for “number of orders.”
One-to-many joins duplicate parent rows
If one user has five matching orders, that user appears five times unless you aggregate or otherwise restrict the right side. A left join guarantees preservation of left rows; it does not guarantee one output row per left row.
Join or Eloquent relationship?
- Join: useful for SQL-level filtering, ordering, grouping, or projection across tables in one result set.
- Eager-loaded relationships: better when you need hydrated related models and domain behavior.
- withCount(): useful when you need relationship counts without hand-writing a join/group query.
- whereHas()/whereDoesntHave(): clearer when the question is relationship existence rather than joined columns.
Avoid N+1 when you choose relationships
$users = User::query()
->withCount('orders')
->get();Do not replace a join with lazy loading inside a loop unless you intentionally accept one query per parent. A join is not automatically faster than a relationship query; choose the correct data shape first, then measure.
Users with no orders
$users = User::query()
->whereDoesntHave('orders')
->get();This communicates the business question directly. Use a left join plus whereNull('orders.id') when the join shape itself is needed in a broader query.
Debugging checklist
- Must unmatched left rows survive?
- Did a right-table condition accidentally go into outer
WHERE? - Are duplicate column names aliased?
- Does a one-to-many join multiply the left row?
- Would
withCount,whereHas, or eager loading be clearer? - Are raw values bound and dynamic identifiers allowlisted?