Part 17 laravel query builder all join methods like Inner, left, right, cross and advanced join

In this tutorial, we will discuss all type of laravel query builder join methods like (Inner Join, Left Join, Right Join, Cross Join and Advanced Join) in laravel 6, laravel 7 and laravel 8. Join mehtods use for join more than two tables and get data. also discuss difference between all laravel joins.

Laravel query builder joins

1. Inner Join
2. Left Join
3. Right Join
4. Cross Join
5. Advanced Join

Inner Join

Laravel provides many join methods to connect tables. first is inner join method we simply use the join method on a query builder instance. The first argument pass in join method is table name which is joined by the main table and the next arguments specify primary and foreign keys which are columns of both tables and their values become the same. You join multiple tables in a single query. Let an example of inner join

<?php

namespace App\Http\Controllers;
use Illuminate\Http\Request;

class StudentController extends Controller{
 public function index(){
    $students = DB::table('students')
			->join('profiles', 'students.id', '=', 'profiles.student_id')
			->join('orders', 'students.id', '=', 'orders.student_id')
			->get();

    dd($students);
  }
}

Left Join

Let we explain laravel Left join. Laravel Left join given all data from the Left side table and only matched data from right side table. Example of laravel Left join. Here students table all data print and results table only print rows which are make relation to students.

<?php

namespace App\Http\Controllers;
use Illuminate\Http\Request;

class StudentController extends Controller{
 public function index(){
    $students = DB::table('students')
			->leftJoin('results', 'students.id', '=', 'results.student_id')
			->get();

    dd($students);
  }
}

Right Join

Let we explain laravel Right join this is just opposite of left join. Laravel right join given all data from the right side table and only matched data from left side table. Example of laravel right join. here results table all data print and students table only rows print which are related to students table.

<?php

namespace App\Http\Controllers;
use Illuminate\Http\Request;

class StudentController extends Controller{
 public function index(){
    $students = DB::table('students')
			->rightJoin('results', 'students.id', '=', 'results.student_id')
			->get();

    dd($students);
  }
}

Cross Join

Laravel Cross join produce cartesian product data of first and joined table. In cross join we not check any relation between tables it print all rows with each other rows of both table. Let explain by example if we have a students table with 9 records and other joined table results have 5 records than cross join give output of 9*5 = 45 rows.

<?php

namespace App\Http\Controllers;
use Illuminate\Http\Request;

class StudentController extends Controller{
 public function index(){
    $students = DB::table('students')
			->crossJoin('results')
			->get();

    dd($students);
  }
}

Advanced Join

Laravel advanced join use a function or closure as second argument of join method. We also pass where conditoin in advanced join method. Let an example of laravel advanced join

<?php

namespace App\Http\Controllers;
use Illuminate\Http\Request;

class StudentController extends Controller{
 public function index(){
    $students = DB::table('students')
			->join('results', function($join){
				$join->on('students.id', '=', 'results.student_id')
				->where('status', '1');
			})
			->get();

    dd($students);
  }
}

You read this tutorial on advanced web tutorial. here we provide beginners to advanced tutorial.

Related tutorial links

• laravel query builder introduction tutorial
• part 1 laravel query insert data
• part 2 laravel all type of where conditions in query builder
• part 3 laravel whereDate, whereYear and whereTime qyery method
• part 4 compare two db columns in laravel query builder
• part 6 laravel pluck method get single column value
• part 7 laravel select and addselect query in query builder
• part 5 laravel first and find queries
• part 8 laravel edit and update method query explaination
• Part 9 laravel query for update or insert data by one method
• part 10 laravel delete vs truncate vs drop method
• part 11 gropupby and orderby query tutorial
• Part 12 laravel union of two queries
• part 13 laravel all aggregate functions with example
• part 14 laravel query builder chunk method
• part 15 laravel exists() and doesntExist() method
• part 16 laravel paginate vs simplePaginate method
• part 17 laravel all join methods