CodeIgniter left join, right, inner, Outer, Cross, full join

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');