This guide shows working Laravel Query Builder join examples and the exact output shape so you can visualize what each join returns.

What is a JOIN in Laravel?

A JOIN combines rows from two or more tables based on a related column. In Laravel, you can express joins using the Query Builder (DB::table) or by relying on Eloquent relationships (with()). Joins help you fetch related data in a single, efficient query.

Quick Reference of Laravel Join Methods

1 — Inner Join (users + orders)

An Inner Join returns only the rows that have matching values in both tables.

Tables

Laravel Query Builder (Inner Join)

$results = DB::table('users')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.id as user_id', 'users.name', 'orders.id as order_id', 'orders.total')
    ->get();

Expected Output

[
  {"user_id":1,"name":"Alice","order_id":1,"total":"50.00"},
  {"user_id":1,"name":"Alice","order_id":2,"total":"25.00"},
  {"user_id":2,"name":"Bob","order_id":3,"total":"100.00"}
]

Explanation: Carol (id 3) has no orders, so she is not included in an inner join. Each row corresponds to a matching user–order pair.

2 — Left Join (Users Without Orders)

A Left Join returns all rows from the left table (users) and the matched rows from the right table (orders). If there is no match, the right-side columns are NULL.

Laravel Query Builder (Left Join)

$results = DB::table('users')
    ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.id as user_id', 'users.name', 'orders.id as order_id', 'orders.total')
    ->orderBy('users.id')
    ->get();

Expected Output

[
  {"user_id":1,"name":"Alice","order_id":1,"total":"50.00"},
  {"user_id":1,"name":"Alice","order_id":2,"total":"25.00"},
  {"user_id":2,"name":"Bob","order_id":3,"total":"100.00"},
  {"user_id":3,"name":"Carol","order_id":null,"total":null}
]

Explanation: leftJoin() returns all users. For Carol, there are no matching orders, so the order fields are returned as null.

3 — Right Join (Orders With Missing Users)

A Right Join is the mirror of a left join. It returns all rows from the right table (orders), even if there's no matching row in the left table (users).

Sample Tables

Laravel Query Builder (Right Join)

$results = DB::table('users')
    ->rightJoin('orders', 'users.id', '=', 'orders.user_id')
    ->select(
        'orders.id as order_id',
        'orders.total',
        'users.name as user_name'
    )
    ->orderBy('orders.id')
    ->get();

Expected Output

[
  {"order_id":1,"total":"50.00","user_name":"Alice"},
  {"order_id":2,"total":"25.00","user_name":"Alice"},
  {"order_id":3,"total":"100.00","user_name":"Bob"},
  {"order_id":4,"total":"200.00","user_name":null}
]

Explanation: Order 4 has user_id = 99 with no corresponding user. Since this is a right join, the order is still included, but the user_name column returns null.

4 — Multiple Joins (users, orders, products)

You can chain multiple joins together to combine data from three or more related tables into a single result set.

Tables

Laravel Query Builder (Multiple Join)

$results = DB::table('users')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->join('products', 'orders.product_id', '=', 'products.id')
    ->select('users.name', 'orders.id as order_id', 'products.title as product', 'orders.total')
    ->orderBy('orders.id')
    ->get();

Expected Output

[
  {"name":"Alice","order_id":1,"product":"T-Shirt","total":"50.00"},
  {"name":"Alice","order_id":2,"product":"Coffee Mug","total":"25.00"},
  {"name":"Bob","order_id":3,"product":"T-Shirt","total":"100.00"}
]

Explanation: This chains two inner joins to return user, order, and product data in one query.

5 — Aggregates with Joins (Total Spent Per User)

Joins are often used with aggregate functions (SUM, COUNT, AVG) to calculate metrics across related tables.

Goal

For each user, return their name and the total_spent.

Query

$results = DB::table('users')
    ->leftJoin('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.id','users.name', DB::raw('COALESCE(SUM(orders.total), 0) as total_spent'))
    ->groupBy('users.id', 'users.name')
    ->orderBy('users.id')
    ->get();

Expected Output

[
  {"id":1,"name":"Alice","total_spent":"75.00"},
  {"id":2,"name":"Bob","total_spent":"100.00"},
  {"id":3,"name":"Carol","total_spent":"0.00"}
]

Notes:

1. We use leftJoin() so users without orders (like Carol) are still included.

2. COALESCE(SUM(orders.total), 0) (or IFNULL in some databases) ensures that a null sum for a user with no orders is returned as 0.

3. The groupBy clause must include all non-aggregated selected columns.

6 — joinSub() (Latest Order Per User)

The joinSub() method allows you to join a table against the results of another query (a subquery or derived table).

