In Laravel, there are several types of joins available for querying data from multiple tables. These include join(), leftJoin(), rightJoin(), crossJoin(), subqueryJoin(), and advancedJoin(). Each join type serves a specific purpose in retrieving data based on the relationships between tables.
In this tutorial, you will learn about these different join types in Laravel and how to effectively fetch data by joining 2, 3, or more tables. The tutorial will cover the following topics:
- join(): This method allows you to perform an inner join between two or more tables. You will learn how to use join() to combine tables based on common column values.
- leftJoin(): With leftJoin(), you can perform a left outer join. This tutorial will demonstrate how to use leftJoin() to retrieve all records from the left table and matching records from the right table.
- rightJoin(): rightJoin() enables you to perform a right outer join. The tutorial will show you how to use rightJoin() to fetch all records from the right table and matching records from the left table.
- crossJoin(): The crossJoin() method performs a cross join, which combines every record from the first table with every record from the second table. You will learn how to use crossJoin() to generate Cartesian product results.
- subqueryJoin(): This join type allows you to use subqueries as a join condition. The tutorial will demonstrate how to leverage subqueryJoin() to perform complex joins using subqueries.
- advancedJoin(): The advancedJoin() method provides more advanced join capabilities, such as multiple join conditions and custom aliases. You will explore how to utilize advancedJoin() for more complex join scenarios.
In this tutorial, you will learn about all the joins ( join(), left join(), right join(), cross join(), sub-query join() and Advanced Join()) of Laravel and also learn how to fetch data from the database by joining 2, 3, and more tables using join(), left join(), right join(), cross join(), sub-query join() and Advanced Join().
In addition to the basic join methods in Laravel, you can leverage the power of Laravel Eloquent relationships to perform joins more conveniently and effectively. This allows you to use eloquent join(), leftJoin(), rightJoin(), and crossJoin() methods with Laravel Eloquent relationship.
Laravel 10 Joins Tutorial with Examples
In Laravel 10, you can utilize various types of joins to query data from multiple tables. The following are the available join types
- Laravel Inner Join
- Example 1: Laravel Eloquent Join() with 2 Tables
- Example 2: Laravel Eloquent Join() with 3 Tables
- Example 3: Laravel Eloquent Join() with Multiple Conditions
- Laravel Left Join
- Example 1: Laravel Left Join Query
- Example 2: Laravel Left Join with Multiple Condition
- Laravel Right Join
- Example 1: Laravel Right Join Query
- Example 2: Laravel Right Join with Multiple Condition
- Laravel Cross Join
- Laravel Advanced Join
- Laravel Sub-Query Joins
Laravel Inner Join
Laravel JOIN eloquent returns all rows from the both table, if there are matches in the both table. Otherwise, the result is NULL.
Now, demonstrates laravel eloquent join with the following examples. You can see the following example of laravel eloquent join() method:
Example 1: Laravel Eloquent Join() with 2 Tables
Here, fetch data using laravel eloquent join(), you can see the following example:
$users = User::join('posts', 'users.id', '=', 'posts.user_id')
->get(['users.*', 'posts.descrption']);
When you dump the above-given laravel eloquent join query, you will get the following SQL query:
select `users`.*, `posts`.`descrption` from `users` inner join `posts` on `users`.`id` = `posts`.`user_id`
Example 2: Laravel Eloquent Join() with 3 Tables
In this example, get data using laravel eloquent join 3 table, you can see the following example:
$users = User::join('posts', 'posts.user_id', '=', 'users.id')
->join('comments', 'comments.post_id', '=', 'posts.id')
->get(['users.*', 'posts.descrption']);
When you dump the above-given laravel eloquent join 3 table query, you will get the following SQL query:
select `users`.*, `posts`.`descrption` from `users` inner join `posts` on `posts`.`user_id` = `users`.`id` inner join `comments` on `comments`.`post_id` = `posts`.`id`
Example 3: Laravel Eloquent Join() with Multiple Conditions
In this example, get data using laravel eloquent join with multiple where conditions, you can see the following example:
$users = User::join('posts', 'posts.user_id', '=', 'users.id')
->where('users.status', 'active')
->where('posts.status','active')
->get(['users.*', 'posts.descrption']);
When you dump the above-given laravel eloquent join with multiple conditions query, you will get the following SQL query:
select `users`.*, `posts`.`descrption` from `users` inner join `posts` on `posts`.`user_id` = `users`.`id` where `users`.`status` = active and `posts`.`status` = active
In another option instead of join laravel, you can use laravel relationship, once you created laravel relationship it will work like join.
Laravel Left Join
Laravel LEFT JOIN eloquent returns all rows from the left table, even if there are no matches in the right table, The result is NULL from the right side.
Let’s understand of laravel left join, suppose you have two 2 tables in the database. One is a posts table and the other is an authors table.
And when you get the data from the authors table. And left join posts with authors table. At this time, the details of the author will be get with posts collection. And if the no posts of authors in posts table. So the collection of posts will get null with all the details of the author.
And if there are author posts in the posts table, then data will get from both tables.
Now learn laravel left join query in laravel, see the following examples one by one:
Example 1: Laravel Left Join Query
Here, fetch data using laravel left join with authors and posts table, you can see the following example:
Author::leftJoin('posts', 'posts.author_id', '=', 'authors.id')
->select('authors.*')
->get();
When you dump the above-given laravel left join query, you will get the following SQL query:
select `authors`.* from `authors` left join `posts` on `posts`.`author_id` = `authors`.`id`
Example 2: Laravel Left Join with Multiple Condition
In this example, get data using laravel left join with multiple where conditions on authors and posts table, you can see the following example:
Author::leftJoin('posts', 'posts.author_id', '=', 'authors.id')
->select('authors.*')
->where('authors.status', 'active')
->where('authors.subscription', 'active')
->get();
When you dump the above-given laravel left join with multiple conditions query, you will get the following SQL query:
select `authors`.* from `authors` left join `posts` on `posts`.`author_id` = `authors`.`id` where `authors`.`status` = active and `authors`.`subscription` = active
Laravel Right Join
The Laravel Right JOIN eloquent returns all rows from the right table, even if there are no matches in the left table, The result is NULL from the left side.
Let’s understand of laravel right join, suppose you have two 2 tables in the database. One is a users table and the other is an city table.
And when you get the data from the users table. And right join with city table. At this time, The query returns all rows in the city table and all matching rows found in the users table.
Here, take a look at examples of laravel right join query, see the following examples one by one:
Example 1: Laravel Right Join Query
Here, this example shows how to fetch data using laravel right join, you can see the following example:
User::rightJoin('city','city.user_id','=','users.id')
->select('users.name','city.city_name')
->get();
When you dump the above-given laravel right join query, you will get the following SQL query:
select `users`.`name`, `city`.`city_name` from `users` right join `city` on `city`.`user_id` = `users`.`id`
Example 2: Laravel Right Join with Multiple Condition
In this example shows how to fetch data using laravel right join with multiple where clause conditions, you can see the following example:
User::rightJoin('city','city.user_id','=','users.id')
->select('users.name','city.city_name')
->where('users.status', 'active')
->where('city.status', 'active')
->get();
When you dump the above-given laravel right join with multiple conditions query, you will get the following SQL query:
select `users`.`name`, `city`.`city_name` from `users` right join `city` on `city`.`user_id` = `users`.`id` where `users`.`status` = active and `city`.`status` = active
Cross Join Clause
The CROSS JOIN joined every row from the first table (T1) with every row from the second table (T2).
Size::crossJoin('colours')
->get();
Laravel Advanced Join
If you would like to use a “where” style clause on your joins, you may use the where and orWhere methods on a join. Instead of comparing two columns, these methods will compare the column against a value:
DB::table('users')
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')
->where('contacts.user_id', '>', 5);
})
->get();
Laravel Sub-Query Joins
The following join is sub query joins in laravel:
DB::table('posts')
->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
->where('is_published', true)->groupBy('user_id');
Conclusion
Additionally, the tutorial will cover how to use Laravel’s Eloquent relationships in conjunction with join(), leftJoin(), rightJoin(), and crossJoin(). You will discover how to leverage the power of Eloquent relationships to perform joins more efficiently and conveniently.
