PostgreSQL Right Join

In this section, we are going to understand the working of PostgreSQL Right join, which is used to return data from the Right table. We also learn how to use table-aliasing, WHERE clause, USING clause, and join multiple tables with the help of the PostgreSQL Right join clause.

What is the PostgreSQL Right Outer Join or Right Join clause?

The PostgreSQL Right JOIN or Right Outer Join is used to return all rows from the right table, and rows from the other table where the join condition is fulfilled defined in the ON condition. And if there are no corresponding records found from the Left table, it will return null values.

The Right Join can also be called as the Right Outer Join clause. Therefore, the Outer is the optional keyword, which is used in Right Join. In PostgreSQL, the Right join is parallel to the Left Join condition, but it will give the opposite result of the join tables.

The following Venn diagram displays the PostgreSQL Right join where we can easily understand that the Right Join returns all the data from the Right table and only a similar data from the left table:

PostgreSQL Right Join Syntax

The Right Join keyword is used with the SELECT command and must be written after the FROM Keyword.

SELECT columns    

FROM table1    

Right [OUTER] JOIN table2    

ON table1.column = table2.column;

In the above syntax, table1 is referring to the left table, and table2 is referring to the right table, which implies that the particular condition will return all records from table 2 and matching records from table1 according to the defined join condition.

We will follow the below steps to join the Left and Right tables with the help of the Right or Right Outer Join condition:

• Firstly, we will define the column list from both tables, where we want to select data in the SELECT condition.
• Then, we will define the Right table, which is table 2 in the FROM clause.
• And lastly, we will describe the Left table, which is table 1 in the Right JOIN condition, and write the join condition after the ON keyword.

NOTE: In the PostgreSQL Right Join, if the tables contain a similar column name, then USING and On clause produce the similar outputs.

Example of PostgreSQL Right join

Let us see an example to understand how the PostgreSQL Right join works:

To join two tables by using PostgreSQL Right Join

For this, we will use the Employee and department table, which we created in the PostgreSQL inner join section of the PostgreSQL tutorial.

Table1: Employee

To see the Employee table’s records, we will use the SELECT command as we can see in the following command:

Select * from Employee;  

Table2: department

To see the records from the department table, we will use the SELECT command as we can see in the following command:

Select * from department;  

Output

After executing the above command, we will get the data from the department table:

The below query is used to select records from both tables (Employee and department):

SELECT department.emp_id,dept_name,location,emp_fname, emp_lname  

FROM Employee    

Right JOIN department     

ON department.emp_id = Employee.emp_id    

order by emp_id;

Output

Once we implemented the above command, we will get the below result:

Working of PostgreSQL Right join

• In the above command, the Employee is the left table, and the department is the right table.
• The PostgreSQL Right Join condition selects the records from the right table which is the department table.
• Then it equates the values of the emp_id, dept_name, and location column from the department table and match the records with the emp_id column of each row from the Left table (Employee).
• If these records are similar, then the Right join creates a new row, which has the columns in the Select Clause and adds the particular row to the output.
• Or else, the Right join still creates a new row, which contains the columns from both the tables and adds the particular row in the outcome, and it fills the columns from the left table (Employee) with NULL.

Note: We can say that the RIGHT JOIN selects all rows from the right table even if they do not have similar rows from the left table.

Table-aliasing with PostgreSQL Right Join

Generally, the tables we want to join will have columns with a similar name like the emp_id column.

We will use table aliases to assign the joined tables short names to make the command more understandable.

In the below command, we will use the table aliasing, and it returns a similar outcome as above:

SELECT e.emp_id, emp_fname, emp_lname,location,dept_name     

FROM Employee e   

INNER JOIN department d   

ON e.emp_id = d.dept_id;

Output

Once we implemented the above command, we will get the below output:

PostgreSQL Right join with USING Clause

In this, we will see how the PostgreSQL Right join works with the USING clause.

For example, the above tables Employee and department contain a similar column, which is emp_id; Thus, in those cases, we are using the USING clause to get the values from the tables.

In the following command, we are using the USING clause in the Right join, which returns the values emp_id, emp_fname, emp_lname, dept_name, and location as both tables have a similar column: emp_id.

SELECT emp_id, emp_fname, emp_lname,dept_name,location  

FROM Employee    

RIGHT JOIN department     

USING(emp_id);

Output

We will get the following outcome after executing the above command:

PostgreSQL Right join using WHERE clause

If we want to identify the rows from the right table(department) that does not have any matching rows in the left table (Employee), we can use the WHERE condition with the Right join.

As we can see in below command, we are selecting the rows from both tables Employee and department where dept_name is equal to ‘RESEARCH’:

SELECT emp_id, emp_fname, emp_lname,dept_name,location  

FROM Employee    

RIGHT JOIN department     

USING(emp_id) WHERE dept_name ='RESEARCH';

Output

After successful execution of the above command, it will give the below output:

To join multiple tables using PostgreSQL Right JOIN

In the above section, we have two tables as Employee and department now, if we want to join more than two tables and get the records from that particular table. In that case, we will use the Right join.

For example, here we will take the Jobs table, which we created in the PostgreSQL Inner Join section of the PostgreSQL tutorial.

To see the Jobs table’s values, we will use the SELECT clause as follows:

Select * from Jobs;  

Table3: Jobs

We will join three tables such as Employee, department, and Jobs with the help of PostgreSQL Right Join as we can see in the following command:

SELECT emp_id, emp_fname, dept_name, job_description  

FROM Employee   

RIGHT JOIN department USING (emp_id)   

RIGHT JOIN Jobs    

ON department.emp_id = jobs.job_id  

ORDER BY emp_id;

Output

After successful execution of the above command, we will give the below result:

To get unmatched records by using of PostgreSQL Right JOIN clause

If we want to get the data from the table, which does not contain any similar row of data from other tables, so in those cases, we will use the PostgreSQL Right Join clause.

As we can see in the below example, the Right join clause is used to identify an employee whose Job_description is Null:

SELECT emp_id, emp_fname, emp_lname, job_description   

FROM Employee  

RIGHT JOIN Jobs  

ON Employee.emp_id=Jobs.job_id   

WHERE Job_description is NULL;

Output

Once we implemented the above command, we will get the following result:

Overview

In the PostgreSQL Right join section, we have learned the following topics:

• We used the PostgreSQL Right join condition with table-aliasing, USING clause, WHERE clause, and get unmatched data.
• We used the PostgreSQL Right join clause to select data from two tables or more than two tables.