MySQL left join的on、and和where的区别

引言

在MySQL中，使用JOIN语句可以将两个或多个表中的数据连接在一起，从而实现对这些表进行联合查询。其中最常用的JOIN类型之一是LEFT JOIN。在LEFT JOIN中，将从左表中选取所有的记录，并根据指定的条件连接右表中的记录。在这个过程中，我们可以使用ON、AND和WHERE来定义连接和筛选的条件。本文将详细解释这三者之间的区别和使用方法。

`LEFT JOIN`语法

LEFT JOIN的基本语法如下所示：

SELECT 列名
FROM 左表
LEFT JOIN 右表 ON 连接条件

`ON`的使用

定义连接条件

使用ON关键字可以在LEFT JOIN中定义连接条件。连接条件是指在两个表进行连接时，通过指定某个字段的值相等来建立关联关系。ON后面跟着的是一个逻辑表达式，用于指定两个表之间的连接条件。

示例代码：

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

运行结果：

+---------------+--------------+
| customer_name | order_number |
+---------------+--------------+
| John Smith    | 1001         |
| Alice Johnson | 1002         |
| Lisa Lee      | NULL         |
+---------------+--------------+

在上面的例子中，我们可以看到使用ON关键字将customers表和orders表连接在一起，并通过customers.customer_id = orders.customer_id建立了连接条件。这样就能够查询到客户名和订单号。

带有多个连接条件

当需要多个条件来进行连接时，可以通过使用AND关键字来连接多个条件。具体语法如下所示：

SELECT 列名
FROM 左表
LEFT JOIN 右表 ON 连接条件1 AND 连接条件2;

示例代码：

SELECT customers.customer_name, orders.order_number
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id AND orders.amount > 1000;

运行结果：

+---------------+--------------+
| customer_name | order_number |
+---------------+--------------+
| John Smith    | 1001         |
| Alice Johnson | NULL         |
| Lisa Lee      | NULL         |
+---------------+--------------+

在上面的例子中，我们添加了一个条件orders.amount > 1000，表示只查询订单金额大于1000的记录。由于Alice Johnson和Lisa Lee的订单金额都小于1000，所以最终结果中只包含了John Smith的记录。

`AND`的使用

定义连接条件

在LEFT JOIN中，AND关键字用于定义连接条件。与ON不同，AND关键字将连接条件放在WHERE子句中而不是ON子句中。

示例代码：

SELECT customers.customer_name, orders.order_number
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.amount > 1000;

运行结果：

+---------------+--------------+
| customer_name | order_number |
+---------------+--------------+
| John Smith    | 1001         |
+---------------+--------------+

在上面的例子中，我们将条件orders.amount > 1000放在了WHERE子句中，这样只有满足该条件的记录才会被返回。

筛选结果

除了用于定义连接条件外，AND还可以用于筛选结果。通过在WHERE子句中添加多个条件，并使用AND连接这些条件，可以对查询结果进行过滤。

示例代码：

SELECT customers.customer_name, orders.order_number
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.amount > 1000 AND customers.city = 'New York';

运行结果：

+---------------+--------------+
| customer_name | order_number |
+---------------+--------------+
| John Smith    | 1001         |
+---------------+--------------+

在上面的例子中，我们添加了一个条件customers.city = 'New York'，表示只查询客户所在城市为”New York”的记录。由于只有John Smith是来自于”New York”的，所以最终结果只返回了他的记录。

`WHERE`的使用

筛选结果

WHERE关键字用于对查询结果进行筛选。与AND不同，WHERE关键字可以用于连接多个条件，也可以与JOIN无关地使用。

示例代码：

SELECT customers.customer_name, orders.order_number
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.amount > 1000;

运行结果：

+---------------+--------------+
| customer_name | order_number |
+---------------+--------------+
| John Smith    | 1001         |
+---------------+--------------+

在上面的例子中，我们使用WHERE关键字筛选了orders.amount > 1000的记录。由于只有John Smith的订单金额满足该条件，所以只返回了他的记录。

不使用`JOIN`时使用`WHERE`

当不使用JOIN语句进行表连接时，WHERE关键字可以用于连接多个表的字段。

示例代码：

SELECT customers.customer_name, orders.order_number
FROM customers, orders
WHERE customers.customer_id = orders.customer_id
AND orders.amount > 1000;

运行结果：

+---------------+--------------+
| customer_name | order_number |
+---------------+--------------+
| John Smith    | 1001         |
+---------------+--------------+

在上面的例子中，我们使用了逗号将customers表和orders表连接在一起，然后使用WHERE关键字定义了连接条件和筛选条件。最终返回了满足条件的记录。

`ON`vs`AND`vs`WHERE`的区别

ON、AND和WHERE在LEFT JOIN中担任了不同的角色和功能，它们之间的区别可总结如下：

ON：用于定义连接条件，指定两个表之间的字段关系。
AND：用于在ON或WHERE子句中通过逻辑运算符连接多个条件。
WHERE：用于对查询结果进行筛选，不一定与JOIN有关。

需要注意的是，ON关键字只能在连接表时使用，而AND和WHERE关键字可以在连接表时使用，也可以在不连接表时使用。

结论

在使用LEFT JOIN语句时，使用ON关键字定义连接条件，AND关键字连接多个条件，WHERE关键字用于筛选结果。这三者在LEFT JOIN中分别承担了不同的角色和功能，掌握它们之间的区别和使用方法是进行联合查询的关键。

总之，在编写MySQL查询语句时，要根据实际需求选择合适的语句和关键字，避免混淆和错误的结果。

注意事项

在使用ON关键字时，必须确保连接条件是正确的，否则可能会导致无结果或错误的结果。同时，也要注意避免使用不必要的连接条件，以免降低查询效率。

使用AND关键字时，要注意逻辑运算符的优先级，并使用括号来明确逻辑关系。

在使用WHERE关键字时，要注意条件的顺序和逻辑关系，以避免错误的结果。

示例代码与运行结果

为了更好地理解LEFT JOIN中ON、AND和WHERE的区别，下面提供一个示例代码，展示了这三者的不同使用方式和运行结果。

-- 创建customers表
CREATE TABLE customers (
  customer_id INT,
  customer_name VARCHAR(50),
  city VARCHAR(50)
);

-- 插入数据
INSERT INTO customers (customer_id, customer_name, city)
VALUES 
  (1, 'John Smith', 'New York'),
  (2, 'Alice Johnson', 'Los Angeles'),
  (3, 'Lisa Lee', 'San Francisco');

-- 创建orders表
CREATE TABLE orders (
  order_id INT,
  order_number INT,
  customer_id INT,
  amount DECIMAL(10, 2)
);

-- 插入数据
INSERT INTO orders (order_id, order_number, customer_id, amount)
VALUES 
  (1, 1001, 1, 1500),
  (2, 1002, 2, 800),
  (3, 1003, 2, 1200),
  (4, 1004, 3, 500);

-- 使用ON连接条件
SELECT customers.customer_name, orders.order_number
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE customers.city = 'New York';

运行结果：

+---------------+--------------+
| customer_name | order_number |
+---------------+--------------+
| John Smith    | 1001         |
+---------------+--------------+

在上面的示例代码中，我们创建了一个包含两个表（customers和orders）的数据库，并插入了一些测试数据。然后，我们使用ON关键字指定了连接条件customers.customer_id = orders.customer_id，并在WHERE子句中添加了过滤条件customers.city = 'New York'。最终，我们只返回了John Smith来自”New York”的记录，它的订单号是1001。