CodeIgniter Join Clause is used to combine rows with two or more tables, depending on the respective columns between them.
If you want to get or fetch records from different tables, which are respectively related each other.
CodeIgniter Join Query
You want to use multiple joins with table in CodeIgniter, you can repeat “$this->db->join( )” with tables.
Codeigniter Types of Joins
- Join
- Inner Join
- Left Join
- Right Join
- Conditional Join
- Full Outer Join
- Left Outer Join
- Right Outer Join
Join()
CodeIgniter Join() query function is used to fetch the result from one or more tables.
$this->db->select('firstTable.*, secondTable.*'); $this->db->from('firstTable'); $this->db->join('secondTable', 'secondTable.id = firstTable.id'); $query = $this->db->get(); //Produces: SELECT `firstTable`.*, `secondTable`.* FROM `firstTable` JOIN `secondTable` ON `secondTable`.`id` = `firstTable`.`id`
Inner Join
CodeIgniter Inner Join clause selects records if the given column values matching in both tables.
$this->db->select('firstTable.*, secondTable.*'); $this->db->from('firstTable'); $this->db->join('secondTable', 'secondTable.id = firstTable.id', 'inner'); $query = $this->db->get(); // Produces: SELECT `firstTable`.*, `secondTable`.* FROM `firstTable` INNER JOIN `secondTable` ON `secondTable`.`id` = `firstTable`.`id`
Left Join
The Codeigniter 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.
$this->db->select('firstTable.*, secondTable.*'); $this->db->from('firstTable'); $this->db->join('secondTable', 'secondTable.id = firstTable.id', 'left'); $query = $this->db->get(); Produces: SELECT `firstTable`.*, `secondTable`.* FROM `firstTable` LEFT JOIN `secondTable` ON `secondTable`.`id` = `firstTable`.`id`
Right Join
The Codeigniter 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.
$this->db->select('firstTable.*, secondTable.*'); $this->db->from('firstTable'); $this->db->join('secondTable', 'secondTable.id = firstTable.id', 'right'); $query = $this->db->get(); Produces: SELECT `firstTable`.*, `secondTable`.* FROM `firstTable` RIGHT JOIN `secondTable` ON `secondTable`.`id` = `firstTable`.`id`
Conditional Join
CodeIgniter Join with where condition with used table fields.
$this->db->select('firstTable.*, secondTable.*'); $this->db->from('firstTable'); $this->db->join('secondTable', 'secondTable.id = firstTable.id'); $this->db->where('firstTable.field_name','field_value'); $query = $this->db->get(); Produces: SELECT `firstTable`.*, `secondTable`.* FROM `firstTable` JOIN `secondTable` ON `secondTable`.`id` = `firstTable`.`id` WHERE `firstTable`.`field_name` = ‘field_value’
Full Outer Join
In Codeigniter the FULL OUTER JOIN return all results of both left and right tables.
$this->db->join('secondTable', 'secondTable.id = firstTable.id', 'outer');
Left Outer Join
The Codeigniter LEFT JOIN OUTER clause returns all rows from the left table, even if there are no matches in the right table, The result will be NULL from the right side.
$this->db->join('secondTable', 'secondTable.id = firstTable.id', 'left outer');
Right Outer Join
The Codeigniter Right JOIN OUTER clause returns all rows from the right table, even if there are no matches in the left table, The result will be NULL from the left side.
$this->db->join('secondTable', 'secondTable.id = firstTable.id', 'right outer');
Thanks this really helped me