SQL RIGHT JOIN Keyword

Welcome to The Coding College, where you’ll find in-depth tutorials on SQL and programming concepts. In this article, we’ll dive into the SQL RIGHT JOIN keyword, helping you understand its purpose, syntax, and use cases with practical examples.

What Is SQL RIGHT JOIN?

The SQL RIGHT JOIN keyword retrieves all records from the right table and the matching records from the left table. If there is no match in the left table, the result will contain NULL for the columns from the left table.

Syntax of RIGHT JOIN

SELECT column_names  
FROM table1  
RIGHT JOIN table2  
ON table1.column_name = table2.column_name;
  • table1: The left table.
  • table2: The right table (returns all rows).
  • ON: Specifies the condition for matching rows.

Example Tables

Table: employees

employee_idnamedepartment_idsalary
1Alice10160000
2Bob10270000
3Charlie10355000

Table: departments

department_iddepartment_name
101HR
102IT
103Finance
104Marketing

RIGHT JOIN Example

Query

SELECT employees.name, employees.salary, departments.department_name  
FROM employees  
RIGHT JOIN departments  
ON employees.department_id = departments.department_id;

Result

namesalarydepartment_name
Alice60000HR
Bob70000IT
Charlie55000Finance
NULLNULLMarketing

Explanation

  1. The query retrieves all rows from the departments table.
  2. Only matching rows from the employees table are included.
  3. Since no employee belongs to the “Marketing” department, the result contains NULL for the columns from employees.

Practical Use Cases for RIGHT JOIN

  1. Department Analysis:
    • List all departments, even those without employees.
  2. Order Tracking:
    • Retrieve all orders, even those without assigned customers.
  3. Data Validation:
    • Ensure completeness by checking for missing relationships.

RIGHT JOIN vs. LEFT JOIN

FeatureLEFT JOINRIGHT JOIN
Main FocusAll rows from the left table.All rows from the right table.
Non-Matching RowsNULL values for unmatched right table.NULL values for unmatched left table.
Common Use CaseWhen the left table has priority.When the right table has priority.

RIGHT JOIN with Multiple Tables

Example

Suppose you have another table, projects:

project_idproject_namedepartment_id
1Project A101
2Project B104

Query

SELECT departments.department_name, projects.project_name  
FROM projects  
RIGHT JOIN departments  
ON projects.department_id = departments.department_id;

Result

department_nameproject_name
HRProject A
ITNULL
FinanceNULL
MarketingProject B

Best Practices for RIGHT JOIN

  • Use Aliases for Readability: 
SELECT d.department_name, e.name  
FROM employees e  
RIGHT JOIN departments d  
ON e.department_id = d.department_id;
  • Check for NULL Values:
    Use COALESCE to replace NULL with a default value. 
SELECT departments.department_name, COALESCE(employees.name, 'No Employee') AS employee_name  
FROM employees  
RIGHT JOIN departments  
ON employees.department_id = departments.department_id;
  • Combine with Filtering:
    Use a WHERE clause to refine results. 
SELECT employees.name, departments.department_name  
FROM employees  
RIGHT JOIN departments  
ON employees.department_id = departments.department_id  
WHERE departments.department_name = 'Marketing';

Real-World Example

Imagine a database for an e-commerce platform. The products table lists all products, while the orders table logs purchases. To identify products that haven’t been ordered, use a RIGHT JOIN:

SELECT orders.order_id, products.product_name  
FROM orders  
RIGHT JOIN products  
ON orders.product_id = products.product_id;

Conclusion

The SQL RIGHT JOIN keyword is essential for queries where the right table takes precedence. By understanding its use, you can extract valuable insights and maintain data completeness.

Explore more SQL tutorials on The Coding College and elevate your programming skills.

Leave a Comment