YogeshChauhan . com

NATURAL JOIN in Postgres

in Postgres on July 31, 2020

A NATURAL JOIN creates an implicit join based on the same column names in the tables.

Basically, it looks like this.

NATURAL JOIN in PostgreSQL

Natural joins can be INNER JOIN, LEFT JOIN, or RIGHT JOIN. By default it’s INNER JOIN means NATURAL JOIN = INNER JOIN by default but it will consider all the column names that are same to join the table.

Syntax:


SELECT a.column2, b.column2
FROM table a
NATURAL [INNER | LEFT | RIGHT] JOIN table b;

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

NATURAL [INNER] JOIN Query Example 

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 NATURAL JOIN orders o;

//Output
contact_name.  order_date 
"Paul Henriot"    "1996-07-04"
"Karin Josephs"    "1996-07-05"
"Mario Pontes"    "1996-07-08"
...
...
830 rows

It’s the same result as the first example in this INNER JOIN post: INNER JOIN In Postgres

NATURAL LEFT JOIN Query Example


SELECT c.contact_name, o.order_date 
FROM customers c NATURAL LEFT JOIN orders o;

//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"
"Marie Bertrand"    null
"Diego Roel"        null
...
832 rows

It’s the same result as the first example in this LEFT JOIN post: LEFT JOIN In Postgres

RIGHT LEFT JOIN Query Example


SELECT c.contact_name, o.order_date 
FROM customers c NATURAL RIGHT JOIN orders o;

//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 result as the first example in this RIGHT JOIN post: RIGHT JOIN In Postgres

When and why to use NATURAL JOIN?

CASE 1: In my opinion, if you have just one common (similar name) column between two or more tables and if you want to JOIN them then you should definitely go for NATURAL JOIN. 

CASE 2: If you have more than one common (similar name) columns and if you want the result to consider all the common columns, then use NATURAL JOIN.

CASE 3: Now, If you have more than one common (similar name) columns and if you want to consider specific common column then DO NOT use NATURAL JOIN.

dreamhost

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 uninstall Cocoapods from the Mac OS? #4 How to add Read More Read Less Button using JavaScript? #5 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

May 5 Use inline if to make a shorter conditional syntax in React May 4 What is Conditional Rendering in React? May 3 How does @extend rule work in SCSS (Sass)? May 2 How different is Handling Events in React vs HTML DOM? May 2 How to create bouncing balls using HTML canvas and JavaScript? Apr 30 HTML canvas methods

You might also like these

Some Useful Operators in The SQL WHERE ClauseSQL/MySQLMySQL queries to get data rows from previous month as well as from last 30 daysSQL/MySQLHow to add CurrencyPipe in TypeScript file in Angular 9 Project?AngularDebugging in WordPress Part 2: WP_DEBUG_LOG and WP_DEBUG_DISPLAYWordPressWhy does MOV matter in IT or any kind of projects?MiscHow to create a simple stopwatch using JavaScript?JavaScript