JOIN query, SQL Server is dropping some rows of my

问题:

I have two tables customer_details and address_details. I want to display customer details with their corresponding address, so I was using a LEFT JOIN, but when I'm executing this query, SQL Server drops rows where street_no of customer_details table doesn't match with the street_no in address_detials table and displays only rows where `street_no' of customer_detials = street_no of address_details table. I need to display a complete customer_details table and in case if street_no doesn't matches it should display empty string or anything. Am I doing anything wrong in my SQL join?

Table customer_details:

case_id customer_name  mob_no       street_no
-------------------------------------------------
 1           John      242342343    4324234234234
 1           Rohan     343233333    43332
 1           Ankit     234234233    2342332423433
 1           Suresh    234234324    2342342342342
 1           Ranjeet   343424323    32233
 1           Ramu      234234333    2342342342343

Table address_details:

 s_no   streen_no       address        city    case_id
 ------------------------------------------------------
  1     4324234234234   Roni road      Delhi    1
  2     2342332423433   Natan street   Lucknow  1
  3     2342342342342   Koliko road    Herdoi   1   

SQL JOIN query:

select  
    a.*, b.address 
from 
    customer_details  a 
left join 
    address_details b on a.street_no = b.street_no 
where 
    b.case_id = 1 

回答1:

Now that it became clear that you used b.case_id=1, I will explain why it filters:

The LEFT JOIN itself returns some rows that contain all NULL values for table b in the result set, which is what you want and expect.

But by using WHERE b.case_id=1, the rows containing NULL values for table b are filtered out because none of them matches the condition (all those rows have b.case_id=NULL so they don't match).

It might work to instead use WHERE a.case_id=1, but we don't know if a.case_id and b.case_id are always the same value for matching rows (they might not be; and if they are always the same, then we just identified a potential redundancy).

There are two ways to fix this for sure.

(1) Move b.case_id = 1 into the left join condition:

left join address_details b on a.street_no = b.street_no and b.case_id = 1

(2) Keep b.case_id = 1 in the WHERE but also allow for NULLED-out b values:

left join address_details b on a.street_no = b.street_no 
where b.case_id = 1
or    b.street_no IS NULL

Personally I'd go for (1) because that is the most clear way to express that you want to filter b on two conditions, without affecting the rows of a that are being returned.

回答2:

I do think that Wilhelm Poggenpohl answer is kind of right. You just need to change the last join condition a.case_id=1 to b.case_id=1

select  a.* , b.address 
from customer_details  a 
  left join address_details b on a.street_no=b.street_no 
  and b.case_id=1

This query will show every row from customer_details and the corresponding adress if there is a match of street_no and the adress meets the condition case_id=1.

回答3:

This is because of the where clause. Try this:

select  a.* , b.address 
from customer_details  a 
  left join address_details b on a.street_no=b.street_no 
and a.case_id=1