What are SQL JOINS and how they are used in Yii2

Yii2 SQL JOINS
SQL JOINS

MySQL

What are SQL JOINS and how they are used in Yii2

Understand how SQL JOINS are working and how to use them in Yii2 Application.

Generically there are two types of Joins Inner and Outer Joins, Inner joins don’t have a direction (like left or right) because they are explicitly bidirectional – we require a match on both sides.

Outer joins, on the other hand, are for finding records that may not have a match in the other table. As such, we have to specify which side of the join is allowed to have a missing record.

Left Join and Right Join are shorthand for Left Outer Join and Right Outer Join; So in general there are three types of Joins, Left Join, Right Join & Inner Join.

Yii2 works in MVC (Model, View, Controller) pattern, where model means a database table.

Suppose we have the following tables:

Employees (Left Table)                   States (Right Table)

                     

Employee’s ID is added as foreign key in States table.

Left Join

Connect two tables and get all records from first one and matched from second one.

If we want to get all employees regardless of living in any state we will use LEFT JOIN like.

Query

Yii2

RESULT:

(We have all records from employees table and matched records from states table)

Right Join

Connect two tables and get all records from second one and matched from first one.

If we want to get all states regardless of employees we will use RIGHT JOIN like.

Query

Yii2

RESULT:

(We have all the records from states table and matched from employees table)

Inner Join

Connect two tables and get only matched records.

Now if we want to get all employees who are living in all saved states in states table we will use INNER JOIN like.

Query

Yii2

RESULT:

(Tim, Jim and Paul are NOT appear in the result because they are not a residential of any state)

I am a Sr. Android App & Web Developer having 5 plus years of extensive hands-on experience in Object Oriented Programming, Database Design and Implementation using Agile & UML. Pro in Yii2 PHP Framework, Android App Development, MYSQL & a Blockchain enthusiast.

Click to comment

Leave a Reply

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

More in MySQL

Recent Posts

Subscription

Categories

Tags

To Top