Outer join in Data Analysis Python - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is an outer join in database terms?

An outer join returns all rows from one or both tables, including rows that do not have matching values in the other table. Missing matches show as NULL.

beginner

What is the difference between left outer join and right outer join?

Left outer join returns all rows from the left table and matched rows from the right. Right outer join returns all rows from the right table and matched rows from the left.

beginner

In Python's pandas, which function is used to perform an outer join?

The pandas function merge() with the argument how='outer' performs an outer join.

intermediate

What does a full outer join return?

A full outer join returns all rows from both tables. Rows without matches in the other table have NULLs in the missing columns.

beginner

Why might you use an outer join instead of an inner join?

Use an outer join when you want to keep all data from one or both tables, even if there are no matching rows, to avoid losing information.

Which join returns all rows from the left table and matched rows from the right table?

A Left outer join

B Right outer join

C Inner join

D Cross join

Left outer join keeps all rows from the left table and matches from the right.

In pandas, how do you specify an outer join using the merge function?

A how='outer'

B how='left'

C how='inner'

D how='right'

Use how='outer' to perform an outer join in pandas merge.

What does a full outer join include that an inner join does not?

A Only rows from the left table

B Only matching rows

C All rows from both tables, including unmatched

D Only rows from the right table

Full outer join includes all rows from both tables, unmatched rows show NULLs.

If you want to keep all rows from the right table regardless of matches, which join do you use?

A Left outer join

B Right outer join

C Inner join

D Full outer join

Right outer join keeps all rows from the right table.

What value appears in columns from the other table when there is no match in an outer join?

A 0

B Empty string

C False

D NULL

Missing matches show as NULL in outer joins.

Explain what an outer join does and give an example of when you might use it.

Think about keeping all data from one or both tables even if no match exists.

Describe the difference between left outer join, right outer join, and full outer join.

Focus on which table's rows are fully kept in each join type.

Practice

1. What does an outer join do when combining two tables?

easy

A. Keeps all rows from both tables, filling missing matches with nulls

B. Keeps only rows that match in both tables

C. Keeps only rows from the first table

D. Keeps only rows from the second table

Solution

1. Step 1: Understand join types

   Inner join keeps only matching rows, outer join keeps all rows from both tables.

2. Step 2: Define outer join behavior

   Outer join fills missing matches with null (or NaN) values to keep all data.

3. Final Answer:

   Keeps all rows from both tables, filling missing matches with nulls -> Option A

4. Quick Check:

   Outer join = all rows kept with nulls for missing [OK]

Hint: Outer join keeps all rows from both tables with nulls for missing [OK]

Common Mistakes:

• Confusing outer join with inner join
• Thinking outer join keeps only one table's rows
• Assuming missing matches are dropped

2. Which of the following is the correct syntax to perform a full outer join using pandas?

easy

A. pd.merge(df1, df2, how='outer')

B. pd.join(df1, df2, type='full')

C. df1.outer_join(df2)

D. pd.merge(df1, df2, how='fulljoin')

Solution

1. Step 1: Recall pandas merge syntax

   Use pd.merge() with how='outer' for full outer join.

2. Step 2: Check options for correctness

   pd.merge(df1, df2, how='outer') matches correct syntax; others use invalid methods or parameters.

3. Final Answer:

   pd.merge(df1, df2, how='outer') -> Option A

4. Quick Check:

   pandas full outer join = merge with how='outer' [OK]

Hint: Use pd.merge with how='outer' for full outer join in pandas [OK]

Common Mistakes:

• Using pd.join instead of pd.merge for outer join
• Wrong parameter names like 'fulljoin' or 'type'
• Calling non-existent methods like outer_join

3. Given these two DataFrames:

df1 = pd.DataFrame({'id': [1, 2], 'val1': ['A', 'B']})
df2 = pd.DataFrame({'id': [2, 3], 'val2': ['X', 'Y']})

What is the result of pd.merge(df1, df2, on='id', how='outer')?

medium

A. [{'id':1, 'val1':'A'}, {'id':2, 'val1':'B'}]

B. [{'id':2, 'val1':'B', 'val2':'X'}]

C. [{'id':1, 'val1':'A', 'val2':NaN}, {'id':2, 'val1':'B', 'val2':'X'}, {'id':3, 'val1':NaN, 'val2':'Y'}]

D. [{'id':3, 'val2':'Y'}]

Solution

1. Step 1: Identify keys and join type

   Joining on 'id' with full outer join keeps all ids from both DataFrames.

2. Step 2: Combine rows with matching and missing data

   id=1 only in df1, id=2 in both, id=3 only in df2; missing values become NaN.

3. Final Answer:

   [{'id':1, 'val1':'A', 'val2':NaN}, {'id':2, 'val1':'B', 'val2':'X'}, {'id':3, 'val1':NaN, 'val2':'Y'}] -> Option C

4. Quick Check:

   Outer join keeps all ids with NaN for missing columns [OK]

Hint: Outer join keeps all keys, fills missing columns with NaN [OK]

Common Mistakes:

• Expecting only matching rows (inner join)
• Ignoring NaN for missing matches
• Confusing left or right join results

4. What is wrong with this code snippet for an outer join?

result = pd.merge(df1, df2, on='key', how='outside')

medium

A. df1 and df2 must have the same columns

B. The 'on' parameter should be a list, not a string

C. pd.merge cannot perform outer joins

D. The 'how' parameter value 'outside' is invalid

Solution

1. Step 1: Check 'how' parameter values

   Valid values are 'left', 'right', 'inner', 'outer'; 'outside' is invalid.

2. Step 2: Validate other parameters

   'on' can be string or list; pd.merge supports outer joins; columns can differ.

3. Final Answer:

   The 'how' parameter value 'outside' is invalid -> Option D

4. Quick Check:

   Invalid join type causes error [OK]

Hint: Check 'how' parameter spelling for join type [OK]

Common Mistakes:

• Using invalid join type strings
• Thinking 'on' must be a list always
• Assuming pd.merge can't do outer joins

5. You have two DataFrames:

df1 = pd.DataFrame({'id': [1, 2, 2], 'score': [10, 20, 20]})
df2 = pd.DataFrame({'id': [2, 3], 'grade': ['A', 'B']})

You want to perform a full outer join on 'id' and get all combinations of matching rows (including duplicates). Which code correctly achieves this?

hard

A. pd.merge(df1, df2, on='id', how='outer').drop_duplicates()

B. pd.merge(df1, df2, on='id', how='outer')

C. pd.merge(df1, df2, on='id', how='inner')

D. pd.merge(df1, df2, on='id', how='outer', indicator=True)

Solution

1. Step 1: Understand join type needed

   Full outer join keeps all rows from both DataFrames, including duplicates.

2. Step 2: Check code options for duplicates

   pd.merge(df1, df2, on='id', how='outer') keeps duplicates; pd.merge(df1, df2, on='id', how='outer').drop_duplicates() removes duplicates; pd.merge(df1, df2, on='id', how='inner') is inner join; pd.merge(df1, df2, on='id', how='outer', indicator=True) adds indicator but doesn't affect duplicates.

3. Final Answer:

   pd.merge(df1, df2, on='id', how='outer') -> Option B

4. Quick Check:

   Full outer join keeps duplicates unless dropped [OK]

Hint: Full outer join keeps duplicates; don't drop them unless needed [OK]

Common Mistakes:

• Using inner join instead of outer join
• Dropping duplicates unintentionally
• Confusing indicator parameter with join behavior