Right Join (Right Outer Join) In Sql
In SQL database management systems, the RIGHT JOIN is a popular method, also known as RIGHT OUTER JOIN. It’s a popular join method in database tables, similar to the LEFT JOIN. However, the main difference between LEFT JOIN and RIGHT JOIN is that the RIGHT JOIN method returns and displays all table rows from the second table in the join process, along with the matching table rows from the first table. If no data record matches from the existing table, then NULL values will be displayed for the left table’s column rows in the final table result.
In other words, a RIGHT JOIN method ensures that all records from the right table are included in the output set, regardless of whether there is a matching row in the left table. If a match is found, the related table data from the left table is added. If no match is found, NULL values are added to the columns of the left table.
Syntax of RIGHT JOIN in SQL database.
SELECT columns
FROM tableP
RIGHT JOIN tableQ
ON tableP.column_name = tableQ.column_name;
Elements of RIGHT JOIN in SQL database.
- tableP – This is the first table in the left join process, where table data may be hidden.
- tableQ – This is the second table in the right join process; all table rows from this database table are returned.
- column_name – This is the table column used for matching between the two database tables in the right join process.
How RIGHT JOIN works in an SQL database.
The right join method in database tables always includes all table rows from its table in the output set.
If there are matching table rows in the left join table, the values will be added during the left table join.
If there are no matching table rows in the left join table, NULL values are added for the columns of the left table in the result set.
Example of a basic RIGHT JOIN in an SQL database.
Database tables for the right join method.
- Employe – The Employe table contains employe records.
- For example, employe_id, emp_name, department_id
- Department – The Departments table contains department records.
- For example, department_id, department_name
RIGHT JOIN Table Sample Data.
Employe table records.
employee_id emp_name department_id
101 Siddhi deora S101
201 Harry deora H204
301 Bhavshi deora B302
RIGHT JOIN Department table sample.
department_id department_name
K403 Marketing
M908 Development
N888 Design
Employe and Department table query.
SELECT employe.emp_name, department.department_name
FROM employe
RIGHT JOIN department
ON employe.department_id = department.department_id;
Explanation of Employe and Department table RIGHT JOIN.
- When this query runs, it retrieves the employe name from the Employees table and the department name from the Departments table.
- Here, the RIGHT JOIN table joining method ensures that all records from the Departments table are present in the result, even if there are no matching employees in the Employees table.
Result of Employe and Department RIGHT JOIN.
emp_name department_id
Siddhi deora Marketing
Harry deora Development
NULL Design
How RIGHT JOIN works.
Here, Siddhi deora and Harry deora have matching department records, so their employee names and department names are added and displayed.
Because there is no matching employee in the Design department, the name for that department is displayed as NULL.
Example of RIGHT JOIN with no matching data in the left table.
Let’s modify the data in the database tables so that there are no matching rows in the left table for some table records.
Updated Employe table.
Employee table output.
employe_id emp_name department_id
101 Siddhi Deora S101
201 Harry deora NULL
Department Employe Table.
department_id department_name
K403 Marketing
M908 Development
N888 Design
Employe and Department Query Statement.
SELECT employee.emp_name, department.department_name
FROM employe
RIGHT JOIN department
ON employe.department_id = department.department_id;
Result of Employe and Department Query.
emp_name department_name
Siddhi Deora Marketing
NULL Development
NULL Design
Explanation of the Employee and Department Query.
- Siddhi Deora belongs to the Marketing department, which is why the employee’s name and department name are displayed here.
- However, there are no matching employees in the employee table for the other departments, Development and Design, which is why NULL is displayed in the name column for these departments in the result.
How RIGHT JOIN works in SQL database management.
In SQL database tables, RIGHT JOIN returns all records from the departments table, even if there are no matching employee rows in the other table.
For the Development and Design departments, the result displays a NULL value in the employe name column because there are no related rows in the employee table.
Example of RIGHT JOIN with aliases in an SQL database.
Using aliases in SQL database tables can make database queries more detailed and easier to read, especially when working with multiple database tables or long table names.
Employee and Department table query with aliases.
SELECT e.emp_name, d.department_name
FROM employe AS e
RIGHT JOIN department AS d
ON e.department_id = d.department_id;
Explanation of the table query with aliases.
- Here, ‘e’ is an alias for the employee database table, and ‘d’ is an alias for the departments table.
- The result of this query will be the same as the example above, but the database table query is more compact and easier to read.
Example of a RIGHT JOIN with multiple tables in an SQL database table.
- Database users can use the RIGHT JOIN method to combine or group data from two or more database tables. Let’s create a third table to store salary information for employees in the employee table, which we’ll add as a pay table.
Employe, department, and pay table information.
- Employe – The Employee table contains employe information such as employe_id, emp_name, department_id, etc.
- Department – The Department table contains department information such as department_id, department_name.
- Pay – The Pay table contains pay information such as employe_id, salary.
Sample data from the EMPLOYE table.
Employe table query.
employe_id emp_name department_id
101 Siddhi deora S101
201 Harry deora H204
301 Bhavshi deora B302
307 Anil Jain NULL
Pay table query.
employe_id salary
101 30000
201 70000
Employe, department, and pay query.
SELECT e.emp_name, d.department_name, p.salary
FROM employe AS e
RIGHT JOIN department AS d
ON e.department_id = d.department_id
RIGHT JOIN pay AS p
ON e.employe_id = p.employe_id;
Explanation of the Employe, department, and pay query.
- In this example, the query retrieves the employee’s name, department_name, and pay information.
- The RIGHT JOIN method ensures that all records from the Department and Pay tables are included, even if there is no matching employee data.
Result of the Employe, department, and pay query.
emp_name department_name salary
Siddhi deora Marketing 50000
Harry deora Development 60000
NULL Sales NULL
Main Differences Between LEFT JOIN and RIGHT JOIN
| Table join Aspect | LEFT JOIN/LEFT OUTER JOIN features | RIGHT JOIN/RIGHT OUTER JOIN features |
| join returns values | Left join helps All table rows from the left table, and matching rows from the right table and display info. | Right join helps All table rows from the right table, and matching rows from the left table and display info. |
| Non-matching join rows | Left join Excludes table rows without matches in the right table database. | Right join Excludes table rows without matches in the left table database. |
| Join table Usage | Left join Use when user want all table records from the left table, even if there are no matches in the right table database at process time. | Right join Use when database user wants all table records from the right table, even if there are no matches in the left table database at the process time. |
| Position of the join Tables | Left join the first table is the left table database. | Right join uses the second table is the right table database. |
How RIGHT JOIN works here.
In the Employee, Department, and Pay table results, all records from the Departments and Pay tables are included. Siddhi deora and Harry deora have their matching salary and department table data, but the Sales department does not have any matching employee or pay table salary data. Therefore, NULL values are displayed for these table columns.
Conclusion of Right Join (Right Outer Join) in SQL.
- The RIGHT JOIN or RIGHT OUTER JOIN table join process method in SQL database management systems is used specifically when the database user wants to ensure that all rows from the right database table are present in the result, even if there is no exact matching row in the left database join table. If no proper match is found in the left table, the database displays NULL for the columns of the left table in the database table result.
- The RIGHT JOIN method is used when the database user needs to extract all table records from the right table, even if there is no proper matching record in the left database table. This process is the reverse of the LEFT JOIN method, which ensures that all rows from the left table in the existing database are returned and displayed.
