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
|
1 |
SELECT * FROM `employees` LEFT JOIN states ON employees.id=states.fk_employee_id WHERE employees.status='active' |
Yii2
|
1 2 3 4 |
Employees::find() ->leftJoin('states','employees.id=states.fk_employee_id') ->where(['employees.status'=>'active']) ->all(); |
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
|
1 |
SELECT * FROM `employees` RIGHT JOIN states ON employees.id=states.fk_employee_id WHERE employees.status='active' |
Yii2
|
1 2 3 4 |
Employees::find() ->rightJoin('states','employees.id=states.fk_employee_id') ->where(['employees.status'=>'active']) ->all(); |
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
|
1 |
SELECT * FROM `employees` INNER JOIN states ON employees.id=states.fk_employee_id WHERE employees.status='active' |
Yii2
|
1 2 3 4 |
Employees::find() ->innerJoin('states','employees.id=states.fk_employee_id') ->where(['employees.status'=>'active']) ->all(); |
RESULT:
(Tim, Jim and Paul are NOT appear in the result because they are not a residential of any state)
