The PostgreSQL Joins provision is utilized to consolidate records from at least two tables in a data set. A JOIN is a methods for consolidating fields from two tables by utilizing values regular to each.
Join Types in PostgreSQL are −
- The CROSS JOIN
- The INNER JOIN
- The LEFT OUTER JOIN
- The RIGHT OUTER JOIN
- The FULL OUTER JOIN
Before we continue, let us think about two tables, COMPANY and DEPARTMENT. We as of now have seen INSERT proclamations to populate COMPANY table. So let us accept the rundown of records accessible in COMPANY table −
id | name | age | address | salary | join_date
----+-------+-----+-----------+--------+-----------
1 | Paul | 32 | California| 20000 | 2001-07-13
3 | Teddy | 23 | Norway | 20000 |
4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13
5 | David | 27 | Texas | 85000 | 2007-12-13
2 | Allen | 25 | Texas | | 2007-12-13
8 | Paul | 24 | Houston | 20000 | 2005-07-13
9 | James | 44 | Norway | 5000 | 2005-07-13
10 | James | 45 | Texas | 5000 | 2005-07-13
Another table is DEPARTMENT, has the accompanying definition −
CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);
Here is the rundown of INSERT explanations to populate DEPARTMENT table −
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (1, 'IT Billing', 1 );
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (2, 'Engineering', 2 );
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
VALUES (3, 'Finance', 7 );
At last, we have the accompanying rundown of records accessible in DEPARTMENT table −
id | dept | emp_id
----+-------------+--------
1 | IT Billing | 1
2 | Engineering | 2
3 | Finance | 7
The CROSS JOIN
A CROSS JOIN coordinates each line of the principal table with each line of the subsequent table. On the off chance that the information tables have x and y segments, individually, the subsequent table will have x+y segments. Since CROSS JOINs can possibly create amazingly enormous tables, care should be taken to utilize them just when suitable.
Coming up next is the punctuation of CROSS JOIN −
SELECT ... FROM table1 CROSS JOIN table2 ...
In light of the above tables, we can compose a CROSS JOIN as follows −
testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
The above given inquiry will deliver the accompanying outcome −
emp_id| name | dept
------|-------|--------------
1 | Paul | IT Billing
1 | Teddy | IT Billing
1 | Mark | IT Billing
1 | David | IT Billing
1 | Allen | IT Billing
1 | Paul | IT Billing
1 | James | IT Billing
1 | James | IT Billing
2 | Paul | Engineering
2 | Teddy | Engineering
2 | Mark | Engineering
2 | David | Engineering
2 | Allen | Engineering
2 | Paul | Engineering
2 | James | Engineering
2 | James | Engineering
7 | Paul | Finance
7 | Teddy | Finance
7 | Mark | Finance
7 | David | Finance
7 | Allen | Finance
7 | Paul | Finance
7 | James | Finance
7 | James | Finance
The INNER JOIN
An INNER JOIN makes another outcome table by consolidating segment estimations of two tables (table1 and table2) in light of the join-predicate. The question contrasts each line of table1 and each column of table2 to discover all sets of lines, which fulfill the join-predicate. At the point when the join-predicate is fulfilled, segment esteems for each coordinated pair of lines of table1 and table2 are consolidated into an outcome line.
An INNER JOIN is the most widely recognized kind of join and is the default sort of join. You can utilize INNER catchphrase alternatively.
Coming up next is the language structure of INNER JOIN −
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;
In light of the above tables, we can compose an INNER JOIN as follows −
testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
The above given inquiry will deliver the accompanying outcome −
emp_id | name | dept
--------+-------+------------
1 | Paul | IT Billing
2 | Allen | Engineering
The LEFT OUTER JOIN
The OUTER JOIN is an expansion of the INNER JOIN. SQL standard characterizes three sorts of OUTER JOINs: LEFT, RIGHT, and FULL and PostgreSQL underpins these.
If there should be an occurrence of LEFT OUTER JOIN, an inward join is performed first. At that point, for each line in table T1 that doesn't fulfill the get condition together with any line in table T2, a joined line is added with invalid qualities in segments of T2. Accordingly, the joined table consistently has at any rate one column for each line in T1.
Coming up next is the grammar of LEFT OUTER JOIN −
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
In view of the above tables, we can compose an internal join as follows −
testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
The above given question will deliver the accompanying outcome −
emp_id | name | dept
--------+-------+------------
1 | Paul | IT Billing
2 | Allen | Engineering
| James |
| David |
| Paul |
| Mark |
| Teddy |
| James |
The RIGHT OUTER JOIN
Initial, an inward join is performed. At that point, for each line in table T2 that doesn't fulfill the get condition together with any line in table T1, a joined line is added with invalid qualities in sections of T1. This is the opposite of a left join; the outcome table will consistently have a column for each line in T2.
Coming up next is the sentence structure of RIGHT OUTER JOIN −
SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...
In light of the above tables, we can compose an internal join as follows −
testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
The above given inquiry will create the accompanying outcome −
emp_id | name | dept
--------+-------+--------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | | Finance
The FULL OUTER JOIN
Initial, an internal join is performed. At that point, for each line in table T1 that doesn't fulfill the get condition together with any line in table T2, a joined line is added with invalid qualities in segments of T2. Also, for each line of T2 that doesn't fulfill the get condition together with any line in T1, a got line together with invalid qualities in the sections of T1 is added.
Coming up next is the linguistic structure of FULL OUTER JOIN −
SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...
In light of the above tables, we can compose an internal join as follows −
testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT
ON COMPANY.ID = DEPARTMENT.EMP_ID;
The above given inquiry will deliver the accompanying outcome −
emp_id | name | dept
--------+-------+---------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | | Finance
| James |
| David |
| Paul |
| Mark |
| Teddy |
| James |