高级连接：保留缺失、构造组合与处理层级

SELECT
    customer_id,
    customer_name,
    city,
    registered_at
FROM customers
WHERE customer_id BETWEEN 1 AND 10
ORDER BY customer_id;
 
SELECT
    order_id,
    customer_id,
    order_date,
    status,
    total_amount
FROM orders
WHERE order_id BETWEEN 101 AND 114
ORDER BY order_id;

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.status
FROM customers AS c
INNER JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE c.customer_id BETWEEN 1 AND 10
  AND o

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.status
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
   AND o.order_id BETWEEN 101 AND 114
WHERE c

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
   AND o.order_id BETWEEN 101 AND 114
WHERE

-- 这条写法会把没有订单的顾客数成 1
SELECT
    c.customer_id,
    COUNT(*) AS wrong_order_count
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE c.customer_id = 7
GROUP BY c.customer_id;

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.status
FROM orders AS o
RIGHT JOIN customers AS c
    ON o.customer_id = c.customer_id
   AND o.order_id BETWEEN 101 AND 114
WHERE c

FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id

-- PostgreSQL：原生 FULL OUTER JOIN
WITH buyers_2025 AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= TIMESTAMP '2025-01-01 00:00:00'
      AND order_date <  TIMESTAMP '2026-01-01 00:00:00'
),
buyers_2026 AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= TIMESTAMP '2026-01-01 00:00:00'
      AND order_date <  TIMESTAMP '2027-01-01 00:00:00'

