sql左联接返回 [英] sql left join returns

问题描述

我正在尝试对2个表进行左联接.我没有分组依据，而我所拥有的唯一条件是在第二张桌子上.但是，返回的行少于第一个表.左联接不是要从第一个表中带走所有数据吗? 这是我的SQL:

I am trying to run a left join on 2 tables. I do not have a group by and the only where condition i have is on the second table. But, the returned rows are less than the first table. isn't the left join suppose to bring all the data from the first table? Here is my SQL:

select * 
  from tbl_a A left join tbl_b B
     ON
       A.Cnumber=B.Cnumber
       and A.CDNUmber=B.CDNumber
       and abs(A.duration - B.Duration)<2
       and substr(A.text,1,3)||substr(A.text,5,8)||substr(A.text,9,2)=substr(B.text,1,8)
where B.fixed = 'b580'

表A中有140,000条记录，但返回的结果少于100,000条记录.有什么问题，我该如何解决?

There are 140,000 records in table A but the result returned is less than 100,000 records. What is the problem and how can I solve it?

推荐答案

只要在WHERE子句中添加了引用正确表的条件，并且该条件不容纳将在以下情况下生成的NULL联接失败，您已经(有效地)将其转换回了INNER JOIN.

As soon as you put a condition in the WHERE clause that references the right table and doesn't accommodate the NULLs that will be produced when the join is unsuccessful, you've transformed it (effectively) back into an INNER JOIN.

尝试:

where B.fixed = 'b580' OR B.fixed IS NULL

或将此条件添加到JOIN的ON子句中.

Or add this condition to the ON clause for the JOIN.

这篇关于sql左联接返回的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！