JOIN OPERATION IN PostgreSQL

Share via:

JOIN OPERATION IN PostgreSQL:

In PostgreSQL, the JOIN clause is used to combine rows from two or more tables based on a related column or condition. It specifies how the tables should be joined and on which columns the join should be performed.
There are several types of joins available in PostgreSQL:
1:  INNER JOIN
2: LEFT JOIN
3: RIGHT JOIN
4: FULL JOIN
5: CROSS JOIN / CARTESIAN JOIN
6: NATURAL JOIN
7: SELF JOIN
INNER JOIN:
In PostgreSQL, an Inner Join combines rows from two or more tables based on a specified condition and returns only the matching rows. The “INNER JOIN” keyword is used to perform an inner Join in PostgreSQL.

LEFT JOIN:
In PostgreSQL, the LEFT JOIN, also known as a left outer join, Is used to retrieve all the rows from the left table and the matching rows from the right table. If no match is found in the right table, NULL values are returned for the columns of the right table.

RIGHT JOIN:
In PostgreSQL, the RIGHT JOIN is used to combine rows from two tables based on a related column, with all the rows from the right table and only the matching rows from the left table. If no match is found, NULL values are returned for the columns of the left table.

FULL JOIN:
In PostgreSQL, a full Join (or full outer join) is a type of Join that combines the result sets of a left join and a right join. It returns all rows from both tables, matching rows from the left table with corresponding rows from the right table. If there are no matches, NULL values are Included for the non-matching side.

CROSS JOIN:
In PostgreSQL, a CROSS JOIN Is a type of Join that combines each row from the first table with every row from the second table, resulting in a Cartesian product. It generates a result set that contains all possible combinations of rows between the Joined tables.

NATURAL JOIN:
In PostgreSQL, the NATURAL JOIN is a type of join that automatically matches columns with the same name in two tables.
Here’s we have two tables “stu_name” and “stu_grades”. Let’s use the NATURAL JOIN operation to combine the tables.

SELF JOIN:
In PostgreSQL, a self-join is used to Join a table to Itself. It allows you to compare and combine rows from the same table based on a related column. Here’s an example of how to perform a self-Join in PostgreSQL:
Let’s say we have a table called “Employee” with the following columns empid, empname, and mgrid. The mgrID column represents the ID of the employee’s manager.

 

Author    : Prudhvi Teja

LinkedIn  : http://linkedin.com/in/prudhvi-teja-nagabhyru-715052224

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTExperts is always active on social media platforms.

Facebook  : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter       : https://twitter.com/ktexpertsadmin
YouTube   :  https://www.youtube.com/c/ktexperts

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

Add Comment