RIGHT JOIN execution behavior in SQL - Step-by-Step Execution

Concept Flow - RIGHT JOIN execution behavior

Start with RIGHT table rows

↓

Match rows in LEFT table

↓

Combine rows

↓

Output result

RIGHT JOIN returns all rows from the right table and matched rows from the left table; unmatched right rows show NULLs.

Execution Sample

SQL

SELECT A.id, A.name, B.score
FROM A
RIGHT JOIN B ON A.id = B.a_id;

This query returns all rows from table B and matching rows from table A, filling NULLs where no match exists.

Execution Table

Step	Current B row	Matching A row?	Action	Output row
1	B.a_id=1, score=90	A.id=1, name='Alice'	Match found, combine rows	(1, 'Alice', 90)
2	B.a_id=2, score=85	A.id=2, name='Bob'	Match found, combine rows	(2, 'Bob', 85)
3	B.a_id=4, score=70	No matching A.id=4	No match, fill A columns with NULL	(NULL, NULL, 70)
4	No more rows in B	End	Stop execution	Query complete

💡 All rows from B processed; unmatched B rows included with NULLs for A columns.

Variable Tracker

Variable	Start	After 1	After 2	After 3	Final
Current B row	None	a_id=1, score=90	a_id=2, score=85	a_id=4, score=70	None
Matching A row	None	id=1, name='Alice'	id=2, name='Bob'	No match	None
Output rows count	0	1	2	3	3

Key Moments - 2 Insights

Why do we get NULLs in the output for some columns?

When a row in the right table has no matching row in the left table, LEFT columns are filled with NULLs, as shown in step 3 of the execution_table.

Does RIGHT JOIN include all rows from the left table?

No, RIGHT JOIN includes all rows from the right table. Left table rows without matching right rows are excluded, as seen in the execution_table where only B rows drive output.

Visual Quiz - 3 Questions

Look at the execution table, what is the output row at step 2?

A (2, NULL, 85)

B (NULL, NULL, 85)

C (2, 'Bob', 85)

D (NULL, 'Bob', 85)

💡 Hint

Check the 'Output row' column for step 2 in the execution_table.

At which step does the RIGHT JOIN output a row with NULLs for the left table columns?

A Step 3

B Step 1

C Step 2

D Step 4

💡 Hint

Look for 'No match, fill A columns with NULL' in the 'Action' column.

If table B had no rows, what would the output be?

A All rows from A with NULLs for B columns

B No rows returned

C All rows from B with NULLs for A columns

D Error due to missing data

💡 Hint

RIGHT JOIN returns all rows from the right table; if right table is empty, no rows output.

Concept Snapshot

RIGHT JOIN syntax:
SELECT columns
FROM left_table
RIGHT JOIN right_table ON condition;

Behavior:
- Returns all rows from right_table
- Matches rows from left_table
- NULLs for left_table columns if no match

Use when you want all right_table data regardless of left_table matches.

Full Transcript

RIGHT JOIN returns all rows from the right table and matching rows from the left table. For each row in the right table, the database looks for matching rows in the left table based on the join condition. If a match is found, it combines the rows. If no match is found, it includes the right table row with NULLs in the left table columns. This ensures all right table rows appear in the result. The execution table shows step-by-step how each right table row is processed, matched, and output. NULLs appear when no left table match exists. If the right table is empty, no rows are returned. This join is useful when you want to keep all data from the right table and add matching left table data if available.

Practice

1. What does a RIGHT JOIN do in SQL?

easy

A. Returns all rows from the right table and matching rows from the left table.

B. Returns all rows from the left table and matching rows from the right table.

C. Returns only rows that have matching values in both tables.

D. Returns all rows from both tables, matching where possible.

Solution

1. Step 1: Understand RIGHT JOIN behavior

   A RIGHT JOIN returns all rows from the right table regardless of matches in the left table.

2. Step 2: Identify matching rows from the left table

   It includes matching rows from the left table and fills NULL where no match exists.

3. Final Answer:

   Returns all rows from the right table and matching rows from the left table. -> Option A

4. Quick Check:

   RIGHT JOIN = all right table rows + matched left rows [OK]

Hint: Remember: RIGHT JOIN keeps all right table rows [OK]

Common Mistakes:

• Confusing RIGHT JOIN with LEFT JOIN
• Thinking it returns only matching rows
• Assuming it returns all rows from left table

2. Which of the following is the correct syntax for a RIGHT JOIN between tables Employees and Departments on DepartmentID?

easy

A. SELECT * FROM Employees RIGHT Departments JOIN ON Employees.DepartmentID = Departments.DepartmentID;

B. SELECT * FROM Employees JOIN Departments RIGHT ON Employees.DepartmentID = Departments.DepartmentID;

C. SELECT * FROM Employees RIGHT JOIN Departments WHERE Employees.DepartmentID = Departments.DepartmentID;

D. SELECT * FROM Employees RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Solution

1. Step 1: Identify correct JOIN syntax

   The correct syntax is: FROM left_table RIGHT JOIN right_table ON condition.

