joins — join(), leftJoin(), rightJoin(), crossJoin()
Concept
JOINs combine rows from multiple tables. The Query Builder provides methods for all standard SQL join types.
join(string $table, $first, string $operator, $second): INNER JOIN. Only rows with matches in both tables.
leftJoin(): LEFT OUTER JOIN. All rows from the left table, NULL for non-matching right columns.
rightJoin(): RIGHT OUTER JOIN. All rows from the right table (rarely used in Laravel).
crossJoin(string $table): Cartesian product — every row from left × every row from right. No ON clause.
Advanced join with closure: ->join('table', fn($join) => $join->on('a.id', '=', 'b.a_id')->where('b.active', true)). The closure receives a JoinClause builder, allowing multiple ON conditions and WHERE clauses within the join.
joinSub(Closure|Builder|string, string $alias, ...): Join a subquery: ->joinSub(fn($q) => $q->from('orders')->whereNotNull('completed_at'), 'o', 'o.user_id', '=', 'users.id').
Column ambiguity: When joining tables that share column names (both have id, created_at), always qualify with table name: ->select('users.id', 'orders.id as order_id'). Without aliasing, later columns overwrite earlier ones in the stdClass object.
Performance: Joins execute in the database engine — efficient for large datasets. Eloquent eager loading uses separate queries in PHP. For reporting, use joins. For model relationships, use eager loading.
Code Example
<?php
use Illuminate\Support\Facades\DB;
// INNER JOIN — only users with at least one order
$usersWithOrders = DB::table('users')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.id', 'users.name', 'orders.total', 'orders.created_at as order_date')
->get();
// LEFT JOIN — all users, even those with no orders
$usersWithOptionalOrders = DB::table('users')
->leftJoin('orders', 'users.id', '=', 'orders.user_id')
->select('users.id', 'users.name', DB::raw('COUNT(orders.id) as order_count'))
->groupBy('users.id', 'users.name')
->get();
// Multiple joins
$report = DB::table('order_items')
->join('orders', 'orders.id', '=', 'order_items.order_id')
->join('products', 'products.id', '=', 'order_items.product_id')
->join('users', 'users.id', '=', 'orders.user_id')
->select(
'users.name as customer',
'products.name as product',
'order_items.quantity',
'order_items.price'
)
->get();
// Advanced join with closure — multiple ON conditions
DB::table('users')
->join('role_user', function($join) {
$join->on('users.id', '=', 'role_user.user_id')
->where('role_user.expires_at', '>', now()) // extra condition
->whereNull('role_user.deleted_at');
})
->join('roles', 'roles.id', '=', 'role_user.role_id')
->select('users.name', 'roles.name as role')
->get();
// joinSub — join a subquery
$latestOrders = DB::table('orders')
->select('user_id', DB::raw('MAX(created_at) as latest_order_at'))
->groupBy('user_id');
DB::table('users')
->joinSub($latestOrders, 'lo', 'lo.user_id', '=', 'users.id')
->select('users.name', 'lo.latest_order_at')
->get();