连接表并保留主表中的所有行

Question

How can you join tables while keeping all rows from the main table?
Specifically using CodeIgniter's database library.

For example, the below code only returns joined rows from table1 and table2 where the condition applies for both tables.
How do you return all rows from table1 and only join rows from table2 where the condition applies?

$this->db->join('table2', 'table2.col = table1.col');
$query = $this->db->get('table1');

Answer 1

The LEFT JOIN clause includes all rows from the "left" table (table1 in this case).

In CodeIgniter this is done by adding it as a third parameter in the join method:

$this->db->join('table2', 'table2.col = table1.col', 'left');
$query = $this->db->get('table1');