In SQL, a RIGHT JOIN (or RIGHT OUTER JOIN) is used to combine rows from two tables based on a related column between them. It returns all rows from the right table and the matched rows from the left table. If there are no matches in the left table, the result will still include all rows from the right table, with NULL values in columns from the left table.
Here's the general syntax for a RIGHT JOIN:
SELECT columns
FROM left_table
RIGHT JOIN right_table
ON left_table.common_column = right_table.common_column;
Example
Let's say we have two tables:
1. employees (left table)
id | name
-------------
1 | Alice
2 | Bob
3 | Charlie
2. departments (right table)
id | department_name | employee_id
------------------------------------
1 | Marketing | 1
2 | Sales | 2
3 | IT | NULL
If you want to list all departments along with the employees (if they exist), you can use a RIGHT JOIN like this:
SELECT employees.id, employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.id = departments.employee_id;
Result
id | name | department_name
-------------------------------------
1 | Alice | Marketing
2 | Bob | Sales
NULL| NULL | IT
Explanation
- In this example:
- The department "Marketing" matches with Alice (employee ID 1).
- The department "Sales" matches with Bob (employee ID 2).
- The department "IT" does not match with any employee, so it shows NULL for the employee ID and name but still appears in the result due to the RIGHT JOIN.
This way, the RIGHT JOIN ensures that all rows from the departments table are included in the result set regardless of whether there's a corresponding row in the employees table.