5.29. PostgreSQL connection (JOIN)

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 .

5.29.1. Example ¶

Create the COMPANY table ( 下载 COMPANY SQL 文件 ), the data are as follows:

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) 

We add a few pieces of data 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 record of the COMPANY table is 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 one. DEPARTMENT Table, add three fields:

CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL ); 

To 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 ); 

At this time 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 ¶

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:

SELECT ... FROM table1 CROSS JOIN table2 ... 

Based on the above table, we can write a CROSS JOIN, as follows:

runoobdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT; 

The results are as follows:

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 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.

INNER Keywords are optional.

Here is the syntax for INNER JOIN:

SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON table1.common_filed = table2.common_field; 

Based on the above table, we can write an inner join, as follows:

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 ¶

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):

SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ... 

Based on the above two tables, we can write a left outer connection, as follows:

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 ¶

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):

SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ... 

Based on the above two tables, we establish a right outer connection:

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 ¶

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:

SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ... 

Based on the above two tables, you can establish an external connection:

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) 