Couchbase多表查询 #

一、JOIN概述 #

1.1 Couchbase JOIN类型 #

Couchbase支持多种JOIN方式：

类型 说明 使用场景
INNER JOIN 内连接 匹配的记录
LEFT JOIN 左连接 左表所有记录
RIGHT JOIN 右连接 右表所有记录
NEST 嵌套连接 一对多关系
UNNEST 数组展开 数组字段

1.2 JOIN与关系数据库的区别 #

text 
关系数据库：
- 表之间通过外键关联
- JOIN基于列值匹配

Couchbase：
- 文档之间通过Key关联
- JOIN基于文档Key或字段值
- 支持嵌套文档和数组

二、USE KEYS JOIN #

2.1 基本语法 #

sql 
SELECT *
FROM bucket1
JOIN bucket2 ON KEYS bucket1.foreign_key;

2.2 单个Key JOIN #

sql 
SELECT 
    u.name,
    u.email,
    o.order_id,
    o.amount
FROM `my-bucket`.`_default`.`_default` u
JOIN `my-bucket`.`_default`.`_default` o ON KEYS o.user_id
WHERE u.type = 'user' AND o.type = 'order';

2.3 反向JOIN #

sql 
SELECT 
    o.order_id,
    o.amount,
    u.name,
    u.email
FROM `my-bucket`.`_default`.`_default` o
JOIN `my-bucket`.`_default`.`_default` u ON KEYS o.user_id
WHERE o.type = 'order' AND u.type = 'user';

2.4 多个Key JOIN #

sql 
SELECT 
    o.order_id,
    p.name AS product_name,
    oi.quantity,
    oi.price
FROM `my-bucket`.`_default`.`_default` o
UNNEST o.items AS oi
JOIN `my-bucket`.`_default`.`_default` p ON KEYS oi.product_id
WHERE o.type = 'order' AND p.type = 'product';

三、ANSI JOIN #

3.1 基本语法 #

sql 
SELECT *
FROM bucket1 alias1
JOIN bucket2 alias2 ON alias1.field = alias2.field;

3.2 INNER JOIN #

sql 
SELECT 
    u.name,
    u.email,
    o.order_id,
    o.amount
FROM `my-bucket`.`_default`.`_default` u
INNER JOIN `my-bucket`.`_default`.`_default` o 
    ON META(u).id = o.user_id
WHERE u.type = 'user' AND o.type = 'order';

3.3 LEFT JOIN #

sql 
SELECT 
    u.name,
    u.email,
    o.order_id,
    o.amount
FROM `my-bucket`.`_default`.`_default` u
LEFT JOIN `my-bucket`.`_default`.`_default` o 
    ON META(u).id = o.user_id AND o.type = 'order'
WHERE u.type = 'user';

3.4 RIGHT JOIN #

sql 
SELECT 
    u.name,
    u.email,
    o.order_id,
    o.amount
FROM `my-bucket`.`_default`.`_default` u
RIGHT JOIN `my-bucket`.`_default`.`_default` o 
    ON META(u).id = o.user_id AND u.type = 'user'
WHERE o.type = 'order';

3.5 多表JOIN #

sql 
SELECT 
    u.name,
    o.order_id,
    p.name AS product_name,
    oi.quantity
FROM `my-bucket`.`_default`.`_default` u
INNER JOIN `my-bucket`.`_default`.`_default` o 
    ON META(u).id = o.user_id AND o.type = 'order'
INNER JOIN `my-bucket`.`_default`.`_default` oi 
    ON META(o).id = oi.order_id AND oi.type = 'order_item'
INNER JOIN `my-bucket`.`_default`.`_default` p 
    ON oi.product_id = META(p).id AND p.type = 'product'
WHERE u.type = 'user';

四、NEST操作 #

4.1 NEST概述 #

NEST将匹配的多个文档作为数组嵌套到结果中：

sql 
SELECT *
FROM bucket1
NEST bucket2 ON KEYS bucket1.array_of_keys;

4.2 基本NEST #

sql 
SELECT 
    u.name,
    u.email,
    orders
FROM `my-bucket`.`_default`.`_default` u
NEST `my-bucket`.`_default`.`_default` orders 
    ON KEYS ARRAY 'order::' || o.id FOR o IN u.order_ids END
WHERE u.type = 'user';

4.3 ANSI NEST #

sql 
SELECT 
    u.name,
    orders
FROM `my-bucket`.`_default`.`_default` u
NEST `my-bucket`.`_default`.`_default` o 
    ON META(u).id = o.user_id AND o.type = 'order'
WHERE u.type = 'user';

4.4 LEFT OUTER NEST #

