The PostgreSQL JOIN clause is used to combine rows from two or more tables based on common fields between those tables.
In PostgreSQL, JOIN has five connection types:
CROSS JOIN: cross connect
INNER JOIN: internal connection
LEFT OUTER JOIN: left outer connection
RIGHT OUTER JOIN: right external connection
FULL OUTER JOIN: full external connection
Next, let’s create two tables COMPANY And DEPARTMENT . Create the COMPANY table ( 下载 COMPANY SQL 文件 ), the data are as follows: We add a few pieces of data to the table: At this point, the record of the COMPANY table is as follows: Create one. To At this time 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 rows. Because cross-joins (CROSS JOIN) have the potential to produce very large tables, you must be careful to use them only when appropriate. Here is the basic syntax of CROSS JOIN: Based on the above table, we can write a CROSS JOIN, as follows: The results are as follows: Inner join (INNER JOIN) creates a new result table based on the join predicate combining the column values of the two tables (table1 and table2). The query compares each row in table1 with each row in table2 to find a match for all rows that satisfy the join predicate. When the join predicate is satisfied, the column values of each match of rows An and B are merged into a single result row. Internal connection (INNER JOIN) is the most common connection type and is the default connection type. Here is the syntax for INNER JOIN: Based on the above table, we can write an inner join, as follows: An external connection is an extension of an internal connection. The SQL standard defines three types of external connections: LEFT, RIGHT, and FULL, all of which PostgreSQL supports. For the left outer connection, first perform an inner connection. Then, for each row in table T1 that does not meet the join condition in table T2, a join row is added with a null value in the column of T2. Therefore, the joined table has at least one row in T1. The following is the basic syntax of the left outer join (LEFT OUTER JOIN): Based on the above two tables, we can write a left outer connection, as follows: First, perform an internal connection. Then, for each row in table T2 that does not meet the join condition in table T1, a join row is also added if the value in the T1 column is empty. This is the opposite of the left join; for each row in T2, the resulting table always has a row. The following is the basic syntax for the right outer join (RIGHT OUT JOIN): Based on the above two tables, we establish a right outer connection: First, perform an internal connection. Then, for each row in table T1 that does not meet any of the row join conditions in table T2, one is added to the result if there is a null value in the column of T2. In addition, for each row in T2 that does not meet the conditions for joining any row in T1, the T1 column containing the null value will be added to the result. The following is the basic syntax for external joins: Based on the above two tables, you can establish an external connection: 5.29.1. Example ¶
runoobdb# 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)
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);
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)
DEPARTMENT
Table, add three fields:CREATE TABLE DEPARTMENT(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT NOT NULL
);
DEPARTMENT
The table inserts three records: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 );
DEPARTMENT
The record of the table is as follows: id | dept | emp_id
----+-------------+--------
1 | IT Billing | 1
2 | Engineering | 2
3 | Finance | 7
5.29.2. Cross connection ¶
SELECT ... FROM table1 CROSS JOIN table2 ...
runoobdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
runoobdb=# 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
5.29.3. Internal connection ¶
INNER
Keywords are optional.SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;
runoobdb=# 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)
5.29.4. Left outer connection ¶
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
runoobdb=# 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)
5.29.5. Right outer connection ¶
SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...
runoobdb=# 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)
5.29.6. External connection ¶
SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...
runoobdb=# 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)