Laravel 8 Tutorial - Join Clauses

In this tutorial, we will learn about join clauses and how to improve the performance of your Laravel application.

Why Use Join Clauses in Laravel?

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

There are several types of join clauses used in Laravel, including:

• Inner Join Clause
• Left Join Clause
• Right Join Clause
• Cross Join Clause
• Advanced Join Clause
• Sub Query Join Clause

In this tutorial, we will discuss inner, left, and right join clauses.

Inner Join Clause

Before starting with the join clause, let's create a table in our database.

Go to phpMyAdmin and click on the laravel8prodb database.

Inside this database, let's create two tables: users and posts.

Run the following SQL query in phpMyAdmin:

CREATE TABLE `users` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `phone` varchar(15) NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



CREATE TABLE `posts` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `body` text NOT NULL,
  `user_id` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Now, insert some users into the users table and some posts into the posts table.

Let's create an inner join. Laravel's Inner Join clause selects records if the given column values match in both tables.

We have two tables: users and posts. Both have id columns, and there is a user_id in the posts table, which is a foreign key for the users table.

Alright, so for creating an inner join, let's create a function:

public innerJoinCluase() 
{ 
    $result = DB::table('users') 
            ->join('posts', 'users.id', '=', 'posts.user_id') 
            ->select('users.name', 'posts.title', 'posts.body') 
            ->get(); 
    return $result; 
}

Now, create a route for this function. Go to the web.php file and type:

Route::get('/inner-join',[PostController::class , 'innerJoinCluase');

Save the file and let's check. Switch to the browser and go to the URL /inner-join.

You can see here the user name, post title, and post body. These records are coming from both tables.

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.

Let's create a function for the left join inside the PostController:

public function leftJoinClause() 
{ 
    $results = DB::table('users') 
                ->leftJoin('posts', 'users.id', '=', 'posts.user_id') 
                ->get(); 
    return $results; 
}

And now, create a route:

Route::get('/left-join','PostController@leftJoinClause');

Now, let's check it. Go to the URL /left-join.

You can see the result here.

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.

Now, create a function for the right join. Go to the PostController and type:

public function rightJoinClause() 
{ 
    $resutls = DB::table('users') 
                ->rightJoin('posts', 'users.id', '=', 'posts.user_id') 
                ->get(); 
    return $resluts; 
}

Now, create a route. Go to the web.php file and create a route:

Route::get('/right-join','PostController@rightJoinClause'); 

Now, let's check the right join. Go to the URL /right-join.

You can see the result here. All records are coming from the right table, which is the posts table.

So, in this way, you can use join clauses in Laravel 8.