LEFT JOIN 与 left semi join 的区别

好的,我再给你举一个使用 Hive SQL 的例子,比较 `LEFT JOIN` 和 `LEFT SEMI JOIN` 的区别,并加入测试数据。

假设我们有两个表:

1. `customers` 表:
   - `customer_id`: 客户 ID
   - `customer_name`: 客户名称
   - `customer_email`: 客户邮箱

2. `orders` 表:
   - `order_id`: 订单 ID
   - `customer_id`: 下单客户 ID
   - `order_amount`: 订单金额

我们先创建这两个表并插入一些测试数据:

```sql
CREATE TABLE customers (
  customer_id INT,
  customer_name STRING,
  customer_email STRING
);

INSERT INTO customers VALUES
  (1, 'John Doe', 'john.doe@example.com'),
  (2, 'Jane Smith', 'jane.smith@example.com'),
  (3, 'Bob Johnson', 'bob.johnson@example.com'),
  (4, 'Alice Williams', 'alice.williams@example.com'),
  (5, 'Tom Lee', 'tom.lee@example.com');

CREATE TABLE orders (
  order_id INT,
  customer_id INT,
  order_amount DOUBLE
);

INSERT INTO orders VALUES
  (1, 1, 100.0),
  (2, 2, 200.0),
  (3, 1, 150.0),
  (4, 3, 300.0),
  (5, 4, 120.0),
  (6, 2, 180.0),
  (7, 5, 250.0);
```

现在,让我们看看 `LEFT JOIN` 和 `LEFT SEMI JOIN` 的区别:

1. **使用 `LEFT JOIN`**

```sql
SELECT
  c.customer_id,
  c.customer_name,
  c.customer_email,
  COUNT(o.order_id) AS order_count,
  SUM(o.order_amount) AS total_order_amount
FROM
  customers c
  LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY
  c.customer_id,
  c.customer_name,
  c.customer_email;
```

这个查询会返回以下结果:

| customer_id | customer_name | customer_email           | order_count | total_order_amount |
|-------------|---------------|--------------------------|-------------|-------------------|
| 1           | John Doe      | john.doe@example.com     | 2           | 250.0             |
| 2           | Jane Smith    | jane.smith@example.com   | 2           | 380.0             |
| 3           | Bob Johnson   | bob.johnson@example.com  | 1           | 300.0             |
| 4           | Alice Williams| alice.williams@example.com| 1           | 120.0             |
| 5           | Tom Lee       | tom.lee@example.com      | 1           | 250.0             |

使用 `LEFT JOIN`，我们可以获取所有客户的信息,包括没有下过订单的客户。对于没有订单的客户,`order_count` 和 `total_order_amount` 列会显示为 0 和 NULL。

2. **使用 `LEFT SEMI JOIN`**

```sql
SELECT
  c.customer_id,
  c.customer_name,
  c.customer_email
FROM
  customers c
  LEFT SEMI JOIN orders o ON c.customer_id = o.customer_id;
```

这个查询会返回以下结果:

| customer_id | customer_name | customer_email           |
|-------------|---------------|--------------------------|
| 1           | John Doe      | john.doe@example.com     |
| 2           | Jane Smith    | jane.smith@example.com   |
| 3           | Bob Johnson   | bob.johnson@example.com  |
| 4           | Alice Williams| alice.williams@example.com|
| 5           | Tom Lee       | tom.lee@example.com      |

使用 `LEFT SEMI JOIN`，我们只能获取有下过订单的客户信息。对于没有订单的客户,它们不会出现在结果集中。
并且不能展示右表的数据。

总的来说,`LEFT JOIN` 会返回左表中的所有行,并将右表中匹配的行合并到结果集中。而 `LEFT SEMI JOIN` 只会返回左表中与右表有匹配的行,不会返回左表中没有匹配的行。

根据具体的业务需求,选择合适的连接方式可以提高查询效率和返回结果的准确性。

如果你有任何其他问题,欢迎继续问我。

 

 

 

• ‌left join‌适用于需要从两个表中获取所有相关记录的场景，尤其是当右表中的记录可能重复时。
• ‌left semi join‌适用于只需要获取左表中记录的场景，尤其是当右表中的记录可能重复且不需要重复显示左表记录时。

 重点讨论了LEFT SEMI JOIN作为IN/EXISTS子查询高效实现的特点

 

查询   a left semi join b 

 

返回 A表 存在b 表中的数据

也就是 B表是大表的话 查询 在不在a表中 ， 有的话返回.