user7146708 user7146708 - 17 days ago 6
SQL Question

Number of rows reduced after join

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

Answer

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.

Comments