sql 
SELECT 
    u.name,
    orders
FROM `my-bucket`.`_default`.`_default` u
LEFT OUTER NEST `my-bucket`.`_default`.`_default` o 
    ON META(u).id = o.user_id AND o.type = 'order'
WHERE u.type = 'user';

五、UNNEST操作 #

5.1 UNNEST概述 #

UNNEST将数组字段展开为多行：

sql 
SELECT *
FROM bucket
UNNEST array_field AS alias;

5.2 基本UNNEST #

sql 
SELECT 
    name,
    hobby
FROM `my-bucket`.`_default`.`_default`
UNNEST hobbies AS hobby
WHERE type = 'user';

5.3 UNNEST嵌套对象数组 #

sql 
SELECT 
    o.order_id,
    item.product_id,
    item.quantity,
    item.price
FROM `my-bucket`.`_default`.`_default` o
UNNEST o.items AS item
WHERE o.type = 'order';

5.4 UNNEST与JOIN结合 #

sql 
SELECT 
    o.order_id,
    p.name AS product_name,
    item.quantity,
    item.price
FROM `my-bucket`.`_default`.`_default` o
UNNEST o.items AS item
JOIN `my-bucket`.`_default`.`_default` p 
    ON KEYS item.product_id
WHERE o.type = 'order' AND p.type = 'product';

5.5 多层UNNEST #

sql 
SELECT 
    u.name,
    addr.type AS address_type,
    addr.value AS address_value
FROM `my-bucket`.`_default`.`_default` u
UNNEST OBJECT_PAIRS(u.addresses) AS addr
WHERE u.type = 'user';

六、实际应用示例 #

6.1 用户与订单 #

sql 
SELECT 
    u.name,
    u.email,
    COUNT(o.order_id) AS order_count,
    SUM(o.amount) AS total_amount
FROM `my-bucket`.`_default`.`_default` u
LEFT JOIN `my-bucket`.`_default`.`_default` o 
    ON META(u).id = o.user_id AND o.type = 'order'
WHERE u.type = 'user'
GROUP BY u.name, u.email
ORDER BY total_amount DESC;

6.2 订单详情 #

sql 
SELECT 
    o.order_id,
    o.created_at,
    u.name AS customer_name,
    ARRAY_AGG({
        'product': p.name,
        'quantity': item.quantity,
        'price': item.price
    }) AS items,
    SUM(item.quantity * item.price) AS total
FROM `my-bucket`.`_default`.`_default` o
JOIN `my-bucket`.`_default`.`_default` u 
    ON o.user_id = META(u).id AND u.type = 'user'
UNNEST o.items AS item
JOIN `my-bucket`.`_default`.`_default` p 
    ON KEYS item.product_id
WHERE o.type = 'order'
GROUP BY o.order_id, o.created_at, u.name;

6.3 产品分类层级 #

sql 
WITH RECURSIVE category_path AS (
    SELECT 
        META().id,
        name,
        parent_id,
        name AS full_path,
        1 AS level
    FROM `my-bucket`.`_default`.`_default`
    WHERE type = 'category' AND parent_id IS NULL
    
    UNION ALL
    
    SELECT 
        META(c).id,
        c.name,
        c.parent_id,
        cp.full_path || ' > ' || c.name,
        cp.level + 1
    FROM `my-bucket`.`_default`.`_default` c
    JOIN category_path cp ON c.parent_id = cp.id
    WHERE c.type = 'category'
)
SELECT 
    p.name AS product_name,
    cp.full_path AS category_path
FROM `my-bucket`.`_default`.`_default` p
JOIN category_path cp ON p.category_id = cp.id
WHERE p.type = 'product';

6.4 购物车分析 #

sql 
SELECT 
    p1.name AS product1,
    p2.name AS product2,
    COUNT(*) AS co_occurrence
FROM `my-bucket`.`_default`.`_default` o
UNNEST o.items AS item1
UNNEST o.items AS item2
JOIN `my-bucket`.`_default`.`_default` p1 ON KEYS item1.product_id
JOIN `my-bucket`.`_default`.`_default` p2 ON KEYS item2.product_id
WHERE o.type = 'order'
  AND item1.product_id < item2.product_id
GROUP BY p1.name, p2.name
ORDER BY co_occurrence DESC
LIMIT 10;

七、JOIN性能优化 #

7.1 创建索引 #

sql 
CREATE INDEX idx_orders_user_id 
ON `my-bucket`.`_default`.`_default`(user_id)
WHERE type = 'order';

CREATE INDEX idx_order_items_product_id 
ON `my-bucket`.`_default`.`_default`(product_id)
WHERE type = 'order_item';

