Laravel Joins Explained: Clear Guide with Practical Examples

• Mastering Joins in Laravel
• What Are Joins?
• 1. Inner Join
• 2. Left Join and Right Join
  • Left Join:
  • Right Join:
• 3. Cross Join
• 4. Advanced Join Clauses
  • Using orOn:
  • Using where and orWhere:
• 5. Subquery Joins
• 6. Lateral Joins
• 7. Unions
• Summary

Mastering Joins in Laravel

Joins are a powerful tool in Laravel's query builder that let you combine data from multiple database tables. Whether you're building a simple query or something more complex, Laravel makes it easy. This guide walks you through the basics of joins, explains when to use each type, and provides clear examples.

What Are Joins?

When working with databases, you often need to fetch data that is related but stored in different tables. For example, a users table might store user details, while a posts table stores their blog posts. To combine this data in a single query, you use a join.

Laravel provides several types of joins:

1. Inner Join
2. Left Join / Right Join
3. Cross Join
4. Advanced Joins
5. Subquery Joins
6. Lateral Joins
7. Unions

Let’s explore each of these with examples.

1. Inner Join

An inner join returns only the rows where there is a match in both tables. In Laravel, you can use the join method:

use Illuminate\Support\Facades\DB;
 
$users = DB::table('users')
    ->join('contacts', 'users.id', '=', 'contacts.user_id')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.*', 'contacts.phone', 'orders.price')
    ->get();

This query joins three tables: users, contacts, and orders. It retrieves user details, phone numbers, and order prices where the data matches across the tables.

2. Left Join and Right Join

A left join returns all rows from the left table and the matching rows from the right table. A right join does the opposite. Here's how you do it in Laravel:

Left Join:

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

Right Join:

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

Use these when you want all rows from one table, even if there's no match in the other.

3. Cross Join

A cross join creates a cartesian product, combining every row of the first table with every row of the second table. For example:

$sizes = DB::table('sizes')
    ->crossJoin('colors')
    ->get();

This query generates every possible combination of sizes and colors.

4. Advanced Join Clauses

Sometimes, you need more complex conditions in your joins. Use a closure to define custom constraints:

Using orOn:

DB::table('users')
    ->join('contacts', function ($join) {
        $join->on('users.id', '=', 'contacts.user_id')
             ->orOn('users.email', '=', 'contacts.email');
    })
    ->get();

Using where and orWhere:

DB::table('users')
    ->join('contacts', function ($join) {
        $join->on('users.id', '=', 'contacts.user_id')
             ->where('contacts.user_id', '>', 5)
             ->orWhere('contacts.is_active', true);
    })
    ->get();

These methods allow you to create detailed conditions, ensuring you fetch exactly the data you need.

5. Subquery Joins

You can even join a table with a subquery using the joinSub method. For example:

$latestPosts = DB::table('posts')
    ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
    ->where('is_published', true)
    ->groupBy('user_id');
 
$users = DB::table('users')
    ->joinSub($latestPosts, 'latest_posts', function ($join) {
        $join->on('users.id', '=', 'latest_posts.user_id');
    })
    ->get();

This retrieves users along with the timestamp of their latest published post.

6. Lateral Joins

Lateral joins evaluate a subquery for each row in the main query. They are supported by PostgreSQL, MySQL >= 8.0.14, and SQL Server.

For example:

$latestPosts = DB::table('posts')
    ->select('id as post_id', 'title as post_title', 'created_at as post_created_at')
    ->whereColumn('user_id', 'users.id')
    ->orderBy('created_at', 'desc')
    ->limit(3);
 
$users = DB::table('users')
    ->joinLateral($latestPosts, 'latest_posts')
    ->get();

This query fetches each user along with their three most recent posts. Each row in the main query can produce multiple results in the joined query.

7. Unions

Sometimes, you may need to combine the results of two queries. Use the union method for this:

use Illuminate\Support\Facades\DB;
 
$first = DB::table('users')
    ->whereNull('first_name');
 
$users = DB::table('users')
    ->whereNull('last_name')
    ->union($first)
    ->get();

If you don't want to remove duplicates, use unionAll instead:

$users = DB::table('users')
    ->whereNull('last_name')
    ->unionAll($first)
    ->get();

Summary

Joins are essential for fetching related data efficiently. Laravel provides a variety of methods to handle different scenarios:

• Inner Join: Matches rows in both tables.
• Left Join / Right Join: Includes unmatched rows from one table.
• Cross Join: Generates all combinations of rows from two tables.
• Advanced Joins: Allows custom conditions.
• Subquery Joins: Joins with complex subqueries.
• Lateral Joins: Subqueries that depend on each row of the main query.
• Unions: Combines results from multiple queries.

By mastering these, you can handle even the most intricate data relationships in your Laravel projects. Happy coding!