MySQL RIGHT JOIN
MySQL RIGHT JOIN
The RIGHT JOIN in MySQL is used to return all rows from the RIGHT table and the matching rows from the LEFT table.
- If there is no match, MySQL returns NULL for columns from the left table.
What is RIGHT JOIN?
Returns all records from the right table
Returns matched records from the left table
Unmatched left-table columns → NULL
Also called RIGHT OUTER JOIN
Basic Syntax
table2is the RIGHT table (all rows included)
Simple Example
Table: employees
| emp_id | emp_name | dept_id |
|---|---|---|
| 1 | Amit | 10 |
| 2 | Ravi | 20 |
Table: departments
| dept_id | dept_name |
|---|---|
| 10 | HR |
| 20 | IT |
| 30 | Finance |
RIGHT JOIN Query
Result
| emp_name | dept_name |
|---|---|
| Amit | HR |
| Ravi | IT |
| NULL | Finance |
- Finance appears even without employees
NULLbecause no matching employee
RIGHT JOIN with WHERE Clause
Be careful: WHERE can convert OUTER JOIN into INNER JOIN.
- Shows departments with no employees
RIGHT JOIN vs LEFT JOIN (Interview Favorite)
LEFT JOIN returns all rows from the left table and matching rows from the right table. If no match exists, columns from the right table contain NULL. RIGHT JOIN returns all rows from the right table and matching rows from the left table. If no match exists, columns from the left table contain NULL. Both joins are logically similar and can be converted into each other by changing table order. In practice, LEFT JOIN is used more often than RIGHT JOIN.
Example
RIGHT JOIN with Multiple Tables in MySQL
RIGHT JOIN can be used to join more than two tables. Each join condition is applied step by step. All rows from the right-most table are always included. Rows without matches in left tables return NULL values.
Example
RIGHT JOIN vs INNER JOIN
| Feature | RIGHT JOIN | INNER JOIN |
|---|---|---|
| Unmatched rows | Included (right table) | Excluded |
| NULL values | Yes | No |
| Use case | Find missing relations | Only matches |
Common Mistakes
- Confusing LEFT and RIGHT tables
- Using WHERE instead of ON for join condition
- Forgetting NULL handling
- Overusing RIGHT JOIN (LEFT JOIN is more readable)
Interview Questions & MCQs (Very Important)
Q1. What does RIGHT JOIN return?
A) Only matching rows
B) All rows from left table
C) All rows from right table
D) Cartesian product
Answer: C
Q2. RIGHT JOIN is also known as?
A) INNER JOIN
B) RIGHT OUTER JOIN
C) FULL JOIN
D) CROSS JOIN
Answer: B
Q3. What happens if no match is found?
A) Row is removing
B) Error occurs
C) NULL values appear
D) Query fails
Answer: C
Q4. Which join is equivalent?
A) A LEFT JOIN B
B) B LEFT JOIN A
C) A INNER JOIN B
D) B RIGHT JOIN A
Answer: B
Q5. Which join is using more commonly?
A) RIGHT JOIN
B) LEFT JOIN
Answer: B
(LEFT JOIN is more readable and preferred)
Real-Life Use Cases
- Departments without employees
- Customers without orders
- Products never sold
- Data completeness checks
Summary
RIGHT JOIN returns all rows from right table
Unmatched left-table data becomes NULL
Equivalent to LEFT JOIN with swapped tables
Useful for finding missing relationships
Important for SQL exams & interviews
