join functions (left_join, inner_join) in R Programming - Step-by-Step Execution

Concept Flow - join functions (left_join, inner_join)

Start with two tables

↓

Choose join type

↓

left_join

↓

Match keys in both tables

↓

left_join: keep all from left, add matches

↓

inner_join: keep only matches

↓

Resulting table

↓

End

Start with two tables, pick join type (left or inner), match keys, then produce a new table with rows combined accordingly.

Execution Sample

R Programming

library(dplyr)
left_join(df1, df2, by = "id")
inner_join(df1, df2, by = "id")

Joins two data frames df1 and df2 by the 'id' column using left and inner joins.

Execution Table

Step	Action	df1 rows	df2 rows	Join Type	Matching Keys	Result Rows	Result Description
1	Start with df1 and df2	3	2	-	-	-	df1 has 3 rows, df2 has 2 rows
2	Perform left_join by 'id'	3	2	left_join	id=1,2	3	All df1 rows kept; matched rows from df2 added; unmatched df2 rows ignored
3	Perform inner_join by 'id'	3	2	inner_join	id=1,2	2	Only rows with matching 'id' in both tables kept
4	End of joins	-	-	-	-	-	Joins complete, results ready

💡 Joins stop after matching keys and producing combined tables.

Variable Tracker

Variable	Start	After left_join	After inner_join
df1_rows	3	3	3
df2_rows	2	2	2
result_rows	-	3	2

Key Moments - 2 Insights

Why does left_join keep more rows than inner_join?

Because left_join keeps all rows from the left table (df1) even if there is no match in df2, as shown in execution_table row 2, while inner_join keeps only rows with matching keys in both tables (row 3).

What happens to rows in df2 that don't match any in df1 during left_join?

They are ignored and not included in the result, as left_join only keeps all rows from df1 and matches from df2, shown in execution_table row 2.

Visual Quiz - 3 Questions

Look at the execution_table, how many rows does the result have after left_join?

A 3

B 2

C 5

D 1

💡 Hint

Check the 'Result Rows' column in execution_table row 2 for left_join.

At which step does the join keep only matching rows from both tables?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the 'Join Type' and 'Result Description' columns in execution_table row 3.

If df2 had an extra row with id=3, how would left_join result rows change?

A Result rows would increase by 1

B Result rows stay the same

C Result rows decrease

D Result rows become zero

💡 Hint

Left_join keeps all rows from df1; extra df2 rows without matching keys do not add rows, see key_moments explanation.

Concept Snapshot

join functions (left_join, inner_join):
- left_join keeps all rows from left table + matched rows from right
- inner_join keeps only rows with matching keys in both tables
- Use 'by' to specify key columns
- Result rows differ: left_join >= inner_join
- Useful for combining related data with different inclusion rules

Full Transcript

This visual execution shows how left_join and inner_join work in R using dplyr. We start with two tables, df1 and df2, each with some rows. The left_join keeps all rows from df1 and adds matching rows from df2 based on the 'id' column. If df2 has no matching row, the left_join result still keeps the df1 row with missing values for df2 columns. The inner_join keeps only rows where both df1 and df2 have matching 'id' values, so the result has fewer rows. The execution table tracks these steps, showing the number of rows before and after each join. The variable tracker shows how the number of rows changes. Key moments clarify why left_join keeps more rows and what happens to unmatched rows in df2. The quiz tests understanding of these concepts by asking about result row counts and join behavior. This helps beginners see exactly how these joins combine data step-by-step.

Practice

1. What does the left_join function do in R's dplyr package?

easy

A. Removes all rows that have any missing values.

B. Keeps only rows that match in both tables.

C. Keeps all rows from the second table and adds matching rows from the first table.

D. Keeps all rows from the first table and adds matching rows from the second table.

Solution

1. Step 1: Understand the purpose of left_join

   left_join keeps all rows from the first (left) table regardless of matches.

2. Step 2: Understand how matching rows are added

   It adds columns from the second (right) table only where keys match, filling with NA if no match.

3. Final Answer:

   Keeps all rows from the first table and adds matching rows from the second table. -> Option D

4. Quick Check:

   left_join = all left rows + matching right rows [OK]

Hint: Remember: left_join keeps all from left, adds matches [OK]

Common Mistakes:

