Joins in CodeIgniter | left join, right, inner, Outer, Cross, full join

JOIN allows users to bring data from two or more database tables. In this tutorial guide, we will show you the types of joins and how to use them to get data from tables in CodeIgniter.

How to use joins in CodeIgniter | left join, right,full join

Here are all types of JOINS and their uses with examples:

• 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();

//SQL QUERY:
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');

Recommended Tutorials