mysql中on的用法 mysql连接查询on条件写法

喵叔

2025-05-17 5

在 MySQL 中，ON 是用于 连接查询（JOIN） 的关键字，主要作用是指定表之间的连接条件。它与 WHERE 的区别在于：ON 定义连接时的匹配规则，而 WHERE 用于对连接后的结果进行过滤。

一、基本语法

在 JOIN 操作中，ON 必须与 JOIN 搭配使用，语法如下：

SELECT 列名
FROM 表1
JOIN 表2 ON 表1.列 = 表2.列;

二、不同连接类型中的 `ON` 用法

1. INNER JOIN（内连接）

只返回两个表满足 ON 条件的行。

示例：查询订单及对应的。

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

2. LEFT JOIN（左连接）

返回左表所有行，右表仅返回满足 ON 条件的行，不满足的补 NULL。

示例：查询所有客户及其订单（即使客户无订单）。

SELECT customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

3. RIGHT JOIN（右连接）

返回右表所有行，左表仅返回满足 ON 条件的行，不满足的补 NULL。

示例：查询所有订单及对应的客户（即使订单无客户）。

SELECT orders.order_id, customers.customer_name
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id;

4. FULL JOIN（全外连接）

MySQL 不直接支持 FULL JOIN，但可通过 LEFT JOIN + RIGHT JOIN + UNION 实现。

三、`ON` 的高级用法

1. 多条件连接

在 ON 中可使用 AND/OR 组合多个条件：

SELECT *
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id AND d.location = '上海';

2. 非等值连接

ON 不仅限于等值条件，还支持范围判断：

SELECT products.name, prices.amount
FROM products
JOIN prices ON products.id = prices.product_id 
            AND products.create_time >= prices.start_date;

3. 自连接

在自连接（同一表连接自身）时，ON 用于区分不同实例：

SELECT a.employee_name AS 员工, b.employee_name AS 上级
FROM employees a
LEFT JOIN employees b ON a.manager_id = b.employee_id;

四、`ON` 与 `WHERE` 的区别

ON：定义表之间的连接条件，发生在数据连接过程中。
WHERE：对连接后的结果进行过滤，发生在数据连接之后。

关键区别示例：

-- LEFT JOIN 中 ON 和 WHERE 的不同效果
SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id 
                 AND orders.amount > 100;  -- 右表仅显示金额>100的订单，左表保留所有客户

VS

SELECT customers.name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id 
WHERE orders.amount > 100;  -- 过滤掉左表中右表条件不满足的行（可能丢失部分客户）

五、`ON` 与 `USING` 的区别

当连接的列名相可用 USING(列名) 替代 ON：

SELECT *
FROM table1
JOIN table2 USING(id);  -- 等效于 ON table1.id = table2.id

USING 更简洁，但 ON 更灵活（可处理不同列名或复杂条件）。

ON 是连接查询的核心，用于指定表间的关联逻辑。
根据需求选择 INNER JOIN、LEFT JOIN 等连接类型，并合理组合 ON 中的条件。
注意区分 ON 和 WHERE 的作用时机，避免错误过滤数据。

(www.nzw6.com)