Laravel 6 Joins & Sub Query Joins Example Tutorial

Laravel 6 Joins & Sub Query Joins Example Tutorial

In this Laravel 6 Tutorial, we would love to share with you, how many types of joins in laravel and how to use this joins with queries.

When we develop any application in laravel, we want to fetch records from the databse.
Sometimes, we need to fetch records more than two or n-tables at that time, we will use joins clauses in laravel.

In this article, you will learn laravel joins clauses and how to improve the performance of your laravel application.

Table of Contents

  • Inner Join Clause
  • Left Join / Right Join Clause
  • Cross Join Clause
  • Advanced Join Clauses
  • Sub-Query Joins

Inner Join Clause

Laravel Inner Join clause selects records if the given column values matching in both tables.

When joining two tables in Laravel, you should be specific about what you are choosing.

Let’s say you have two tables, users and posts. Both have ID columns, and there is a user_id in the post, which is a foreign key for the users’ table.

$user = User::join('posts', 'posts.user_id', '=', 'users.id')
       ->select('users.*')
       ->get();

Left Join / Right Join Clause

Left Join Clause

The Laravel LEFT JOIN clause 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.

 User::leftJoin('posts', 'posts.user_id', '=', 'users.id')
       ->select('users.*')
       ->get();

Right Join Clause

The Laravel Right JOIN clause 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.

 User::rightJoin('posts', 'posts.user_id', '=', 'users.id')
       ->select('users.*')
       ->get();

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();

Advanced Join Clauses

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();

Sub-Query Joins

DB::table('posts')
->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
->where('is_published', true)->groupBy('user_id');

Conclusion

In this tutorial, you have learned laravel joins like inner join, left join, right join, advanced join, cross join and sub-query join.

Leave a Reply

Your email address will not be published. Required fields are marked *