• Confusing left_join with inner_join
• Thinking left_join removes unmatched rows
• Mixing up which table is 'left' or 'right'

2. Which of the following is the correct syntax to perform an inner join of two data frames df1 and df2 by the column id in R?

easy

A. inner_join(df1, df2, on = "id")

B. innerjoin(df1, df2, on = "id")

C. inner_join(df1, df2, by = "id")

D. innerjoin(df1, df2, by = "id")

Solution

1. Step 1: Identify correct function name and parameters

   The function is inner_join from dplyr, and it uses by to specify join columns.

2. Step 2: Check parameter names and spelling

   Correct syntax is inner_join(df1, df2, by = "id"). Other options use wrong function names or parameter names.

3. Final Answer:

   inner_join(df1, df2, by = "id") -> Option C

4. Quick Check:

   inner_join syntax uses by = "column" [OK]

Hint: Use inner_join() with by = "column" for correct syntax [OK]

Common Mistakes:

• Using 'on' instead of 'by' for join column
• Misspelling function name as innerjoin
• Confusing parameter order

3. Given these two data frames:

df1 <- data.frame(id = c(1, 2, 3), value1 = c("A", "B", "C"))
df2 <- data.frame(id = c(2, 3, 4), value2 = c("X", "Y", "Z"))

What is the result of inner_join(df1, df2, by = "id")?

medium

A. Rows with ids 2 and 3 with columns from both data frames

B. Rows with ids 1, 2, 3, 4 with all columns

C. Rows with ids 1 and 4 only

D. Rows with ids 1, 2, 3 with only columns from df1

Solution

1. Step 1: Identify matching ids in both data frames

   df1 has ids 1, 2, 3; df2 has ids 2, 3, 4. Common ids are 2 and 3.

2. Step 2: Understand inner_join output

   inner_join keeps only rows with matching ids and combines columns from both data frames for those rows.

3. Final Answer:

   Rows with ids 2 and 3 with columns from both data frames -> Option A

4. Quick Check:

   inner_join = only matching rows [OK]

Hint: inner_join keeps only rows with matching keys [OK]

Common Mistakes:

• Including unmatched rows in inner_join result
• Confusing inner_join with left_join
• Ignoring columns from second data frame

4. You run this code:

left_join(df1, df2, by = "id")

but get an error: Error: object 'df2' not found. What is the likely cause?

medium

A. The data frame df2 was not created or loaded before the join.

B. The by argument is misspelled.

C. left_join cannot join on column named 'id'.

D. You need to use inner_join instead of left_join.

Solution

1. Step 1: Analyze the error message

   The error says df2 is not found, meaning R cannot find the object named df2.

2. Step 2: Identify cause of missing object

   This usually means df2 was not created, loaded, or is misspelled before calling left_join.

3. Final Answer:

   The data frame df2 was not created or loaded before the join. -> Option A

4. Quick Check:

   Missing data frame causes object not found error [OK]

Hint: Check if all data frames exist before joining [OK]

Common Mistakes:

• Assuming join syntax error causes object not found
• Ignoring typo in data frame names
• Thinking join functions require special packages loaded

5. You have two data frames:

students <- data.frame(student_id = c(1, 2, 3, 4), name = c("Anna", "Ben", "Cara", "Dan"))
scores <- data.frame(student_id = c(2, 3, 5), score = c(88, 92, 75))

You want a table that shows all students with their scores if available, and NA if no score exists. Which join function and code should you use?

hard

A. inner_join(students, scores, by = "student_id")

B. left_join(students, scores, by = "student_id")

C. right_join(students, scores, by = "student_id")

D. full_join(students, scores, by = "student_id")

Solution

1. Step 1: Understand the goal

   You want all students listed, even if they don't have a score, with scores added where available.

2. Step 2: Choose the correct join

   left_join keeps all rows from the first table (students) and adds matching scores, filling with NA if no match.

3. Final Answer:

   left_join(students, scores, by = "student_id") -> Option B

4. Quick Check:

   left_join keeps all left rows, adds matches, fills NA [OK]

Hint: Use left_join to keep all from first table with matches [OK]

Common Mistakes:

• Using inner_join which drops students without scores
• Using right_join which keeps all scores but drops some students
• Using full_join which adds extra rows not needed