SQL JOIN 详解：LEFT 和 INNER 的区别

先想行的配对，别想圆圈。对第一张表里的每一行，数据库会去第二张表里找出所有满足连接条件的行。一个客户有两笔订单，结果里就成了两行。inner join 只保留配对成功的那些。

-- users                        -- orders
-- id | name                    -- id | user_id | amount
-- 1  | Ada                     -- 1  | 1       | 19.99
-- 2  | Lin                     -- 2  | 1       | 5.00
-- 3  | Sam                     -- 3  | 2       | 42.50
--          (Sam has no orders — watch what happens to Sam)

SELECT users.name, orders.amount
FROM orders
JOIN users ON orders.user_id = users.id;

-- Ada | 19.99
-- Ada |  5.00      ← Ada matched twice → appears twice
-- Lin | 42.50
-- (no Sam. no match = no row. that's INNER.)

left join 承诺保留写在前面那张表的每一行，哪怕它一个搭子都找不到。第二张表缺的那些列，会被填成 null。想决定谁写在前面，把需求念出来：每个用户和他的订单，那 users 就得放前面。

SELECT users.name, orders.amount
FROM users
LEFT JOIN orders ON orders.user_id = users.id;

-- Ada | 19.99
-- Ada |  5.00
-- Lin | 42.50
-- Sam | NULL       ← kept! right side filled with NULL

要找从来没下过单的客户，先用 left join 把所有客户留住，再挑出订单那一侧为空的行。要记住的故障症状是：给订单加了个过滤条件之后，客户少了几个。把那个条件搬进连接条件里，没配上的客户才能活下来。

-- "users who never ordered anything"
SELECT users.name
FROM users
LEFT JOIN orders ON orders.user_id = users.id
WHERE orders.id IS NULL;      -- the missing match IS the filter
-- → Sam

三张表的连接不是什么新东西。先连上一张，再把下一张连到已经变宽的结果上。别名起短一点，连接关系才看得清。right join 只是多塞给你一套思维模型，没有别的好处；把表的顺序调一下，写成 left join。

SELECT u.name, o.amount, p.title
FROM orders o
JOIN users    u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id;
-- each JOIN glues one more table onto the widening row.
-- short aliases (o, u, p) keep it readable.

RIGHT JOIN   -- LEFT JOIN with the tables swapped. almost nobody
             -- writes it; rewrite as LEFT and keep one mental model.
FULL JOIN    -- keep unmatched rows from BOTH sides. rare; audits.
CROSS JOIN   -- every pairing (no ON). sizes × colors = variants.