SQL: Queries: Table Expressions: Join

20th August 2020 at 2:19pm
SQL: Queries: Table Expressions

JOIN 结构的通用语法如下:

T1 <join_type> T2 [ <join_condition> ]

其中 join type 有两种:cross join 及 qualified join。

Cross join

Cross join 比较好理解。比如 JOIN 两个表,那么第一个表的每一行,都与第二个表的每一行合并成一个大行,这个行里面有两个表的全部列。假如表 1 表 2 分别有 N 和 M 行,那 join 后的虚拟表就有 NxM 行。

语法:

T1 CROSS JOIN T2

FROM T1 CROSS JOIN T2 等效于 FROM T1, T2FROM T1 INNER JOIN T2 ON TRUE

Qualified joins

语法:

T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON <boolean_expression>
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( <join column list> )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2

之所以叫 qualified,是因为这类语句中需要用 ON USING 或者隐式的 NATURAL,来指定两个表 如何关联在一起

如何关联在一起?

ON 是最常见的 join 条件,它后面带一个 boolean expression:

SELECT * FROM T1 JOIN T2 ON T1.a = T2.b;

USING 是一种简化,当用来匹配的列在两个表中同名时可使用:

SELECT * FROM T1 JOIN T2 USING (a, b);

--- equivalent to
SELECT * FROM T1 JOIN T2 ON T1.a = T2.a AND T1.b = T2.b;

NATURAL 则是对 USING 的一种简化,会把两个表中所有相同列名的列用在 USING 列表中。如果两个表没有相同列名,则它的效果就是一个 cross join。

两个表如何整合

对于在集合(数学意义上的 Set)层面上两个表如何整合,有两种形式:

  • Inner Join,表示两个表相交(intersect)
  • Outer Join,表示两个表联合(union)。对于不匹配部分如何显示的问题,有
    • Left Outer Join
    • Right Outer Join
    • Full Outer Join

其中 INNER OUTER 两个关键字是可以不写的。默认是 INNER;有 LEFT RIGHTFULL 出现时则表示是 OUTER

注意

ON 中的条件是在 JOIN 前处理的,而 WHERE 中的条件是在 JOIN 后处理的。这意味着:

  • 对于 outer join,JOIN + ON 是必须的,ON 来表达匹配规则;PG 没有对等的 WHERE 写法
  • 对于 inner join,用 JOIN + ON 或者 WHERE 来表达是一样的

写 SQL 时需注意这点。

下面的示例来自这个 SO 答案

A,B 两张表,对应有 a、b 两个字段,数据如下:

A    B
-    -
1    3
2    4
3    5
4    6

Inner join:

SELECT * FROM a INNER JOIN b ON a.a = b.b;
SELECT a.*, b.* FROM a,b WHERE a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join:

SELECT * FROM a LEFT OUTER JOIN b ON a.a = b.b;

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join:

SELECT * FROM a RIGHT OUTER JOIN b ON a.a = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join:

SELECT * FROM a FULL OUTER JOIN b ON a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5