Eloquent join queries in Laravel

Eloquent join queries in Laravel 5.8,Eloquent join queries in Laravel 5.7,join queries in Laravel 5.6,join queries in Laravel 5.5,join queries in Laravel 5.4,Laravel 5.3,Laravel 5.2 and Laravel 5.1

With the help of joins keyword, you are used to add two or more tables through a single query. If this work is done without joins you will have to write more than one query.
Laravel provides you 3 types of joins.
1.Inner Join
2.Left Join
3.Right Join
All these joins have been given in detail. Let’s try to know about them.

 

Also Read

Laravel 5.8 CRUD (Create Read Update Delete) Operation For Beginners With Example

 

Inner Join: – The most commonly used join is INNER JOIN. When you use this join then the results are show in the rows which match in both tables.

$users = DB::table('users')
->join('profile', 'users.id', '=', 'profile.user_id')
->select('users.fname', 'profile.gender', 'profile.status', 'forum_question.title', 'profile.lname', 'profile.address', 'profile.number')
->get();

Join query use with WHERE Condition

$users = DB::table('users')
->join('profile', 'users.id', '=', 'profile.user_id')
->select('users.fname', 'profile.gender', 'profile.status', 'forum_question.title', 'profile.lname', 'profile.address', 'profile.number')
->where('profile.status', '=', 'active')
->get();

Join query use with Limit

$users = DB::table('users')
->join('profile', 'users.id', '=', 'profile.user_id')
->select('users.fname', 'profile.gender', 'profile.status', 'forum_question.title', 'profile.lname', 'profile.address', 'profile.number')
->where('profile.status', '=', 'active')
->limit(5)
->get();

Join query use with ASC Orderby Show

$users = DB::table('users')
->join('profile', 'users.id', '=', 'profile.user_id')
->select('users.id', 'users.fname', 'profile.gender', 'profile.status', 'forum_question.title', 'profile.lname', 'profile.address', 'profile.number')
->orderby('user.id', '=', 'asc')
->get();

Join query use with DESC Orderby Show

$users = DB::table('users')
->join('profile', 'users.id', '=', 'profile.user_id')
->select('users.id', 'users.fname', 'profile.gender', 'profile.status', 'forum_question.title', 'profile.lname', 'profile.address', 'profile.number')
->orderby('user.id', '=', 'desc')
->get();

Join query use with paginate Query

$users = DB::table('users')
->join('profile', 'users.id', '=', 'profile.user_id')
->select('users.id', 'users.fname', 'profile.gender', 'profile.status', 'forum_question.title', 'profile.lname', 'profile.address', 'profile.number')
->orderby('user.id', '=', 'desc')
->paginate(10);

Left Join: – Left join in Laravel, if all the rows are show, then you use LEFT JOIN for it. By LEFT JOIN, you show that you should show all the rows of the left table.

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

Right Join: – If you want to have all the rows show in the right table in Laravel or if there is no row in the left table, you can use RIGHT JOIN for it.

DB::table('users')
->rightJoin('profile','profile.user_id','=','users.id')
->select('users.name','profile.last_name')
->get();