CodeIgniter 資料庫 JOIN 查詢完整用法指南

CodeIgniter Query Builder 提供直觀的 join() 方法，支援 INNER、LEFT、RIGHT、OUTER 等多種聯結類型，並能處理複雜條件、別名、多表聯結。本文系統整理語法、參數、實戰範例與效能建議。

一、基本語法

$this->db->join($table, $cond, $type = '', $escape = TRUE);

二、核心範例

1. 簡單 INNER JOIN

$this->db->select('users.name, orders.order_no, orders.total');
$this->db->from('users');
$this->db->join('orders', 'orders.user_id = users.id');
$query = $this->db->get();
// SQL: SELECT users.name, orders.order_no, orders.total FROM users JOIN orders ON orders.user_id = users.id

2. LEFT JOIN (保留左表所有資料)

$this->db->select('p.title, c.name AS category');
$this->db->from('posts p');
$this->db->join('categories c', 'c.id = p.category_id', 'left');
$query = $this->db->get();
// 無分類的文章仍會列出，category 為 NULL

3. 多表聯結 (鏈式呼叫)

$this->db->select('u.name, o.order_no, oi.product_name, oi.qty');
$this->db->from('users u');
$this->db->join('orders o', 'o.user_id = u.id');
$this->db->join('order_items oi', 'oi.order_id = o.id');
$this->db->join('products pr', 'pr.id = oi.product_id', 'left');
$query = $this->db->get();

4. 使用別名避免欄位衝突

$this->db->select('u.id AS user_id, u.name, c.id AS cat_id, c.name AS cat_name');
$this->db->from('users u');
$this->db->join('categories c', 'c.id = u.category_id');

5. 複雜 ON 條件 (陣列/多條件)

$this->db->join('logs l', [
    'l.user_id = u.id',
    'l.type' => 'login',
    'l.created_at >' => '2024-01-01'
]);

三、進階技巧

子查詢聯結 (Derived Table)

$sub = $this->db->select('user_id, COUNT(*) AS cnt')
                ->from('orders')
                ->group_by('user_id')
                ->get_compiled_select(); // 取得 SQL 字串

$this->db->select('u.name, COALESCE(o.cnt, 0) AS order_count');
$this->db->from('users u');
$this->db->join("($sub) o", 'o.user_id = u.id', 'left');

聯結後再 WHERE 過濾

$this->db->join('profiles p', 'p.user_id = u.id');
$this->db->where('p.status', 'active'); // 等同於 ON 條件延伸

四、效能優化關鍵點

1. 只 SELECT 需要欄位：避免 SELECT * 造成網路/記憶體浪費。
2. 確保 JOIN 條件欄位有索引：EXPLAIN 檢查 type = ref/eq_ref。
3. 大表驅動小表：左表應為結果集較小的表 (配合 WHERE 過濾)。
4. 避免不必要的 LEFT JOIN：若確定關聯必存在，用 INNER JOIN 讓優化器更好下推條件。
5. 分頁查詢優化：先用子查詢取 ID 再 JOIN 詳細欄位，避免 LIMIT 100000, 20 深分頁效能崩塌。

五、常見錯誤與除錯

Unknown column 'xxx' in 'on clause'

欄位名拼錯、未加表別名導致歧義、資料庫實際無此欄位。

SQL 語法錯誤近 'JOIN'

CI 版本過舊不支援 join() 多參數，請升級至 3.1+。

結果集重複行

一對多聯結導致左表行數乘以右表匹配數；使用 GROUP BY 或 DISTINCT，或改用子查詢聚合。

六、原生 SQL 對照 (除錯用)

echo $this->db->last_query(); // 顯示最後執行的完整 SQL

輸出範例：

SELECT `u`.`name`, `o`.`order_no` FROM `users` AS `u` LEFT JOIN `orders` AS `o` ON `o`.`user_id` = `u`.`id`