MySQL:left join 后用 on 还是 where?

在MySQL中,LEFT JOIN用于返回左表(即LEFT JOIN关键字左边的表)的所有记录,即使在右表中没有匹配的记录。对于那些右表中没有匹配的记录,结果集中右表的部分会被填充为NULL。关于ONWHERE子句的使用,它们在LEFT JOIN中的作用是不同的:

  • ON 子句:用于定义连接条件,即决定哪些记录应该被连接在一起。当你需要基于左右表的某些字段值的关系来组合记录时,这些条件应该放在ON后面。如果在ON后面还有对右表的额外条件限制,这些也会在连接时应用,但不会影响左表返回所有记录的原则。
  • WHERE 子句:用于对已经通过JOIN操作产生的结果集进行进一步的过滤。当条件放在WHERE子句中时,那些不符合条件的记录(无论是左表的还是右表的)都会被移除,这可能会影响到左表返回所有记录的初衷,尤其是在处理NULL值时需格外小心。

具体案例一:

假设我们有两个表,一个是employees(员工表),另一个是departments(部门表),我们想找出所有员工及其所在的部门名称,即使某些员工没有分配到具体的部门。

employees 表:

idname
1Alice
2Bob
3Carol

departments 表:

idname
1HR
2IT
3Marketing

其中,employees表的department_id字段可以是NULL,表示没有分配部门。

使用 ON 示例:

SELECT employees.name, departments.name AS department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id;

这个查询会返回所有员工的名字,以及他们对应的部门名字,如果员工没有部门(即department_id为NULL),部门名字则为NULL。

使用 WHERE 示例:

SELECT employees.name, departments.name AS department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.id
WHERE departments.id IS NOT NULL;

这个查询试图过滤掉那些部门ID为NULL的记录,但由于使用的是LEFT JOIN,加上WHERE子句过滤掉NULL的部门ID实际上会导致那些没有分配部门的员工也被排除在外,违反了LEFT JOIN的初衷,即返回左表所有记录。因此,在大多数情况下,如果你想保留左表的所有记录,应避免在WHERE子句中过滤与右表相关的NULL值,而是应该在ON子句中完成所有必要的连接条件和限制。

正确的做法是在ON子句中处理所有连接条件,然后仅在确实需要进一步过滤整个结果集(而不影响左表完整性)时才使用WHERE子句。

具体案例二:
假设我们有两个表,一个是Orders(订单表),一个是Customers(客户表),我们想找出所有客户的订单信息,即使某些客户还没有下过任何订单。

Orders表:

  • OrderID
  • CustomerID
  • ProductName

Customers表:

  • CustomerID
  • CustomerName

使用ON的例子
如果我们想找到所有客户的订单,即使他们没有订单,我们会这样写:

SELECT Customers.CustomerName, Orders.ProductName
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

在这个查询中,ON子句确保了每个客户与他们的订单关联,即使没有订单的客户也会出现在结果集中,其ProductName为NULL。

使用WHERE的例子
如果我们进一步想在结果中只包括那些至少有一个订单的客户,我们可能会尝试这样写,但这是错误的做法,因为它违背了LEFT JOIN的初衷:

SELECT Customers.CustomerName, Orders.ProductName
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderID IS NOT NULL; -- 这里会过滤掉左表中无匹配项的记录

上述查询实际上会变成内连接的效果,因为WHERE子句排除了左表中没有匹配项(即订单为NULL)的记录。

正确的使用方式
如果我们的目的是在保留所有客户的同时,筛选出有订单的客户信息,我们应该这样写:

SELECT Customers.CustomerName, Orders.ProductName
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID AND Orders.OrderID IS NOT NULL;

在这个修正的查询中,我们在ON子句中同时指定了连接条件和筛选条件,这样既保证了左表的完整性,又对连接后的结果进行了有效的过滤,只保留了那些有订单的记录。

  • 5
    点赞
  • 10
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
MySQL中,LEFT JOIN是一种连接查询的方式,可以链接两个或多个表格。当使用LEFT JOIN时,将左边的表格的所有记录与右边表格的匹配记录连接起来,而不管右边表格的记录是否存在。 在LEFT JOIN中,将条件放在ON和WHERE子句中的区别如下: 1. ON子句:这是在连接两个表格时使用的条件。在ON子句中,我们可以指定要使用的列进行连接,例如:ON table1.column = table2.column。通过在ON子句中指定连接条件,可以确保将两个表格中匹配的记录连接起来。如果在ON子句中省略连接条件,则会返回左边表格的所有记录与右边表格的每条记录的组合。 2. WHERE子句:该子句用于在连接两个表格之后对结果进行筛选。在WHERE子句中,我们可以使用条件对连接后的结果进行筛选,例如:WHERE column1 = value。通过在WHERE子句中指定过滤条件,可以从连接后的结果中选择满足条件的记录。如果在WHERE子句中省略条件,则返回连接后的所有记录。 因此,ON子句用于连接两个表格,而WHERE子句用于筛选连接后的结果。放置条件的不同位置会对查询结果产生不同的影响。当连接条件放在ON子句中时,会在连接时对两个表格进行匹配。而当条件放在WHERE子句中时,会在连接后对匹配的结果进行过滤。 总结:ON子句用于连接表格,WHERE子句用于筛选连接后的结果。根据具体需求,我们可以选择将条件放置在ON子句或WHERE子句中来实现不同的查询目的。
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值