7.2 使用USE INDEX提示 #

sql 
SELECT u.name, o.order_id
FROM `my-bucket`.`_default`.`_default` u
USE INDEX (idx_users_type)
JOIN `my-bucket`.`_default`.`_default` o 
USE INDEX (idx_orders_user_id)
    ON META(u).id = o.user_id
WHERE u.type = 'user' AND o.type = 'order';

7.3 优化JOIN顺序 #

sql 
SELECT u.name, o.order_id
FROM `my-bucket`.`_default`.`_default` o
JOIN `my-bucket`.`_default`.`_default` u 
    ON o.user_id = META(u).id AND u.type = 'user'
WHERE o.type = 'order' AND o.amount > 1000;

7.4 使用HASH JOIN #

sql 
SELECT u.name, o.order_id
FROM `my-bucket`.`_default`.`_default` u
USE HASH(build)
JOIN `my-bucket`.`_default`.`_default` o 
    ON META(u).id = o.user_id
WHERE u.type = 'user' AND o.type = 'order';

八、复杂查询示例 #

8.1 用户行为分析 #

sql 
SELECT 
    u.name,
    COUNT(DISTINCT o.order_id) AS order_count,
    COUNT(DISTINCT r.review_id) AS review_count,
    AVG(r.rating) AS avg_rating,
    SUM(o.amount) AS total_spent
FROM `my-bucket`.`_default`.`_default` u
LEFT JOIN `my-bucket`.`_default`.`_default` o 
    ON META(u).id = o.user_id AND o.type = 'order'
LEFT JOIN `my-bucket`.`_default`.`_default` r 
    ON META(u).id = r.user_id AND r.type = 'review'
WHERE u.type = 'user'
GROUP BY u.name
HAVING COUNT(DISTINCT o.order_id) > 0
ORDER BY total_spent DESC;

8.2 产品推荐 #

sql 
SELECT 
    p.name,
    COUNT(DISTINCT o.user_id) AS buyer_count,
    AVG(item.rating) AS avg_rating
FROM `my-bucket`.`_default`.`_default` p
JOIN `my-bucket`.`_default`.`_default` item 
    ON META(p).id = item.product_id AND item.type = 'order_item'
JOIN `my-bucket`.`_default`.`_default` o 
    ON item.order_id = META(o).id AND o.type = 'order'
WHERE p.type = 'product'
  AND p.category_id = $category_id
GROUP BY p.name
ORDER BY buyer_count DESC, avg_rating DESC
LIMIT 10;

8.3 销售报表 #

sql 
SELECT 
    DATE_FORMAT_STR(o.created_at, 'YYYY-MM') AS month,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(item.quantity * item.price) AS revenue,
    COUNT(DISTINCT o.user_id) AS customer_count,
    AVG(item.quantity * item.price) AS avg_order_value
FROM `my-bucket`.`_default`.`_default` o
UNNEST o.items AS item
WHERE o.type = 'order'
  AND o.created_at BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY DATE_FORMAT_STR(o.created_at, 'YYYY-MM')
ORDER BY month;

九、常见问题 #

9.1 JOIN结果为空 #

sql 
SELECT u.name, o.order_id
FROM `my-bucket`.`_default`.`_default` u
LEFT JOIN `my-bucket`.`_default`.`_default` o 
    ON META(u).id = o.user_id AND o.type = 'order'
WHERE u.type = 'user';

9.2 性能问题 #

sql 
SELECT u.name, o.order_id
FROM `my-bucket`.`_default`.`_default` u
JOIN `my-bucket`.`_default`.`_default` o 
    ON META(u).id = o.user_id
WHERE u.type = 'user' AND o.type = 'order'
    AND o.amount > 1000
LIMIT 100;

9.3 重复数据 #

sql 
SELECT DISTINCT u.name, o.order_id
FROM `my-bucket`.`_default`.`_default` u
JOIN `my-bucket`.`_default`.`_default` o 
    ON META(u).id = o.user_id
WHERE u.type = 'user' AND o.type = 'order';

十、总结 #

JOIN操作要点：

类型 说明 使用场景
USE KEYS JOIN 基于Key的JOIN 高性能、已知Key
ANSI JOIN 基于条件的JOIN 灵活、通用
NEST 嵌套结果 一对多关系
UNNEST 数组展开 数组字段

最佳实践：

  1. 为JOIN字段创建索引
  2. 选择合适的JOIN类型
  3. 注意JOIN顺序影响性能
  4. 使用EXPLAIN分析查询计划
  5. 合理使用LEFT JOIN避免数据丢失

下一步，让我们学习索引管理！

最后更新：2026-03-27