SQL JOIN

INNER JOIN (内连接)    只返回两表都有匹配的行

-- 查询有订单的客户
SELECT c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- 结果: 只显示既在客户表又在订单表中存在的记录

LEFT JOIN (左连接)     返回左表全部 + 右表匹配的行(无匹配显示NULL)

-- 查询所有客户及其订单(包括没有订单的客户)
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

-- 结果: 所有客户都显示,没下过订单的客户订单列为 NULL

RIGHT JOIN (右连接)   返回右表全部 + 左表匹配的行(无匹配显示NULL)

-- 查询所有订单及客户信息(包括客户已删除的订单)
SELECT c.name, o.order_id, o.amount
FROM customers c
RIGHT JOIN orders o ON c.id = o.customer_id;

-- 结果: 所有订单都显示,客户信息缺失的显示 NULL

FULL JOIN (全外连接)    返回两表所有行(任一边无匹配显示NULL)

-- 查询所有客户和订单的完整情况
SELECT c.name, o.order_id, o.amount
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

-- 结果: 客户表和订单表的并集

LEFT SEMI JOIN (左半连接)    只返回左表中在右表有匹配的行,但不返回右表列

-- 查询有订单的客户(只显示客户信息)
SELECT c.*
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- 或使用 IN
SELECT c.*
FROM customers c
WHERE c.id IN (SELECT customer_id FROM orders);

-- 结果: 只返回下过订单的客户,不显示订单详情

LEFT ANTI JOIN (左反连接)     只返回左表中在右表没有匹配的行

-- 查询没有订单的客户
SELECT c.*
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- 或使用 NOT IN
SELECT c.*
FROM customers c
WHERE c.id NOT IN (SELECT customer_id FROM orders);

-- 或使用 LEFT JOIN + NULL 判断
SELECT c.*
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

/**
结果: 只返回从未下过订单的客户

---实战对比示例，假设数据:
customers: id=1(Alice), id=2(Bob), id=3(Carol)
orders: order_id=101(customer_id=1), order_id=102(customer_id=1), order_id=103(customer_id=4)

**/