YogeshChauhan.com
SELF JOIN in Postgres
October 16, 2020


A Self JOIN is a regular join, but the table is joined with itself.

Basically, it looks like this.

SELF JOIN in PostgreSQL

There is no keyword like SELF. We can use INNER JOIN, LEFT JOIN, RIGHT JOIN etc to JOIN table to itself. Sound weird. Isn’t it?

Syntax:


SELECT a.column2, b.column2
FROM table a
INNER 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

INNER SELF JOIN Query Example 

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


SELECT c.contact_name, cu.contact_title 
FROM customers c RIGHT JOIN customers cu
ON c.customer_id = cu.customer_id

//Output
contact_name.       contact_title
"Maria Anders"    "Sales Representative"
"Ana Trujillo"    "Owner"
"Antonio Moreno"    "Owner"
...
...
91 rows

I know, I have the same question, why we need to JOIN same tables!

We use a self join when a table references data in itself.

SELF JOIN Query Example

Let’s say we want to find out company names of the customers whose city are same but id are not. Means they live in same city.

This is the example of SELF JON without using the keyword JOIN. 


SELECT A.company_name AS companyName1, B.company_name AS companyName2, A.city
FROM customers A, customers B
WHERE A.customer_id <> B.customer_id
AND A.city = B.city 
ORDER BY A.city;

//Output
companyName1                       companyName2                     city
"Océano Atlántico Ltda."	       "Cactus Comidas para llevar"	    "Buenos Aires"
"Océano Atlántico Ltda."	       "Rancho grande"                  "Buenos Aires"
"Cactus Comidas para llevar"	   "Océano Atlántico Ltda."	        "Buenos Aires"
"Cactus Comidas para llevar"	   "Rancho grande"	                "Buenos Aires"
"Rancho grande"	                   "Océano Atlántico Ltda."	        "Buenos Aires"
"Rancho grande"	                   "Cactus Comidas para llevar"	    "Buenos Aires"
"Furia Bacalhau e Frutos do Mar"   "Princesa Isabel Vinhos"	        "Lisboa"
"Princesa Isabel Vinhos"	       "Furia Bacalhau e Frutos do Mar" "Lisboa"
...
...
88 rows

dreamhost

Leave a Reply

Most Read

#1 How to set opacity or transparency using CSS? #2 Pagination in CSS with multiple examples #3 How to check if radio button is checked or not using JavaScript? #4 How to make HTML form interactive and using CSS? #5 How to uninstall Cocoapods from the Mac OS? #6 How to add Read More Read Less Button using JavaScript?



Recently Posted

Feb 24 How to modify the latest post array using get_posts() in WordPress? Feb 22 WordPress: How to get ACF field values from another post? Feb 22 WordPress: How to print ACF repeater field values? Feb 22 WordPress: How to print ACF array field values? Feb 21 WordPress: How to get field values in Advanced Custom Fields? Feb 21 WordPress: How to add a Search Icon in Menus with toggle effect using jQuery?



You might also like these

JavaScript Input Validation Theme Park Example using throw StatementJavaScriptaddcslashes() and addslashes() String Functions in PHPPHPWordPress: How to add a Search Icon in Menus with toggle effect using jQuery?jQueryHow to create a cross-browser smooth scrolling with jQuery?jQueryHow to show confirmation alerts with OK and cancel buttons using Swift 5?SwiftHow to check if the page is single post page in WordPress?WordPress