mysql外连接后where失效,MySQL使用where子句保留外连接 - 返回不匹配的行

I have two tables: pq and pe. I am trying to LEFT OUTER JOIN left table (pq) on right table (pe).

pq has primary key column id

pe has two-column primary key, so it may have many pqid's or none

pe.uid column has to be used to extract only relevant data (WHERE pe.uid = "12345")

pe.data should be joined to every pq.id row

Here is how tables look:

pq:

id | data

1 | "abc"

2 | "efg"

pe:

pqid | uid | data

2 | 54321 | "uvw"

2 | 12345 | "xyz"

I can use the following query to match first 2 rows of pq.id to pe.pqid

SELECT pq.id, pq.data, pe.data FROM pq

LEFT OUTER JOIN pe ON pq.id = pe.pqid

ORDER BY pq.id LIMIT 2

I get:

pq.id | pq.data | pe.data

1 | "abc" |

2 | "efg" | "uvw"

But if I use the WHERE statement like this:

SELECT pq.id, pq.data, pe.data FROM pq

LEFT OUTER JOIN pe ON pq.id = pe.pqid

WHERE pe.uid='12345'

ORDER BY pq.id LIMIT 2

I only get one row with matching pe.pqid AND pe.uid:

pq.id | pq.data | pe.data

2 | "efg" | "xyz"

So with the WHERE clause I get the right pe.data, but I don't get pq rows that have no pq.id matching pe.pqid

I need to get this:

pq.id | pq.data | pe.data

1 | "abc" |

2 | "efg" | "xyz"

解决方案

Yes. The where clause is turning the left outer join into an inner join.

Why? The value of pe.pqid is NULL (as is pe.uid) when there is no match. So the comparison in the where clause fails (almost all comparisons to NULL return NULL which is considered false).

The solution is to move the comparison to the on clause:

SELECT pq.id, pq.data, pe.data

FROM pq LEFT OUTER JOIN

pe

ON pq.id = pe.pqid and

pe.uid='12345'

ORDER BY pq.id LIMIT 2

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
### 回答1: 是的,在 MySQL 中,如果在 WHERE 子句使用函数处理列的值,则可能会导致索引失效。这是因为当使用函数处理列的值时,MySQL 将无法使用索引进快速查找。 例如,如果在 WHERE 子句使用 LOWER() 函数来搜索小写字符串,则 MySQL 将无法使用索引进查找,因为索引中的值是大写的。 因此,在使用 MySQL 时,应尽量避免在 WHERE 子句使用函数处理列的值,以便使用索引进优化查询。 ### 回答2: 在MySQL中,使用函数对字段进操作会导致索引失效是有可能的。这是因为索引是建立在原始字段上的,当我们在查询中对字段使用函数时,MySQL必须对每个记录进计算,以获取函数的结果,而不是直接使用索引。这样就会导致MySQL无法有效地利用索引来加速查询的过程,从而降低了查询性能。 举个例子,如果我们有一个名为"birthday"的字段,并在该字段上建立了索引,将其定义为DATE类型。如果我们在查询中使用了函数DATE()来提取birthday字段的日期,就会导致索引失效MySQL将无法直接使用索引来优化查询,而需要对每条记录进日期计算。 那么如何避免这种情况呢?一种方法是尽量避免对字段使用函数,如果可能的话,应该在程序中对数据进预处理,将计算移至程序中进,而不是在查询中使用函数。另外,可以考虑对需要用到的字段建立更为常用的索引,以提高查询性能。 总结来说,使用函数可能会导致MySQL索引失效,因为函数会对每个记录进计算,而无法直接使用索引来加速查询。为了避免这种情况,应尽量避免使用函数,如果需要使用函数,可以考虑在程序中进预处理,或者对需要的字段建立更合适的索引。 ### 回答3: 在MySQL中,使用函数可能会导致索引失效的情况是存在的。 当我们在查询中使用函数来对某列进处理时,例如使用函数对列进计算、转换、截取等操作,MySQL优化器无法利用已经创建的索引来加速查询。这是因为函数会修改列的值或者将其转化为不同的数据类型,导致索引无法正确地匹配或者无法利用索引的有序性。 例如,我们有一个名为age的列,并且对其创建了一个B-tree索引。如果我们在查询中使用函数对age列进计算,如SELECT * FROM table WHERE SQRT(age) > 10,那么MySQL无法对SQRT(age)这个表达式进优化,无法利用age的索引,会导致索引失效。 另外,对于字符串类型的列,使用函数进截取或者转换操作也会导致索引失效。因为函数对字符串的处理会导致索引无法正确地进匹配,从而无法利用索引加速查询。 为了避免这种情况,可以考虑在实际存储数据时对需要进函数处理的列进预处理,将处理后的结果存储在新列中,并对新列创建索引。这样在查询时就可以直接使用新列的索引,避免函数导致的索引失效。 综上所述,MySQL使用函数可能导致索引失效,这时需要注意对需要处理的列进预处理或者重新设计查询语句,以避免影响查询性能。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值