Codeigniter All join() Query With Example

Codeigniter All join() Query With Example

CodeIgniter Join Clause is used to combine rows with two or more tables, depending on the respective columns between them.

We use to codeigniter join query because we want to get or fetch records from diffrent tables, which is respective ralated each other.

CodeIgniter Join Query

We want to use multiple joins with table in CodeIgniter, we can repeat “$this->db->join( )” with tables.

Codeigniter Types of Joins

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

Leave a Reply

Your email address will not be published. Required fields are marked *