WITH buyers_2025 AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= '2025-01-01'
      AND order_date <  '2026-01-01'
),
buyers_2026 AS (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date >= '2026-01-01'
      AND order_date <  '2027-01-01'
)
SELECT
    y2025.customer_id AS

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.status
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
   AND o.status IN ('已支付', '已发货', 

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.status
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
   AND o.order_id BETWEEN 101 AND 114
WHERE c

SELECT DISTINCT
    c.customer_id,
    c.customer_name
FROM customers AS c
INNER JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE o.status IN ('已支付', '已发货', '已完成');

WHERE o.status IN ('已支付', '已发货', '已完成')
   OR o.status IS NULL

SELECT
    c.customer_id,
    c.customer_name
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.customer_id;

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    o.status AS order_status,
    p.payment_id,
    p.amount AS payment_amount,
    p.status AS payment_status
FROM customers AS c
LEFT JOIN orders AS o
    ON

WITH payment_summary AS (
    SELECT
        order_id,
        SUM(CASE WHEN status = '成功' THEN amount ELSE 0 END) AS paid_amount,
        COUNT(CASE WHEN status = '成功' THEN 1 END) AS success_payment_count
    FROM payments
    GROUP BY order_id
)
SELECT
    c

-- 正常：订单明细属于订单，按 order_id 连接
LEFT JOIN order_items AS oi
    ON oi.order_id = o.order_id
 
-- 错误：把明细接到支付是否存在上
LEFT JOIN order_items AS oi
    ON oi.order_id = p.order_id

customers.customer_id
        ↓
orders.customer_id
        ├── orders.order_id → order_items.order_id
        └── orders.order_id → payments.order_id

SELECT
    p.product_id,
    p.product_name,
    oi.order_item_id,
    oi.quantity AS item_quantity,
    movement.movement_id,
    movement.quantity AS movement_quantity
FROM products AS p
LEFT JOIN order_items AS oi
    ON oi.product_id = p

SELECT
    p.product_id,
    COUNT(*) AS joined_row_count,
    SUM(oi.quantity) AS item_quantity_after_join,
    SUM(movement.quantity) AS movement_quantity_after_join
FROM products AS p
LEFT JOIN order_items AS oi
    ON oi.product_id = p.

WITH item_summary AS (
    SELECT
        product_id,
        SUM(quantity) AS ordered_quantity,
        COUNT(*) AS item_line_count
    FROM order_items
    GROUP BY product_id
),
movement_summary AS (
    SELECT
        product_id,
        SUM(quantity) AS net_movement_quantity,
        COUNT(*) AS movement_count
    FROM inventory_movements

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    p.payment_id
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
LEFT JOIN payments AS p
    ON p.order_id = o

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    p.payment_id
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
LEFT JOIN payments AS p
    ON p.order_id = o

WITH paid_orders AS (
    SELECT DISTINCT
        o.order_id,
        o.customer_id
    FROM orders AS o
    INNER JOIN payments AS p
        ON p.order_id = o.order_id
       AND p.status = '成功'
)
SELECT
    c.customer_id,
    c

-- 先把顾客与订单连接，再保留这个中间结果去找支付
(customers AS c
 LEFT JOIN orders AS o
   ON o.customer_id = c.customer_id)
LEFT JOIN payments AS p
  ON p.order_id = o.order_id

-- 先形成“订单与支付”的整体，再把它作为右侧连接给顾客
customers AS c
LEFT JOIN (
    orders AS o
    INNER JOIN payments AS p
      ON p.order_id = o.order_id
     AND p.status = '成功'
)
  ON o.customer_id = c.customer_id

SELECT
    child.category_id,
    child.category_name,
    parent.category_id AS parent_category_id,
    parent.category_name AS parent_category_name
FROM categories AS child
LEFT JOIN categories AS parent
    ON parent.category_id = child.parent_id
WHERE child.category_id IN (

SELECT
    parent.category_id,
    parent.category_name
FROM categories AS parent
LEFT JOIN categories AS child
    ON child.parent_id = parent.category_id
WHERE parent.category_id IN (1, 2, 3, 4, 5)
  AND child.

WITH RECURSIVE category_tree AS (
    SELECT
        category_id,
        category_name,
        parent_id,
        0 AS depth,
        CAST(category_name AS CHAR(500)) AS category_path
    FROM categories
    WHERE parent_id IS NULL
 
    UNION ALL
 
    SELECT
        child.category_id,
        child.category_name,

SELECT
    child.category_id,
    child.category_name,
    child.parent_id AS missing_parent_id
FROM categories AS child
LEFT JOIN categories AS parent
    ON parent.category_id = child.parent_id
WHERE child.parent_id IS NOT NULL
  AND parent.category_id IS NULL

SELECT
    employee.employee_id,
    employee.employee_name,
    manager.employee_name AS manager_name
FROM employees AS employee
LEFT JOIN employees AS manager
    ON manager.employee_id = employee.manager_id
ORDER BY employee.employee_id;

WITH RECURSIVE dates AS (
    SELECT DATE('2025-06-18') AS report_date
 
    UNION ALL
 
    SELECT report_date + INTERVAL 1 DAY
    FROM dates
    WHERE report_date < '2025-06-21'
),
selected_products AS (
    SELECT product_id, product_name
    FROM products
    WHERE product_id IN (1, 

WITH dates AS (
    SELECT report_time::date AS report_date
    FROM generate_series(
        TIMESTAMP '2025-06-18 00:00:00',
        TIMESTAMP '2025-06-21 00:00:00',
        INTERVAL '1 day'
    ) AS series(report_time)
),
selected_products AS (
    SELECT product_id, product_name
    FROM products
    WHERE product_id IN (1, 2, 

WITH RECURSIVE dates AS (
    SELECT DATE('2025-06-18') AS report_date
    UNION ALL
    SELECT report_date + INTERVAL 1 DAY
    FROM dates
    WHERE report_date < '2025-06-21'
),
selected_products AS (
    SELECT product_id, product_name
    FROM products
    WHERE product_id IN (1, 2

WITH package_options AS (
    SELECT '普通包装' AS package_name
    UNION ALL
    SELECT '礼盒包装'
)
SELECT
    p.product_id,
    p.product_name,
    option_set.package_name
FROM products AS p
CROSS JOIN package_options AS option_set
WHERE p.product_id IN (1, 

SELECT COUNT(*) AS product_count
FROM products
WHERE status = '在售';
 
SELECT DATEDIFF('2025-06-30', '2025-06-01') + 1 AS date_count;

SELECT
    customer_id,
    customer_name,
    order_id
FROM customers
NATURAL JOIN orders;

WITH product_list AS (
    SELECT
        product_id,
        product_name,
        status
    FROM products
),
movement_flags AS (
    SELECT
        product_id,
        movement_type
    FROM inventory_movements
)
SELECT *
FROM product_list
NATURAL JOIN movement_flags;

WITH product_list AS (
    SELECT
        product_id,
        product_name,
        status
    FROM products
),
movement_flags AS (
    SELECT
        product_id,
        movement_type AS status
    FROM inventory_movements
)
SELECT *
FROM product_list
NATURAL JOIN movement_flags;

ON product_list.product_id = movement_flags.product_id

ON product_list.product_id = movement_flags.product_id
AND product_list.status = movement_flags.status

SELECT
    customer_id,
    c.customer_name,
    o.order_id
FROM customers AS c
JOIN orders AS o USING (customer_id);

FROM customers AS c
JOIN orders AS o
    ON o.customer_id = c.customer_id

FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
   AND o.status IN ('已支付', '已发货', '已完成')
WHERE c.city = '杭州'

-- 全部商品，零销量也要保留
FROM products AS p
LEFT JOIN sales_summary AS s
    ON s.product_id = p.product_id

-- 只看已经形成的销售汇总及商品信息
FROM sales_summary AS s
INNER JOIN products AS p
    ON p.product_id = s.product_id

SELECT COUNT(*) AS customer_rows FROM customers;
SELECT COUNT(*) AS order_rows FROM orders;
SELECT COUNT(*) AS product_rows FROM products;
SELECT COUNT(*) AS order_item_rows FROM order_items;

-- 第一步：保留侧有 10 行
SELECT customer_id, customer_name
FROM customers;
 
-- 第二步：加订单，但先不加 WHERE 中的右表条件
SELECT c.customer_id, c.customer_name, o.order_id, o.status
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id;
 
-- 第三步：只检查补位行是否还在

SELECT
    o.order_id,
    o.customer_id AS order_customer_id,
    c.customer_id AS matched_customer_id,
    c.customer_name
FROM orders AS o
LEFT JOIN customers AS c
    ON c.customer_id = o.customer_id
ORDER BY o.order_id;

SELECT
    order_id,
    COUNT(*) AS row_count
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

SELECT product_id, COUNT(*) AS item_rows
FROM order_items
WHERE product_id = 5
GROUP BY product_id;
 
SELECT product_id, COUNT(*) AS movement_rows
FROM inventory_movements
WHERE product_id = 5
GROUP BY product_id;

SELECT
    p.product_id,
    COUNT(*) AS joined_rows,
    COUNT(DISTINCT oi.order_item_id) AS distinct_item_rows,
    COUNT(DISTINCT movement.movement_id) AS distinct_movement_rows
FROM products AS p
LEFT JOIN order_items AS oi
    ON oi.product_id =

SUM(DISTINCT oi.unit_price)

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id,
    CASE
        WHEN o.order_id IS NULL THEN '没有匹配订单'
        ELSE '匹配成功'
    END AS match_state
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c

WITH all_order_summary AS (
    SELECT
        customer_id,
        COUNT(*) AS total_order_count
    FROM orders
    GROUP BY customer_id
),
valid_order_summary AS (
    SELECT
        customer_id,
        COUNT(*) AS valid_order_count,
        SUM(total_amount) AS valid_order_amount
    FROM orders
    WHERE status IN (

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(*) AS order_count
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name;

SELECT p.product_id, p.product_name, oi.order_item_id
FROM order_items AS oi
RIGHT JOIN products AS p
    ON p.product_id = oi.product_id;