mysql left join的基本用法及on与where的区别是什么

在MySQL中，LEFT JOIN是一种常用的表连接操作，用于从左表中获取所有记录，并尝试与右表中的记录进行匹配。如果右表中没有匹配的记录，则结果集中右表的字段将显示为NULL。本文将介绍LEFT JOIN的基本用法，并探讨ON与WHERE在LEFT JOIN中的区别。

1. LEFT JOIN的基本用法

LEFT JOIN的基本语法如下：

SELECT 列名
FROM 左表
LEFT JOIN 右表
ON 左表.列名 = 右表.列名;

示例

假设我们有两个表：users和orders。

• users表结构：

| id | name | |—–|——-| | 1 | Alice | | 2 | Bob | | 3 | Carol |

• orders表结构：

| id | user_id | amount | |—–|———|——–| | 1 | 1 | 100 | | 2 | 2 | 200 | | 3 | 4 | 300 |

我们想要获取所有用户及其订单信息，即使某些用户没有订单。可以使用以下查询：

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

查询结果

在这个结果中，Carol没有对应的订单，因此amount字段显示为NULL。

2. ON与WHERE的区别

在LEFT JOIN中，ON和WHERE子句的作用有所不同，理解它们的区别对于正确使用LEFT JOIN至关重要。

2.1 ON子句

ON子句用于指定连接条件，即在连接两个表时，如何匹配记录。ON子句中的条件决定了哪些记录会被连接在一起。

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

在这个查询中，ON users.id = orders.user_id指定了连接条件，即users表中的id字段与orders表中的user_id字段相匹配。

2.2 WHERE子句

WHERE子句用于过滤结果集，即在连接完成后，对结果集进行进一步的筛选。WHERE子句中的条件会影响最终返回的记录。

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

在这个查询中，WHERE orders.amount > 100会过滤掉所有amount小于或等于100的记录。由于LEFT JOIN会返回左表中的所有记录，即使右表中没有匹配的记录，WHERE子句会进一步过滤这些记录。

2.3 ON与WHERE的区别

• ON子句：用于指定连接条件，决定哪些记录会被连接在一起。即使右表中没有匹配的记录，左表中的记录仍然会出现在结果集中，右表的字段显示为NULL。

• WHERE子句：用于过滤结果集，决定哪些记录会最终返回。WHERE子句会过滤掉不符合条件的记录，包括那些在LEFT JOIN中右表字段为NULL的记录。

示例对比

假设我们有以下查询：

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

查询结果

在这个结果中，Alice的订单金额为100，不满足WHERE条件，因此被过滤掉。Carol没有订单，amount为NULL，也不满足WHERE条件，因此也被过滤掉。

如果将WHERE条件改为ON条件：

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

查询结果

在这个结果中，Alice和Carol的订单金额不满足ON条件，因此amount字段显示为NULL，但左表中的记录仍然保留。

3. 总结

• LEFT JOIN用于从左表中获取所有记录，并尝试与右表中的记录进行匹配。如果右表中没有匹配的记录，则右表的字段显示为NULL。
• ON子句用于指定连接条件，决定哪些记录会被连接在一起。
• WHERE子句用于过滤结果集，决定哪些记录会最终返回。
• 在LEFT JOIN中，ON子句影响连接过程，而WHERE子句影响最终结果集。

理解ON与WHERE的区别，可以帮助我们更好地使用LEFT JOIN，避免在查询中出现意外的结果。