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.
|
1 2 3 4 5 |
postgres=# select * from stu_table1 INNER JOIN stu_table2 ON name=stu_name; id | name | stu_id | stu_name ----+-------+--------+---------- 1 | Harry | 2 | Harry 2 | Peter | 1 | Peter |
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.
|
1 2 3 4 5 6 7 |
postgres=# select * from stu_table1 LEFT JOIN stu_table2 ON name=stu_name; id | name | stu_id | stu_name ----+-------+--------+---------- 1 | Harry | 2 | Harry 2 | Peter | 1 | Peter 3 | steve | | 4 | Marry | | |
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.
|
1 2 3 4 5 6 7 |
postgres=# select * from stu_table1 RIGHT JOIN stu_table2 ON name=stu_name; id | name | stu_id | stu_name ----+-------+--------+---------- 2 | Peter | 1 | Peter 1 | Harry | 2 | Harry | | 3 | David | | 4 | Person |
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.
|
1 2 3 4 5 6 7 8 9 |
postgres=# select * from stu_table1 FULL OUTER JOIN stu_table2 ON name=stu_name; id | name | stu_id | stu_name ----+-------+--------+---------- 1 | Harry | 2 | Harry 2 | Peter | 1 | Peter 3 | steve | | 4 | Marry | | | | 4 | Person | | 3 | David |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
postgres=# SELECT * FROM stu_table1 CROSS JOIN stu_table2; id | name | stu_id | stu_name ----+-------+--------+---------- 1 | Harry | 1 | Peter 1 | Harry | 2 | Harry 1 | Harry | 3 | David 1 | Harry | 4 | Person 2 | Peter | 1 | Peter 2 | Peter | 2 | Harry 2 | Peter | 3 | David 2 | Peter | 4 | Person 3 | steve | 1 | Peter 3 | steve | 2 | Harry 3 | steve | 3 | David 3 | steve | 4 | Person 4 | Marry | 1 | Peter 4 | Marry | 2 | Harry 4 | Marry | 3 | David 4 | Marry | 4 | Person |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
id | name id | grades ----+-------- ----+------- 1 | Harry 1 | A+ 2 | Peter 2 | B 3 | steve 3 | A 4 | Marry 4 | B+ postgres=# SELECT * FROM scholar_name NATURAL JOIN scholar_grades; id | name | grades ----+-------+-------- 1 | Harry | A+ 2 | Peter | B 3 | steve | A 4 | Marry | B+ |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
postgres=# select empid, empname, sal, mgrid from Employee; empid | empname | sal | mgrid -------+------------------+---------+------- 1 | John Doe | 5000.00 | 4 2 | Jane Smith | 6000.00 | 4 3 | Michael Johnson | 5500.00 | 4 4 | Emily Davis | 7000.00 | 5 5 | David Wilson | 6500.00 | 6 6 | Sarah Brown | 7500.00 | 6 7 | Robert Miller | 5200.00 | 4 8 | Karen Thompson | 5800.00 | 5 9 | Daniel Lee | 7200.00 | 5 To perform a self join to find pairs of employees with their manager’s you can use the following query: postgres=# select e.empname, m.empname from employee e INNER JOIN employee m ON e.mgrid=m.empid; empname | empname ------------------+-------------- John Doe | Emily Davis Jane Smith | Emily Davis Michael Johnson | Emily Davis Emily Davis | David Wilson David Wilson | Sarah Brown Sarah Brown | Sarah Brown Robert Miller | Emily Davis Karen Thompson | David Wilson Daniel Lee | David Wilson |
| 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
Note: Please test scripts in Non Prod before trying in Production.
Loading...
