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.
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.
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
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
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
last_name column values from
Employee table and for non-matching rows, it will show NULL values.
dept_id = 4, there is no employee defined in
Employee table, so above query shows NULL value for
From the resultset, we can select any column from
Department table in the SELECT clause. As you can see, in the above query we selected
Employee table and
dept_name from the