Yogesh Chauhan's Blog

RIGHT JOIN in Postgres

in Postgres on January 9, 2021

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

Basically, it looks like this.

RIGHT JOIN in PostgreSQL

The highlighted green part from the image above is our query results when we apply RIGHT JOIN between 2 tables.

Syntax:


SELECT a.column2, b.column2
FROM table a
RIGHT JOIN table b
ON a.column = b.column;

I am using alias as well in the syntax. You can use name of the table instead of alias. Alias makes it better to write the query. Learn more about alias in this post:

Column And Table Alias In Postgres

RIGHT JOIN Query Example 2 tables

I am using the database for all examples. It is available on my Github public repo


SELECT c.contact_name, o.order_date 
FROM customers c 
RIGHT JOIN orders o ON c.customer_id = o.customer_id

//Output
contact_name.       order_date
....
....
....
"Guillermo Fernández"    "1998-05-05"
"Jytte Petersen"    "1998-05-06"
"Michael Holz"    "1998-05-06"
"Laurence Lebihan"    "1998-05-06"
"Paula Wilson"    "1998-05-06"
...
830 rows

It’s the same example from this LEFT JOIN post: LEFT JOIN In Postgres

In LEFT JOIN we saw the rows with some null values in the right column because it was including all the columns from left and the matches from right. Over here, all the columns in the right table are already in the left table so we don’t see any null values in the results.

What will happen if we apply RIGHT JOIN to 3 tables?

RIGHT JOIN Query Example 3 tables


SELECT c.contact_name, o.order_date, od.product_id
FROM customers c 
RIGHT JOIN orders o ON c.customer_id = o.customer_id
RIGHT JOIN order_details od ON od.order_id = o.order_id

//Output
contact_name.    order_date.   count
...
...
...
"Paula Wilson"    "1998-05-06"    73
"Paula Wilson"    "1998-05-06"    75
"Paula Wilson"    "1998-05-06"    77
...
2155 rows

This example is also from this LEFT JOIN post: LEFT JOIN In Postgres

And same as the previous example, over here, all the columns in the right table are already in the left table so we don’t see any null values in the results.

We are seeing the same names in many rows because we haven’t used GROUP BY clause. If we want to filter this data more, we can add an aggregate function and add GROUP BY clause.


Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
How to show widgets on the Appearance tab in WordPress?WordPressHow to replace HTML lists using CSS Counters?CSSOOP, Class and Objects Strategies For Beginners (PHP)PHPWordPress: How to display fields from ACF Flexible Contents?WordPressFew more :nth-child examples in CSSCSSSome Useful Operators in The SQL WHERE ClauseSQL/MySQL