关联查询是指从 TDSQL PG 数据库的两个或多个表/视图中检索数据。TDSQL PG 支持的关联方式的有内连接、（全/左/右）外连接、自然连接等。
内连接
内连接用于返回满足联接条件的多表数据。
INNER JOIN需要搭配联接条件（ON）一起使用，否则将返回多表的笛卡尔积。
语法
SELECT target_list FROM table_name1 [INNER] JOIN table_name2 ON join_condition
[ WHERE query_condition ]
[ ORDER BY column_list ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition ]
[ LIMIT { count | ALL } ]
[ OFFSET start [ ROW | ROWS ] ]
或者，直接在 FROM 子句后面写多个表（或者视图），使用逗号分开
SELECT target_list FROM table_name1，table_name2 [ WHERE query_condition ] ...
示例
查询有多少人比自己的薪水低
select e1.name, count(*) from EMPLOYEES e1 join EMPLOYEES e2 on e1.salary > e2.salary group by e1.name;
NAME | COUNT
-------+-------
henry | 8
eric | 2
andy | 4
bob | 9
david | 7
leo | 10
frank | 6
julia | 2
emily | 5
(9 rows)
另一种内联接写法
select e1.name, count(*) from EMPLOYEES e1, EMPLOYEES e2 WHERE e1.salary > e2.salary group by e1.name;
NAME | COUNT
-------+-------
henry | 8
eric | 2
andy | 4
bob | 9
david | 7
leo | 10
frank | 6
julia | 2
emily | 5
(9 rows)
全外连接
全外连接，FULL [OUTER] JOIN，返回多表的所有的行，包括满足联接条件的行和不满足的行，未匹配到的用 NULL 值填充。
语法
SELECT target_list FROM table_name1 FULL [OUTER] JOIN table_name2 ON join_condition
[ WHERE query_condition ]
[ ORDER BY column_list ]
示例
CREATE TABLE t1(a INT, b INT, c INT);
CREATE TABLE t2(x INT, y INT, z INT);
INSERT INTO t1 VALUES(1, 1, 1);
INSERT INTO t1 VALUES(2, 2, 2);
INSERT INTO t1 VALUES(3, 3, 3);
INSERT INTO t2 VALUES(2, 2, 2);
INSERT INTO t2 VALUES(3, 3, 3);
INSERT INTO t2 VALUES(5, 5, 5);
SELECT * FROM t1 FULL JOIN t2 ON a = x;
A | B | C | X | Y | Z
---+---+---+---+---+---
1 | 1 | 1 | | |
2 | 2 | 2 | 2 | 2 | 2
3 | 3 | 3 | 3 | 3 | 3
| | | 5 | 5 | 5
左外连接
左外连接，LEFT [OUTER] JOIN，它返回左侧表中所有的行，并与右侧表进行匹配，未匹配到的用 NULL 值填充。
语法
SELECT target_list FROM table_name1 LEFT JOIN table_name2 ON join_condition
[ WHERE query_condition ]
[ ORDER BY column_list ]
示例
SELECT * FROM t1 LEFT JOIN t2 ON a = x;
A | B | C | X | Y | Z
---+---+---+---+---+---
1 | 1 | 1 | | |
2 | 2 | 2 | 2 | 2 | 2
3 | 3 | 3 | 3 | 3 | 3
(3 rows)
右外连接
右外连接又称为 RIGHT [OUTER] JOIN，它返回右侧表中所有的行，并与左侧表进行匹配，未匹配到的用 NULL 值填充
语法
SELECT target_list FROM table_name1 RIGHT [OUTER] JOIN table_name2 ON join_condition
[ WHERE query_condition ]
[ ORDER BY column_list ]
示例
SELECT * FROM t1 RIGHT JOIN t2 ON a = x;
A | B | C | X | Y | Z
---+---+---+---+---+---
2 | 2 | 2 | 2 | 2 | 2
3 | 3 | 3 | 3 | 3 | 3
| | | 5 | 5 | 5
(3 rows)
自然连接
自然连接， NATURAL JOIN，是一种特殊的等值连接，它要求两个表中进行比较的列必须是同名且可比较，并在结果中把同名的列去掉，如果列类型不匹配则会报错。
语法
SELECT target_list FROM table_name1 NATURAL JOIN table_name2
示例
CREATE TABLE tab1(a INT, b INT, c INT);
CREATE TABLE tab2(a INT, d INT, f INT);
INSERT INTO tab1 VALUES(1, 1, 1);
INSERT INTO tab1 VALUES(2, 2, 2);
INSERT INTO tab1 VALUES(3, 3, 3);
INSERT INTO tab2 VALUES(2, 2, 2);
INSERT INTO tab2 VALUES(3, 3, 3);
INSERT INTO tab2 VALUES(5, 5, 5);
SELECT * FROM tab1 NATURAL JOIN tab2;
A | B | C | D | F
---+---+---+---+---
2 | 2 | 2 | 2 | 2
3 | 3 | 3 | 3 | 3
(2 rows)
--类型不匹配，报错
CREATE TABLE tab3(a INT, b INT, c INT);
CREATE TABLE tab4(a DATE, d INT, f INT);
SELECT * FROM tab3 NATURAL JOIN tab4;
ERROR: (42804) JOIN/USING types integer and date cannot be matched