SQL RIGHT JOIN

In SQL, the RIGHT JOIN (also called RIGHT OUTER JOIN) is used to combine rows from two tables based on a related column. It returns all records from the right table and only the matching records from the left table. If there is no match in the left table, the result will show NULL values for the left table’s columns.

• Returns all rows from the right table.
• Includes only matching rows from the left table.
• Non-matching rows from the left table appear as NULL

Syntax:

SELECT column_name(s)
FROM tableA 
RIGHT JOIN tableB 
ON tableA.column_name = tableB.column_name;

Examples of SQL RIGHT JOIN

In this example, we will consider two tables employee table containing details of the employees working in the particular department the and department table containing the details of the department

Employee Table:

Department Table:

Query:

SELECT emp_no , emp_name ,d_name, location 
FROM employee 
RIGHT  JOIN dept 
ON employee.dept_no = department.dept_no;

Output:

Explanation:

• The RIGHT JOIN ensures that all departments are listed.
• Since D4 (Testing) and D5 (Marketing) have no employees, the emp_no and emp_name columns show NULL.
• Departments with employees show proper matches.

Applications of SQL RIGHT JOIN

• Merging Data: Combines related data from different tables.
• Ensuring Completeness: Guarantees all records from the right table are included.
• Handling Missing Values: Identifies records without matches in the left table.
• Analyzing Relationships: Helps detect data gaps and dependencies across tables.

When Should You Use RIGHT JOIN?

1. Data Completeness: When you need to show all records from the right table (e.g., all departments).
2. Analyzing Missing Data: To detect records in the right table without matches in the left.
3. Reporting & Data Integrity: Ensures no records from the right table are skipped.
4. Handling Optional Data: When some left-side records may not exist but right-side ones must be shown.