CodeIgniter join query: Inner – Left – Right – Outer – Left Outer – Right Outer Join with example

CodeIgniter join query using query builder class in active record pattern. article includes CodeIgniter table join example for Inner Join, Left Join, Right Join, Normal Join, Conditional Join, Outer Join, Left Outer Join and Right Outer Join.

CodeIgniter query builder class join query function is used to fetch the data result from one or more tables.

Article includes: CodeIgniter $this->db->join() use with example in active record pattern | Query Builder Class use for different type of select join queries | CodeIgniter join query demo and tutorial.

If you are beginner for CodeIgniter and SQL queries, checkout below link for useful tutorial for it:

$this->db->join():
Permits you to write the JOIN portion of your query.

For multiple table join in CodeIgniter, you can repeat $this->db->join( ) in syntax, just change table name and condition based on requirement.

Multiple function calls can be made if you need several joins in one query.

If you need a specific type of JOIN you can specify it via the third parameter of the function. Options are: left, right, outer, inner, left outer, and right outer.

CodeIgniter join query
CodeIgniter join query

CodeIgniter Join

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

For multiple table join in CodeIgniter, you can repeat $this->db->join( ) in syntax


$this->db->select('table1.*, table2.*');
$this->db->from('table1');
$this->db->join('table2', 'table2.id = table1.id');
$query = $this->db->get();

Produces:
SELECT `table1`.*, `table2`.* FROM `table1`
JOIN `table2` ON `table2`.`id` = `table1`.`id`

CodeIgniter Inner Join

CodeIgniter Inner Join selects records that have matching values in both tables.


$this->db->select('table1.*, table2.*');
$this->db->from('table1');
$this->db->join('table2', 'table2.id = table1.id', 'inner');
$query = $this->db->get();

Produces:
SELECT `table1`.*, `table2`.* FROM `table1`
INNER JOIN `table2` ON `table2`.`id` = `table1`.`id`

CodeIgniter Left Join

CodeIgniter Left Join returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.


$this->db->select('table1.*, table2.*');
$this->db->from('table1');
$this->db->join('table2', 'table2.id = table1.id', 'left');
$query = $this->db->get();

Produces:
SELECT `table1`.*, `table2`.* FROM `table1`
LEFT JOIN `table2` ON `table2`.`id` = `table1`.`id`

CodeIgniter Right Join

CodeIgniter Right Join returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.


$this->db->select('table1.*, table2.*');
$this->db->from('table1');
$this->db->join('table2', 'table2.id = table1.id', 'right');
$query = $this->db->get();

Produces:
SELECT `table1`.*, `table2`.* FROM `table1`
RIGHT JOIN `table2` ON `table2`.`id` = `table1`.`id`

CodeIgniter Outer Join

CodeIgniter Full Outer Join return all records when there is a match in either left (table1) or right (table2) table records.

A Full Outer Join is a union of the Left Outer Join and Right Outer Join.

Same output can be possible using Left Join and Right Join.

Note: In my testing below syntax gives error, seems MySQL not support Full Outer Join / Outer Join.

$this->db->join('table2', 'table2.id = table1.id', 'outer');


CodeIgniter Left Outer Join

Left Join and Left Outer Join are the same.

For use Left Outer Join, you have to pass left outer in join function third parameter parameter.


$this->db->join('table2', 'table2.id = table1.id', 'left outer');


CodeIgniter Right Outer Join

Right Join and Right Outer Join are the same.

For use Left Outer Join, you have to pass right outer in join function third parameter parameter.


$this->db->join('table2', 'table2.id = table1.id', 'right outer');


CodeIgniter Conditional Join

CodeIgniter Join with where condition with used table fields.


$this->db->select('table1.*, table2.*');
$this->db->from('table1');
$this->db->join('table2', 'table2.id = table1.id');
$this->db->where('table1.field_name','field_value');
$query = $this->db->get();

Produces:
SELECT `table1`.*, `table2`.* FROM `table1`
JOIN `table2` ON `table2`.`id` = `table1`.`id`
WHERE `table1`.`field_name` = ‘field_value’

Read more for CodeIgniter: Article for CodeIgniter