Confusing Explanation

This doesn’t have to do with code, but rather with an explanation that I’m having trouble following from this screen:

At the bottom of the explanation on this screen we see this, noting that an INNER JOIN would remove necessary data:

“This means that we may need to use different merge strategies with different data sets. Some of the data sets have a lot of missing DBN values. This makes a left join more appropriate, because we don’t want to lose too many rows when we merge. If we did an inner join, we would lose the data for many high schools”

But then we get the following explanation that for another df, using an INNER JOIN would preserve information that we are trying to keep:

“Some data sets have DBN values that are almost identical to those in sat_results. Those data sets also have information we need to keep. Most of our analysis would be impossible if a significant number of rows was missing from demographics, for example. Therefore, we’ll do an inner join to avoid missing data in these columns.”

Why would an INNER JOIN ignore information in one case, but not in the other?

Hi @Willyjgolden,

I think I can help explain,

For the first paragraph it is saying:

  1. Since one of those two data sets has a lot of missing DBN values, an Inner join would result in many rows being lost.
  2. A left join would preserve the data in the rows that have missing DBNs but would result in null values in the DBN column
  3. They are making the judgement that we would rather deal with null values than loose that many rows.

For the second paragraph, it is saying:

  1. For these two data sets, an inner join would mean that the resulting dataset would not have any null values in the DBN column which is desirable.
  2. Since the DBNs are nearly identical, the amount of rows that would be dropped by using inner join would be very small.
  3. In this case they are making the judgement that is is worth loosing those rows in order to have no null values.

So in the first paragraph it is referring to “lost data” (aka rows being dropped) if inner join was used; the second paragraph is referring to “avoiding missing data” (aka avoiding null values) if left join was used.

Basically its always a trade off between null values and lost data (unless the joining column matches perfectly for both datasets).

1 Like


I still do not get it. An inner join can never lead to more information than a left join, right?

An inner join will only keep rows for which a match can be found. Whereas a left join will keep those same rows plus all rows from the left df which did not find a match but with null values for the columns of the right df.

So the result of a left join has more information about the left df and the same information of the right df than compared to an inner join.

Or not? :slight_smile:


df = pd.DataFrame({'A': ['a', 'c', 'd', 'e', 'f'],
                   'C': [1, 2, 3, 4, 5]})
df2 = pd.DataFrame({'A': ['a', 'b', 'e'],
                   'C2': [11, 44, 55]})

check different joins:

pd.merge(df,df2, on='A', how='inner')
A C C2
0 a 1 11
1 e 4 55
pd.merge(df,df2, on='A', how='left')
A C C2
0 a 1 11.0
1 c 2 NaN
2 d 3 NaN
3 e 4 55.0
4 f 5 NaN

Hi @adam.kubalica,

thanks for replying.

I however do not see how the inner join in your experiment has led to more information than the left join.
The same information can be found in the left join and then some (row indices 1,2,4).