Search code examples
sqlleft-joinconditional-statementsrecords

sql left join returns

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'

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?

Solution

  • 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.

    Try:

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

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