PostgreSQL: Right Outer Join

The RIGHT JOIN is a type of join where it returns all records from the right table and matching records from the left table.

Here the right table is the table that comes to the right side of the "RIGHT JOIN" phrase in the query, and the left table refers to a table that comes at the left side or after the "RIGHT JOIN" phrase. It returns NULL for all non-matching records from the left table.

The RIGHT JOIN is the reverse of LEFT JOIN. In some databases, it is called RIGHT OUTER JOIN.

Here is a diagram that represents RIGHT JOIN.

Syntax 

SELECT <table_1.column_name(s)>, <table_2.column_name(s)>
FROM <table_1>
RIGHT [OUTER] JOIN <table_2>
ON <table_1.column_name> = <table_2.column_name>;

        

As per the above syntax, we have the table_2 as a right table and the table_1 as a left table and they have some matching columns between them.

For the demo purpose, we will use the following Employee and Department tables in all examples.

Notice Department table is parent table with dept_id as a primary key. Employee table is child table that has dept_id as foreign key referencing dept_id column of Department table. Now let's join both the first table (Employee) with a second table (Department) by matching values of dept_id column.

Employee table has some employee, for e.g. emp_id = 5 who does not have dept_id assigned to it and for the Department with dept_id = 4, there is no employee. Let's use RIGHT JOIN to select data from the department that may or may not have matching data in the Employee table.

Example:

SELECT emp.emp_id, emp.first_name, emp.last_name, dept.dept_id, dept.dept_name 
FROM Employee emp RIGHT JOIN department dept
ON emp.dept_id = dept.dept_id;

Here, the RIGHT JOIN selects all rows of right table that is Department. It may or may not have matching data in left table, that is Employee. For matching data, it will show emp_id, first_name and last_name column values from Employee table and for non-matching rows, it will show NULL values.

For dept_id = 4, there is no employee defined in Employee table, so above query shows NULL value for emp_id, first_name and last_name columns.

From the resultset, we can select any column from Employee or Department table in the SELECT clause. As you can see, in the above query we selected emp_id, first_name and last_name from Employee table and dept_id and dept_name from the Department table.