PostgreSQL Right JoinIn 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 SyntaxThe Right Join keyword is used with the SELECT command and must be written after the FROM Keyword. 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:
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 joinLet us see an example to understand how the PostgreSQL Right join works: To join two tables by using PostgreSQL Right JoinFor 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: Table2: department To see the records from the department table, we will use the SELECT command as we can see in the following command: 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): Output Once we implemented the above command, we will get the below result: Working of PostgreSQL Right join
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 JoinGenerally, 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: Output Once we implemented the above command, we will get the below output: PostgreSQL Right join with USING ClauseIn 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. Output We will get the following outcome after executing the above command: PostgreSQL Right join using WHERE clauseIf 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': Output After successful execution of the above command, it will give the below output: To join multiple tables using PostgreSQL Right JOINIn 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: 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: Output After successful execution of the above command, we will give the below result: To get unmatched records by using of PostgreSQL Right JOIN clauseIf 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: Output Once we implemented the above command, we will get the following result: OverviewIn the PostgreSQL Right join section, we have learned the following topics:
Next TopicPostgreSQL Full Join |