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
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值