PostgreSQL RIGHT JOIN or RIGHT OUTER JOIN

How PostgreSQL Right Join or Right Outer Join works?

The PostgreSQL RIGHT JOIN joins two tables and fetches rows based on a condition, which is matching in both the tables and the unmatched rows will also be available from the table written after the JOIN clause.

So, in case of RIGHT JOIN or RIGHT OUTER JOIN, PostgreSQL -

1. takes all selected values from the right table

2. combines them with the column names ( specified in the condition ) from the left table

3. retrieve the matching rows from both the associated tables.

4. sets the value of every column from the left table to NULL which is unmatched with the right table.

Syntax:

Select *
FROM table1
RIGHT [ OUTER ] JOIN table2
ON table1.column_name=table2.column_name;

Pictorial Presentation of PostgreSQL Right Join or Right Outer Join

PostgreSQL RIGHT join fetches a complete set of records from the right, with the matching records (depending on the availability) in left. The result is NULL in the left side when no matching will take place.

Sample table: Customer

Sample table: Item

Sample table: Invoice

Example:

Code:

SELECT invoice.invoice_no,invoice.sold_qty,
item.item_no,item_descrip
FROM invoice
RIGHT JOIN item
ON item.item_no=invoice.item_no;

OR

Code:

SELECT invoice.invoice_no,invoice.sold_qty,
item.item_no,item_descrip
FROM invoice
RIGHT OUTER JOIN item
ON item.item_no=invoice.item_no;

Output:

Explanation

In the above example, the item_no I8 of item table not exists in the invoice table, and for this row in the item table, a new row have been generated in the invoice table and sets the value NULL for this row.

Pictorial Presentation for the above example

Previous: LEFT JOIN
Next: FULL OUTER JOIN