SQL Joins: How to Create Joins in Laravel Tables

There’s no denying SQL joins are powerful in the database sector. They allow developers to merge data from multiple tables to enable more sophisticated queries and more visualization of their data. Their main purpose is to combine two tables with a common field in a database.

Besides SQL databases, joins can be used in Laravel to extract data from the tables in the database models, serving another advantage of using the framework. This article provides a clear and practical approach for using SQL joins within the framework for a beginner or an experienced Laravel developer.

What is an SQL join?

An SQL Join is a statement or clause used to combine two or more tables with a common field and value. These statements make normalization possible by allowing specific queries to return results or values from multiple tables.

Consider two tables, A and B, representing the student databases.

Fig 1.0: Table A with the student’s ID, name, and course details

Fig 1.1: Table B with the student’s ID, registration number, and year of enrollment details

The SQL Join statement can be used to extract and update data at once using certain conditions to produce the following result:

Fig 1.2: A join table with the student’s ID, name, and course details

As per the above table, the SQL clause has been used to extract values from the two tables, A and B, using a common field, the ID field. In most cases, the primary key (e.g., the ID) forms a join relationship between tables, and the developer can manage them if the relationship is preserved.

What are the types of SQL joins?

SQL joins refer to two tables, the right and the left table, which are named according to the side of the join table where they appear. Therefore, the join statement 'SELECT * FROM tableA JOIN tableB' means tableA is the right table, and tableB is the left table.

As such, there are five types of SQL join clauses, namely:

• Inner join: It returns all fields and values in both tables.

• Left outer join: It is similar to the inner join. The only difference is that it includes all fields and data from the left table and specified ones from the right table.

• Right outer join: It is the opposite of the left outer join. It includes all values from the right table

• Full join: It returns all values from both tables if there is a relationship between them.

• Cross join: It combines the rows on the left table with the rows on the right table.

The where() condition can also be added to the join clauses to specify the values that should be displayed. There are other types of joins, such as self-join and natural joins, which are customized by the developer and use the UNION statement.

How to set up Laravel tables for database connection using join

To set up Laravel tables for database connection using join, the right table must be related to the left table. Here are the steps to follow when setting up eloquent models or Laravel tables for database connection using Join:

i. Create database tables

The first step to using join in Laravel is creating database tables that will be connected using the clause. You can do so by creating a migration using the following command in the terminal:

php artisan make:migration table_name

The table_name represents the name of the table that you want to create. Create at least two tables to establish a database relationship.

php artisan make:migration users

php artisan make:migration car_details

ii. Define the schema

A Laravel schema class enables a developer to manipulate tables in a database. When using the join clause, define each table’s schema by adding fields and the primary key that will be used later.

public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->string('address”');
        $table->string('email')->unique();
        $table->timestamps();
    });
}

Fig 3.1: users table schema

public function up()
{
    Schema::create('car_details', function (Blueprint $table) {
        $table->id();
        $table->string("userID");
        $table->string('name');
        $table->string('car_reg_plate”');
        $table->string('carModel”);
        $table->timestamps();
    });
}

Fig 3.2: Car details table schema

Run the migration command on the terminal to push the tables to the database:

php artisan migrate

iii. Create the models

In Laravel, each table has a corresponding model, which is used to interact with it. An Eloquent model enables the user to manipulate the table by deleting, updating, adding, inserting, and retrieving records from the table. The command is run on the terminal to create a model in Laravel:

php artisan make:model User

php artisan make:model Post

Once you have created the models, move to the next step, establishing the relationship between the two tables.

iv. Create a controller

Next, create a controller for both tables using the php command below:

php artisan make:controller UsersController

php artisan make:controller CarDetailsController

v. Define the relationship between the two tables using the join statement

The relationship between two Laravel tables is defined in the class of the controller using the join statement as demonstrated below:

public function innerJoin() {
$usersDetails = DB::table('users');
->join ('car_details', 'users.id', "=", 'car_details.userID');
//->select('users.*', 'posts.title as post_title');
->get();
}

Fig 3.3: A full join of the users and posts tables

When the code is run on the local server, the results will be a joined table with values from both tables, depending on the join used which is further explained below.

How to implement Joins in Laravel

i. Left outer join

The left outer join displays all the fields on the left table and partial fields (including the primary key) from the right table.

public function leftJoin (){
$usersDetails = DB::('car_details'); 
->join('users', 'car_details.userID', '=', 'users.id');
->select('car_details.*');
->get();
}

ii. Right outer join

The right outer join displays all the fields on the right table and partial fields (including the primary key) from the left table.

public function rightJoin(){
$usersDetails = DB::('users'); 
->join('car_details', 'users.id','=','car_details.userID');
->select('users.*');
->get();
}

iii. Inner join

The inner join displays all fields that are present in the left and the right table.

public function innerJoin(){
$usersDetails = DB::('users'); 
->join('car_details', 'users.id','=','car_details.userID');
->get();
}

iv. Full outer join

The full outer join displays all the values and fields from both the left and the right tables.

public function fullJoin() {
$usersDetails = DB::table('users');
->join ('car_details', 'users.id', "=", 'car_details.userID');
->select('users.*', 'car_details.*');
->get();
}

Or you can use the alternative UNION statement to combine the left and right join as illustrated below:

public function fullJoin() {
$leftJoin = User::leftJoin('posts', 'users.id', '=', 'posts.user_id')
->select('users.*', 'posts.title as post_title');
$rightJoin = User::rightJoin('posts', 'users.id', '=', 'posts.user_id')
->select('users.*', 'posts.title as post_title');
$usersWithPosts = $leftJoin->union($rightJoin)->get();
}

v. Cross join

Also known as the Cartesian join, the cross join’s result table contains a paired combination of each row of the right table with each row of the left.

public function crossJoin() {
$crossJoinResult = DB::table('users')
->join ('car_details', 'users.id', "=", 'car_details.userID');
->select('users.id', 'car_details.carRegNo')
->get();
}

How to join with the condition where()

The where() clause or method compares the column against a value in a Laravel table. An example of where() being used to derive results based on certain criteria includes the following:

public function list(){
DB::table('users')
        ->join('car_details', function($join)

        {
            $join->on('users.id', '=', 'car_details.userID')
                 ->where('car_details.user_id', '>', 100);
        })

        ->get();
}

Conclusion

SQL join’s main purpose is to connect multiple tables in a database with a common column. Mastering the technique of joins in a Laravel framework is beneficial in the retrieval and manipulation of data from relational tables. By using the different type of join such as left and right outer, inner, full and cross join, you gain the knowledge to craft complex and efficient queries that meet your application’s specific needs.

Resource Links

• Database: Query Builder

• SQL | Join (Inner, Left, Right and Full Joins)

• SQL - Joins

• Types of SQL JOINS Explained with Examples

• Controllers

• Generating Model Classes