2. Step 2: Match the syntax with given options

   SELECT * FROM Employees RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID; correctly uses RIGHT JOIN with ON clause and proper table order.

3. Final Answer:

   SELECT * FROM Employees RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID; -> Option D

4. Quick Check:

   RIGHT JOIN syntax = FROM left RIGHT JOIN right ON condition [OK]

Hint: RIGHT JOIN syntax: FROM left RIGHT JOIN right ON condition [OK]

Common Mistakes:

• Placing RIGHT keyword after JOIN
• Using WHERE instead of ON for join condition
• Incorrect table order in JOIN

3. Given tables:
Employees:
ID | Name | DeptID
1 | Alice | 10
2 | Bob | 20
3 | Carol | NULL

Departments:
DeptID | DeptName
10 | Sales
20 | Marketing
30 | HR

What is the result of:
SELECT Employees.Name, Departments.DeptName FROM Employees RIGHT JOIN Departments ON Employees.DeptID = Departments.DeptID;?

medium

A. [('Alice', 'Sales'), ('Bob', 'Marketing'), (NULL, 'HR')]

B. [('Alice', 'Sales'), ('Bob', 'Marketing')]

C. [('Alice', 'Sales'), ('Bob', 'Marketing'), ('Carol', NULL)]

D. [('Alice', 'Sales'), ('Bob', 'Marketing'), ('Carol', 'HR')]

Solution

1. Step 1: Identify RIGHT JOIN effect on rows

   RIGHT JOIN keeps all Departments rows (right table), matching Employees rows or NULL if no match.

2. Step 2: Match Employees to Departments by DeptID

   DeptID 10 matches Alice, 20 matches Bob, 30 has no employee so NULL for Name.

3. Final Answer:

   [('Alice', 'Sales'), ('Bob', 'Marketing'), (NULL, 'HR')] -> Option A

4. Quick Check:

   RIGHT JOIN keeps all right rows, unmatched left columns NULL [OK]

Hint: RIGHT JOIN keeps all right rows, unmatched left columns NULL [OK]

Common Mistakes:

• Ignoring unmatched right table rows
• Assuming unmatched left rows appear
• Mixing up NULL placement

4. Consider this SQL query:
SELECT * FROM Orders RIGHT JOIN Customers ON Orders.CustomerID = Customers.ID;
It returns fewer rows than expected. What is a likely cause?

medium

A. RIGHT JOIN always returns fewer rows than LEFT JOIN.

B. Orders table is empty, so no rows are returned.

C. The JOIN condition uses wrong column names causing no matches.

D. RIGHT JOIN syntax requires WHERE instead of ON clause.

Solution

1. Step 1: Analyze JOIN condition correctness

   If column names in ON clause are wrong, no matches occur, reducing rows.

2. Step 2: Understand RIGHT JOIN behavior with no matches

   RIGHT JOIN still returns all right table rows, but if condition is wrong, matches fail and left columns are NULL.

3. Final Answer:

   The JOIN condition uses wrong column names causing no matches. -> Option C

4. Quick Check:

   Wrong ON columns cause fewer matches [OK]

Hint: Check ON clause column names carefully [OK]

Common Mistakes:

• Assuming RIGHT JOIN returns fewer rows by default
• Confusing ON and WHERE clauses
• Ignoring empty tables impact

5. You have two tables:
Products:
ProductID | Name
1 | Pen
2 | Pencil
3 | Eraser

Sales:
SaleID | ProductID | Quantity
101 | 1 | 10
102 | 2 | 5

You want a report showing all products and their sold quantities, including products with no sales (show quantity as 0). Which query correctly uses RIGHT JOIN and handles missing sales?

hard

A. SELECT Products.Name, Sales.Quantity FROM Products RIGHT JOIN Sales ON Products.ProductID = Sales.ProductID;

B. SELECT Products.Name, COALESCE(Sales.Quantity, 0) AS Quantity FROM Sales RIGHT JOIN Products ON Sales.ProductID = Products.ProductID;

C. SELECT Products.Name, COALESCE(Sales.Quantity, 0) AS Quantity FROM Products LEFT JOIN Sales ON Products.ProductID = Sales.ProductID;

D. SELECT Products.Name, Sales.Quantity FROM Sales LEFT JOIN Products ON Sales.ProductID = Products.ProductID;

Solution

1. Step 1: Identify which table is right and which is left

   Products is the right table to keep all products; Sales is left table.

2. Step 2: Use RIGHT JOIN from Sales to Products and handle NULLs

   RIGHT JOIN keeps all Products rows; COALESCE replaces NULL sales quantity with 0.

3. Final Answer:

   SELECT Products.Name, COALESCE(Sales.Quantity, 0) AS Quantity FROM Sales RIGHT JOIN Products ON Sales.ProductID = Products.ProductID; -> Option B

4. Quick Check:

   RIGHT JOIN keeps all right rows; COALESCE handles NULLs [OK]

Hint: Use COALESCE to replace NULLs after RIGHT JOIN [OK]

Common Mistakes:

• Using LEFT JOIN instead of RIGHT JOIN
• Not handling NULL sales quantities
• Swapping table order in JOIN