Goal

Attach each user’s latest order date (latest_at) to their profile.

Subquery (Finds latest order date for each user)

$latest = DB::table('orders')
    ->select('user_id', DB::raw('MAX(created_at) as latest_at'))
    ->groupBy('user_id');

JoinSub Usage

$results = DB::table('users')
    ->leftJoinSub($latest, 'latest_orders', function($join) {
        $join->on('users.id', '=', 'latest_orders.user_id');
    })
    ->select('users.id','users.name','latest_orders.latest_at')
    ->get();

Expected Output

[
  {"id":1,"name":"Alice","latest_at":"2025-08-05"},
  {"id":2,"name":"Bob","latest_at":"2025-07-25"},
  {"id":3,"name":"Carol","latest_at":null}
]

Explanation: The subquery finds the maximum created_at for each user, and leftJoinSub attaches that single date to the user's record.

7 — crossJoin() (Product Variants)

A Cross Join generates a Cartesian product, creating a result set that includes every possible combination of rows from the joined tables.

Goal

Generate all size × color variants for a product.

Tables

Query

$variants = DB::table('sizes')
    ->crossJoin('colors')
    ->select('sizes.name as size', 'colors.name as color')
    ->get();

Expected Output

[
  {"size":"S","color":"Red"},
  {"size":"S","color":"Blue"},
  {"size":"M","color":"Red"},
  {"size":"M","color":"Blue"}
]

Warning: crossJoin() generates $N\times M$ rows (rows in first table $\times$ rows in second table) — use carefully on large tables.

8 — Many-to-Many Joins (posts, tags, pivot post_tag)

For many-to-many relationships, you need to join across the intermediate pivot table to link the two primary tables.

Tables

Query

$rows = DB::table('posts')
    ->join('post_tag', 'posts.id', '=', 'post_tag.post_id')
    ->join('tags', 'post_tag.tag_id', '=', 'tags.id')
    ->select('posts.id as post_id', 'posts.title', 'tags.name as tag')
    ->orderBy('posts.id')
    ->get();

Expected Output

[
  {"post_id":1,"title":"Laravel Tips","tag":"laravel"},
  {"post_id":1,"title":"Laravel Tips","tag":"tutorial"},
  {"post_id":2,"title":"PHP News","tag":"php"}
]

Explanation: The post_tag table allows one post to have multiple tags, resulting in multiple rows for the same post in the final output.

9 — Eloquent Relationships vs Query Builder Joins

Eloquent (with Eager Loading)

Eloquent uses with() for eager loading, which typically runs 2+ queries to build a nested PHP object structure.

// Models: User hasMany Order
$users = User::with('orders')->get();

Sample Output Shape (Nested Structure)

[
  {
    "id":1,
    "name":"Alice",
    "orders":[
      {"id":1,"user_id":1,"total":"50.00"},
      {"id":2,"user_id":1,"total":"25.00"}
    ]
  },
  {
    "id":3,
    "name":"Carol",
    "orders":[]
  }
]

Query Builder (join Method)

The Query Builder join() method runs a single SQL query and returns a flattened result set.

Equivalent Joined Output (Flattened)

[
  {"user_id":1,"name":"Alice","order_id":1,"total":"50.00"},
  {"user_id":1,"name":"Alice","order_id":2,"total":"25.00"},
  {"user_id":2,"name":"Bob","order_id":3,"total":"100.00"}
]

Best Practices

Optimize Performance

• Select Only Columns You Need: Avoid select('*'). Explicitly list columns, especially when joining:

  PHP

  ->select('users.id', 'users.name', 'orders.total') // Avoid *
  

• Add Indexes: Ensure all columns used in your on clauses (e.g., orders.user_id) are indexed to speed up join operations.

• Use Pagination/Chunking: For large result sets, avoid memory issues by using:

  PHP

  ->paginate(50) 
  // OR 
  DB::table(...)->chunk(1000, function($rows){ ... });
  

• Profile Queries: Use tools like Laravel Debugbar in development, or log queries to find bottlenecks:

  PHP

  DB::enableQueryLog(); 
  $results = DB::table('...')->get();
  dd(DB::getQueryLog());
  

Writing Joins

• Be Careful with groupBy: When using groupBy, you must include all non-aggregated selected columns in the groupBy clause to avoid ambiguous SQL errors in strict modes.

When to Use Eloquent vs. Query Builder

• Use Eloquent for: Readability, nested data (object graphs), and standard Create/Read/Update/Delete (CRUD) flows.

• Use Query Builder/Raw Joins for: Complex reports, analytics, performance-critical data dumps, and heavy aggregations/grouping.