The Project | About Us | Contribute | Donations | License

HOME

Introduction

In SQL, the RIGHT JOIN (or RIGHT OUTER JOIN) is used to combine rows from two or more tables based on a related column between them. The RIGHT JOIN returns all rows from the right table and the matched rows from the left table. If there is no match, the result is NULL on the side of the left table.

Syntax

The basic syntax for a RIGHT JOIN is as follows:

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

columns: The columns you want to retrieve.
table1: The left table.
table2: The right table.
common_column: The column that is common between the two tables.

Example

Let's consider two tables, Employees and Departments.

Employees Table

EmployeeID	Name	DepartmentID
1	John Smith	1
2	Jane Doe	2
3	Emily Davis	3

Departments Table

DepartmentID	DepartmentName
1	HR
2	IT
4	Marketing

Query

We want to retrieve all departments and their corresponding employees, including departments that do not have any employees.

SELECT Employees.EmployeeID, Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

Result

EmployeeID	Name	DepartmentName
1	John Smith	HR
2	Jane Doe	IT
NULL	NULL	Marketing

In this result:

The HR and IT departments have corresponding employees.
The Marketing department does not have any employees, so the EmployeeID and Name columns are NULL.

Practical Exercise

Task

Given the following tables, write a RIGHT JOIN query to list all products and their corresponding orders, including products that have not been ordered.

Products Table

ProductID	ProductName
1	Laptop
2	Mouse
3	Keyboard

Orders Table

OrderID	ProductID	Quantity
101	1	2
102	2	5

Solution

SELECT Products.ProductID, Products.ProductName, Orders.OrderID, Orders.Quantity
FROM Orders
RIGHT JOIN Products
ON Orders.ProductID = Products.ProductID;

Expected Result

ProductID	ProductName	OrderID	Quantity
1	Laptop	101	2
2	Mouse	102	5
3	Keyboard	NULL	NULL

Common Mistakes

Confusing RIGHT JOIN with LEFT JOIN: Remember that RIGHT JOIN returns all rows from the right table and the matched rows from the left table.
Incorrect ON clause: Ensure that the columns specified in the ON clause are correctly matched between the two tables.
Not handling NULL values: When using RIGHT JOIN, be prepared to handle NULL values in the result set.

Conclusion

The RIGHT JOIN is a powerful tool for combining rows from two tables, ensuring that all rows from the right table are included in the result set, even if there are no matching rows in the left table. Understanding how to use RIGHT JOIN effectively can help you retrieve comprehensive data sets and gain deeper insights from your database.

RIGHT JOIN

Introduction

Syntax

Example

Employees Table

Departments Table

Query

Result

Practical Exercise

Task

Products Table

Orders Table

Solution

Expected Result

Common Mistakes

Conclusion

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL Queries

Module 3: Working with Multiple Tables

Module 4: Advanced Data Filtering

Module 5: Data Manipulation

Module 6: Advanced SQL Functions

Module 7: Subqueries and Nested Queries

Module 8: Indexes and Performance Optimization

Module 9: Transactions and Concurrency

Module 10: Advanced Topics

Module 11: SQL in Practice

Module 12: Final Project