Laravel 9, 8 Joins tutorial example. In this tutorial, we will show you what types of joins in laravel 9,8 and how to use theme.
If you are using laravel left and right join eloquent with a query builder, At that time, laravel left and right join for fetch data from the left and right table.
Laravel eloquent join 2, 3, or multiple tables example. Here, you will learn how to use laravel eloquent join 2 or multiple tables for fetching data from database tables.
And As well as how to use laravel eloquent join() with multiple where conditions.
If you want to join two or multiple tables in laravel then you can use laravel eloquent join(), left join(), right join(), cross join(). And another option to join two or multiple table, you can use laravel eloquent relationships instead of laravel join.
Types of Joins In Laravel 9, 8
- Laravel Inner Join
- Laravel Left Join
- Laravel Right Join
- Laravel Cross Join
- Laravel Advanced Join
- Laravel Sub-Query Joins
1 – 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.
2 – 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
3 – 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
4 – 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();
5 – 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();
6 – 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
Laravel 9, 8 joins tutorial, you have learned about laravel inner join, left join, right join, advanced join, crosss join and as well as how to them with multiple conditions in laravel 9, 8.
