Various JOINS are used in the PostgreSQL database to fetch joined records from multiple tables. For instance, the "RIGHT JOIN" joins the data from the right table and only the corresponding values from the left table. It retrieves Null for all the records on the left that do not contain any value or don’t match with the right table.

This guide will explain how to use RIGHT JOIN in PostgreSQL.

How to Use RIGHT JOIN in PostgreSQL?

Databases use RIGHT JOIN to merge or combine multiple tables in the PostgreSQL database by applying different queries to them. It is required to have knowledge about the structure of the tables, like which one is the right table and which is the left one. Applying RIGHT JOIN will return all the data available on the right table and corresponding data from the left one.

Syntax

The following code snippet illustrates the syntax of Postgres’ RIGHT JOIN:

SELECT <Columns>
FROM <TableA>
 RIGHT JOIN <TableB>
 ON <TableA.Column_x> = <TableB.Column_y>;

The above code block contains the following information:

- It selects the list of columns present in the right table. Use the “*” sign with the SELECT statement to fetch all columns in the said table.
- Specify the right table or Table A in the FROM clause.
- Specify the RIGHT JOIN followed by the name of the left table or Table B which needs to be merged with the right table or Table A.
- ON clause contains the condition on which the tables will be combined.

Example 1: Combine Tables With RIGHT JOIN Using ON Clause

The example displays the RIGHT JOIN being applied on two tables created in the PostgreSQL database using the following query to fetch data from the person table:

SELECT * FROM person;

Running the above query will display all records from the table person:

Another table named “vehicle” containing records about vehicles can be accessed using the following query:

SELECT * FROM vehicle;

The above query returns the records available in the vehicle table:

Use the following query to apply RIGHT JOIN on the given tables:

SELECT * FROM vehicle
RIGHT JOIN person ON vehicle.id = person.vehicle_id;

The above code suggests the following:

- The SELECT keyword with a “*” will get all the data from the vehicle table.
- RIGHT JOIN is used on the person table with conditions specified using the ON clause.
- The specified condition states that match the values from the id column on the vehicle table with vehicle_id from the person table:

The output displays all the records of the vehicle table and the matching records of the person table.

Example 2: RIGHT JOIN With USING Clause

RIGHT JOIN with USING condition combines the tables having the same column name between them. In the following example, the join condition is specified in the RIGHT JOIN via the “USING” clause:

SELECT * FROM vehicle
 RIGHT JOIN person USING(id);

The above code applies RIGHT JOIN on the “vehicle” and “person” tables based on the “id” column:

Example 3: Using RIGHT JOIN With WHERE Clause

The following query contains another example of the RIGHT JOIN with the additional condition using the WHERE clause:

SELECT * FROM vehicle
 RIGHT JOIN person USING(id)
 WHERE vehicle_id IS NULL;

The above query will only return records that don’t contain any value in the vehicle-id column:

That’s all about using RIGHT JOIN in the PostgreSQL database.

Conclusion

In PostgreSQL, RIGHT JOIN is used to merge two tables in the database and return all records of the Right table and only matched records from the left one. The user is allowed to add conditions with the help of ON and USING clauses to apply RIGHT JOIN. Additional conditions can be applied to get specific records using the WHERE clause with the RIGHT JOIN. This guide has explained the use of RIGHT JOIN in PostgreSQL tables.