Number of rows reduced after join

Question

I have a query that gives me 759 rows:

Select
    buildingID
   ,buildingAddress
   ,building_zip
From
   BuildingTable

However, when I join the table to get a column from another table, the number of rows is reduced to 707

Select
   buildingID
   ,buildingaddress
   ,building_zip
   ,b.surveyCost
From
   BuildingTable as A

   Inner Join SurveyTable as B
   On a.buildingAddress = b.address

What is the best way to test which rows I lost and why? and how do I prevent this from happening? I was thinking that maybe some of the buildings don't have survey costs and therefore it was only showing me the ones that have costs, but I see some null values there so that was not the issue, I think.

If you need extra information let me know. Thanks

Possible duplicate of [How to join (merge) data frames (inner, outer, left, right)?](http://stackoverflow.com/questions/1299871/how-to-join-merge-data-frames-inner-outer-left-right) — Daniel A. Thompson, Nov 15 '16 at 19:08
You have `NULL` values in the results because some records in `SurveyTable` have `NULL` in the `surveyCost` field. But you have "lost rows" because some buildings don't have surveys. Look at using `LEFT JOIN` instead of `INNER JOIN` and add `b.address` to your query to see the difference between `NoSurvery` and `SurveyWithNULLCost`. — MatBailie, Nov 15 '16 at 19:12

score 0 · Answer 1 · answered Nov 15 '16 at 19:09

You can find the row which rows are lost usig a left join between original query and resulting query where the join is nulll

Select
    buildingID
   ,buildingAddress
   ,building_zip
From
   BuildingTable t1
   left join ( 
    Select
       buildingID
       ,buildingaddress
       ,building_zip
       ,b.surveyCost
    From
       BuildingTable as A
       Inner Join SurveyTable as B
       On a.buildingAddress = b.address
       ) t2 on t1.buildingID  =  t2.buildingID
where  t2.buildingID is null

but why .. depend on you knowledge of the data

score 0 · Accepted Answer · answered Nov 15 '16 at 19:52

To find the rows you have lost, just replace the inner join with a left join and look for missing rows:

Select bt.*
From BuildingTable bt left join
     SurveyTable st
     on bt.buildingAddress = st.address
where st.address is null;

Note that rows can also be duplicated in both tables, so you might have more missing rows than you expect.

Number of rows reduced after join

2 Answers2