PostgreSQL JOIN
The PostgreSQL JOIN clause is used to combine rows from two or more tables, based on common fields between these tables.
In PostgreSQL, there are five types of JOIN:
- CROSS JOIN: Cross Join
- INNER JOIN: Inner Join
- LEFT OUTER JOIN: Left Outer Join
- RIGHT OUTER JOIN: Right Outer Join
- FULL OUTER JOIN: Full Outer Join
Next, let's create two tablesCOMPANYandDEPARTMENT。
Example
Create the COMPANY table (Download COMPANY SQL file), the data content is as follows:
exampledb# select * from COMPANY; id | name | age | address | salary ----+-------+-----+-----------+-------- 1 | Paul | 32 | California| 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall| 45000 7 | James | 24 | Houston | 10000 (7 rows)
Let's add a few records to the table:
INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00); INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00); INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);
At this point, the records in the COMPANY table are as follows:
id | name | age | address | salary ----+-------+-----+--------------+-------- 1 | Paul | 32 | California | 20000 2 | Allen | 25 | Texas | 15000 3 | Teddy | 23 | Norway | 20000 4 | Mark | 25 | Rich-Mond | 65000 5 | David | 27 | Texas | 85000 6 | Kim | 22 | South-Hall | 45000 7 | James | 24 | Houston | 10000 8 | Paul | 24 | Houston | 20000 9 | James | 44 | Norway | 5000 10 | James | 45 | Texas | 5000 (10 rows)
Create a DEPARTMENT table with three fields:
CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL );
Insert three records into the 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 this point, the records in the DEPARTMENT table are as follows:
id | dept | emp_id ----+-------------+-------- 1 | IT Billing | 1 2 | Engineering | 2 3 | Finance | 7
Cross Join
CROSS JOIN matches each row of the first table with each row of the second table. If the two input tables have x and y rows respectively, the result table has x*y rows.
Since CROSS JOIN may produce very large tables, you must be careful when using it and only use it when appropriate.
The following is the basic syntax of CROSS JOIN:
SELECT ... FROM table1 CROSS JOIN table2 ...
Based on the tables above, we can write a CROSS JOIN as follows:
exampledb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
The result obtained is as follows:
exampledb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
emp_id | name | dept
--------+-------+--------------------
1 | Paul | IT Billing
1 | Allen | IT Billing
1 | Teddy | IT Billing
1 | Mark | IT Billing
1 | David | IT Billing
1 | Kim | IT Billing
1 | James | IT Billing
1 | Paul | IT Billing
1 | James | IT Billing
1 | James | IT Billing
2 | Paul | Engineering
2 | Allen | Engineering
2 | Teddy | Engineering
2 | Mark | Engineering
2 | David | Engineering
2 | Kim | Engineering
2 | James | Engineering
2 | Paul | Engineering
2 | James | Engineering
2 | James | Engineering
7 | Paul | Finance
Inner Join
INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based on the join predicate. The query compares each row in table1 with each row in table2 to find all pairs of rows that satisfy the join predicate.
When the join predicate is satisfied, the column values of each matching pair of rows A and B are merged into one result row.
INNER JOIN is the most common type of join and is the default join type.
The INNER keyword is optional.
The following is the syntax of INNER JOIN:
SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON table1.common_filed = table2.common_field;
Based on the tables above, we can write an inner join as follows:
exampledb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
emp_id | name | dept
--------+-------+--------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
(3 rows)
Left Outer Join
Outer joins are an extension of inner joins. The SQL standard defines three types of outer joins: LEFT, RIGHT, and FULL. PostgreSQL supports all of them.
For left outer join, an inner join is first performed. Then, for each row in table T1 that does not satisfy the join condition with table T2, a joined row with null values in T2's columns is also added. Therefore, the joined table has at least one row for each row in T1.
The following is the basic syntax of LEFT OUTER JOIN:
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
Based on the two tables above, we can write a left outer join as follows:
exampledb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
emp_id | name | dept
--------+-------+----------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
| James |
| David |
| Paul |
| Kim |
| Mark |
| Teddy |
| James |
(10 rows)
Right Outer Join
First, an inner join is performed. Then, for each row in table T2 that does not satisfy the join condition with table T1, a joined row with null values in T1's columns is also added. This is the opposite of left join; for each row in T2, the result table always has one row.
The following is the basic syntax of right outer join (RIGHT OUT JOIN):
SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...
Based on the two tables above, we create a right outer join:
exampledb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
emp_id | name | dept
--------+-------+-----------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
(3 rows)
Full Outer Join
First, an inner join is performed. Then, for each row in table T1 that does not satisfy the join condition with any row in table T2, a row with null values in T2's columns is also added to the result. In addition, for each row in T2 that does not satisfy the join condition with any row in T1, a row with null values in T1's columns is added to the result.
The following is the basic syntax of the full outer join:
SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...
Based on the two tables above, we can create a full outer join:
exampledb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
emp_id | name | dept
--------+-------+-----------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
| James |
| David |
| Paul |
| Kim |
| Mark |
| Teddy |
| James |
(10 rows)
Other Extensions