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

• 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 tables COMPANY and DEPARTMENT。

Online Example

Create COMPANY table（Download COMPANY SQL file ），data content is as follows：

w3codeboxdb#　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 several data entries 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 moment, the records of 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 and add 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 time, 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

A 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, then the resulting table has x*y rows.

Since a cross join (CROSS JOIN) can produce a very large table, it must be used with caution and only at appropriate times.

The basic syntax of CROSS JOIN is as follows:

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

Based on the above table, we can write a cross join (CROSS JOIN) as follows:

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

The results are as follows:

w3codeboxdb=# 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）根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表。查询会把 table1 中的每一行与 table2 　中的每一行进行比较，找到所有满足连接谓词的行的匹配对。

当满足连接谓词时，A 和 B 行的每个匹配对的列值会合并成一个结果行。

内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型。

INNER 关键字是可选的。

下面是内连接（INNER JOIN）的语法：

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

基于上面的表，我们可以写一个内连接，如下所示：

w3codeboxdb=#　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 join is an extension of inner join. The SQL standard defines three types of outer joins: LEFT, RIGHT, and FULL, PostgreSQL supports all these.

For the left outer join, first execute an inner join. Then, for the table T1 of any row that does not meet the table T2 where each row of the join condition, where T2 column with null values will also add a join row. Therefore, the joined tables in T1 There is at least one row in each.

The basic syntax of the left outer join (LEFT OUTER JOIN) is as follows:

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

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

w3codeboxdb=# 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, execute the inner join. Then, for the table T2of any row that does not meet the table T1of each row that meets the connection condition, where T1of the column value is empty will also add a connection line. This is opposite to the left join; for T2of each row, there is always one row in the result table.

The basic syntax of the right outer join (RIGHT OUT JOIN) is as follows:

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

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

w3codeboxdb=# 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)

Outer join

First, execute the inner join. Then, for the table T1 of any row that does not meet the table T2 of any row that meets the connection condition, if T2 in the column with null values will also be added to the result. In addition, for T2 of any row that does not meet the connection condition with T1 of any row that meets the connection condition will be added T1 Rows containing null values are included in the result.

The basic syntax of the outer join is as follows:

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

Based on the above two tables, we can create an outer join:

w3codeboxdb=# 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)