数据库查询中的表连接

表X，n行m列

表Y，k行s列

num | name

-----+------

   1 | a

   2 | b

   3 | c

num | value

-----+-------

   1 | xxx

   3 | yyy

   5 | zzz

连接

名称

连接结果

说明

示例

行

列

交叉连接

cross join

笛卡尔集

n*k

m+s

列出数据行之间的乘积和列之间的加和；连接语句不带有连接条件

SELECT * FROM X CROSS JOIN Y;

连接结果，3行*3行=9行，2列+2列=4列：

 num | name | num | value

-----+------+-----+-------

   1 | a    |   1 | xxx

   1 | a    |   3 | yyy

   1 | a    |   5 | zzz

   2 | b    |   1 | xxx

   2 | b    |   3 | yyy

   2 | b    |   5 | zzz

   3 | c    |   1 | xxx

   3 | c    |   3 | yyy

   3 | c    |   5 | zzz

内连接

inner join

又称

simple join

n*k中满足条件的行

m+s或去除重复的列

列出与连接条件匹配的数据行

分类：

根据连接条件符号：

等值连接[1]

不等值连接[2]

重复列去除，称为自然连接[3]

SELECT * FROM X INNER JOINY

ON X.num = Y.num;

连接结果：

 num | name | num | value

-----+------+-----+-------

   1 | a    |   1 | xxx

   3 | c    |   3 | yyy

內连接有重复的num列

SELECT * FROM X NATURAL INNER JOIN Y;

连接结果：

 num | name | value

-----+------+-------

   1 | a    |  xxx

   3 | c    |  yyy

自然连接没有重复的num列

外连接

outer join

不满足连接条件的行+內连接方式得到的行

取决于“查询列”和“using”子句

返回到查询结果集合中的不止包含符合连接条件的行，而且还包括左表(左外连接[4]时)、右表(右外连接[5]时)或两个边接表(全外连接[6])中的所有数据行

SELECT * FROM X LEFT JOIN Y USING (num);

 num | name | value

-----+------+-------

   1 | a    | xxx

   2 | b    |

   3 | c    | yyy

多出第二行，不满足连接条件，但在X表中

USING子句消除了num重复列

SELECT * FROM X RIGHT JOIN Y ON X.num = Y.num;

 num | name | num | value

-----+------+-----+-------

   1 | a    |   1 | xxx

   3 | c    |   3 | yyy

     |      |   5 | zzz

多出第三行，不满足连接条件，但在Y表中

SELECT * FROM X FULL JOIN Y ON X.num = Y.num;

 num | name | num | value

-----+------+-----+-------

   1 | a    |   1 | xxx

   2 | b    |     |

   3 | c    |   3 | yyy

     |      |   5 | zzz

多出第二行，不满足连接条件，但在X表中

多出第四行，不满足连接条件，但在Y表中

连接

名称

说明

示例（源自Oracle）

半连接[7]

semi join

如TBL_A 半连接TBL_B，对TBL_A中的每一条元组tuple，如果tuple能与TBL_B中的某一条元组连接上，则停止tuple与TBL_B的连接，并将tuple作为结果返回，并继续执行TBL_A的下一条元组，如果tuple与B中任何一条都连接不成功，则不返回结果。

对于“subquery”，使用IN、EXISTS等谓词表示存在即可，称之为半连接。

SELECT * FROM departments

WHERE EXISTS

(SELECT * FROM employees

WHERE departments.department_id = employees.department_id

AND employees.salary > 2500)

ORDER BY department_name;

反半连接[8]

anti join

如TBL_A反半连接TBL_B，对TBL_A中的每一条元组tuple，如果tuple能与TBL_B中的某一条元组连接上，则停止tuple与TBL_B的连接，且不返回任何结果，TBL_A的下一条元组开始与TBL_B做连接；如果TBL_B中所有元组都不能与tuple连接上，则将tuple作为结果返回，执行TBL_A的下一条元组。

对于“subquery”，使用NOT IN谓词表示不存在即可，称之为反半连接。

SELECT * FROM employees

WHERE department_id NOT IN

(SELECT department_id FROM departments

WHERE location_id = 1700)

ORDER BY last_name;