1.连接（JOIN）
JOIN子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。
JOIN的五种连接类型：
- CROSS JOIN：交叉连接
- INNER JOIN：内连接
- LEFT OUTER JOIN：左外连接
- RIGHT OUT JOIN：右外连接
- FULL OUT JOIN：全外连接 ```sql —以下面两个表格作为操作对象 runoobdb=# SELECT * FROM department; id | dept | emp_id ——+——————————————————————————+———— 1 | IT Billing | 1 2 | Engineering | 2 3 | Finance | 7 (3 行记录)
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 8 | Paul | 24 | Houston | 20000 9 | James | 44 | Norway | 5000 10 | James | 45 | Texas | 5000 (10 行记录)
<a name="o6HC4"></a>### 1.1交叉连接交叉连接（CROSS JOIN）把第一个表的每一行与第二个表的每一行进行匹配，如果两个输入表分别有 x 和 y 行，则结果表有 x*y 行。<a name="RPutQ"></a>#### 1.1.1语法`SELECT ... FROM table1 CROSS JOIN table2 ...`<a name="eoare"></a>#### 1.1.2示例```sqlrunoobdb=# SELECT emp_id, name, dept FROM company CROSS JOIN department;emp_id | name | dept--------+-------+----------------------------------------------------1 | Paul | IT Billing1 | Allen | IT Billing1 | Teddy | IT Billing1 | Mark | IT Billing1 | David | IT Billing1 | Kim | IT Billing1 | James | IT Billing1 | Paul | IT Billing1 | James | IT Billing1 | James | IT Billing2 | Paul | Engineering2 | Allen | Engineering2 | Teddy | Engineering2 | Mark | Engineering2 | David | Engineering2 | Kim | Engineering2 | James | Engineering2 | Paul | Engineering2 | James | Engineering2 | James | Engineering7 | Paul | Finance7 | Allen | Finance7 | Teddy | Finance7 | Mark | Finance7 | David | Finance7 | Kim | Finance7 | James | Finance7 | Paul | Finance7 | James | Finance7 | James | Finance(30 行记录)
1.2内连接
内连接（INNER JOIN）是最常见的连接类型，是默认的连接类型，INNER也可以省略不写。
内连接（INNER JOIN）根据连接谓词结合两个表（table1 和 table2）的列值来创建一个新的结果表
查询会把 table1 中的每一行与 table2 中的每一行进行比较，找到所有满足连接谓词的行的匹配对。
1.2.1语法
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_filed = table2.common_field;
1.2.2示例
runoobdb=# SELECT emp_id,name,dept,name FROM department JOIN company ON department.emp_id = company.id;
emp_id | name | dept | name
--------+-------+----------------------------------------------------+-------
1 | Paul | IT Billing | Paul
2 | Allen | Engineering | Allen
7 | James | Finance | James
(3 行记录)
--更换一下两个表的次序，发现并不影响结果
runoobdb=# SELECT emp_id,name,dept,name FROM company JOIN department ON department.emp_id = company.id;
emp_id | name | dept | name
--------+-------+----------------------------------------------------+-------
1 | Paul | IT Billing | Paul
2 | Allen | Engineering | Allen
7 | James | Finance | James
(3 行记录)
--也就是说SELECT 后面的表是要在查询结果里面显示的表，两个表的次序可以随便写
--最后指定两个表连接的条件就行了
1.3外连接
外连接：合并具有同一列的两个以上的表的行，结果里面除了包含一个表与另一个表匹配的行之外，还包含了两个（或多个）表中不匹配的行。
外连接还包括左外连接、右外连接、满外连接
左外连接：两个表在连接过程中除了返回满足连接条件的行以外，还返回左表中不满足条件的行，这种连接称为左外连接
右外连接：两个表在连接过程中除了返回满足连接条件的行以外，还返回右表中不满足条件的行，这种连接称为右外连接
1.3.1示例
--左外连接
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 行记录)
--满足company.id = department.emp_id的只有前面三条，但是company中不满足此条件的其他数据也显示出来了
--右外连接
runoobdb=# SELECT dept,emp_id,name,address FROM department RIGHT OUTER JOIN company
runoobdb-# ON department.emp_id = company.id;
dept | emp_id | name | address
----------------------------------------------------+--------+-------+----------------------------------------------------
IT Billing | 1 | Paul | California
Engineering | 2 | Allen | Texas
Finance | 7 | James | Houston
| | James | Texas
| | David | Texas
| | Paul | Houston
| | Kim | South-Hall
| | Mark | Rich-Mond
| | Teddy | Norway
| | James | Norway
(10 行记录)
--调换两个表的顺序，把deparment表作为右表，这是查询结果应该是只有满足department.emp_id = company.id的
--和department中不满足此条件的其他数据（其实并没有不满的），department表中总共就三条数据，所以查询结果也一定是三条
runoobdb=# SELECT dept,emp_id,name,address FROM company RIGHT OUTER JOIN department
runoobdb-# ON company.id = department.emp_id;
dept | emp_id | name | address
----------------------------------------------------+--------+-------+----------------------------------------------------
IT Billing | 1 | Paul | California
Engineering | 2 | Allen | Texas
Finance | 7 | James | Houston
(3 行记录)
--我们往deparment表中插入一条数据,这条数据的emp_id和company中的所有id都不匹配
runoobdb=# INSERT INTO department VALUES(4,'哈哈',15);
INSERT 0 1
runoobdb=# SELECT * FROM department;
id | dept | emp_id
----+------------------------------------------------------+--------
1 | IT Billing | 1
2 | Engineering | 2
3 | Finance | 7
4 | 哈哈 | 15
(4 行记录)
--按照右外连接的定义，我们这次将department作为右表，得出的查询结果应该是4条数据
runoobdb=# SELECT dept,emp_id,name,address FROM company RIGHT OUTER JOIN department
runoobdb-# ON department.emp_id = company.id;
dept | emp_id | name | address
------------------------------------------------------+--------+-------+----------------------------------------------------
IT Billing | 1 | Paul | California
Engineering | 2 | Allen | Texas
Finance | 7 | James | Houston
哈哈 | 15 | |
(4 行记录)
--结果也确实如此
2.并（UNION）
2.1使用条件：
- UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（如果使用3个SELECT语句那么就要使用2个UNION关键字）
- UNION中的每个查询必须包含相同的列、表达式或聚集函数
- 列数据类型必须兼容：类型不必完全相同，但必须是数据库可以隐含的转换的类型
作用：UNION 操作符用于合并两个或多个 SELECT 语句的结果集
2.2语法
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
--就是两个语句通过UNION连接
2.3示例
runoobdb=# SELECT emp_id,name,dept FROM company INNER JOIN department
runoobdb-# ON company.id = department.emp_id
runoobdb-# UNION
runoobdb-# SELECT emp_id,name,dept FROM company LEFT OUTER JOIN department
runoobdb-# ON company.id = department.emp_id;
emp_id | name | dept
--------+-------+----------------------------------------------------
7 | James | Finance
6 | Kim | Finance
2 | Allen | Engineering
3 | Teddy | Engineering
5 | David | Engineering
4 | Mark | Finance
1 | Paul | IT Billing
(7 行记录)
3.UNION ALL
UNION会自动去除重复行，如果想显示重复行，那就选择使用UNION ALL。
使用方式和UNION一样，就是把两个SELECT语句连接起来。
--还是UNION中的示例语句，现在使用UNION ALL，发现查询结果中展示了重复的数据
runoobdb=# SELECT emp_id,name,dept FROM company INNER JOIN department
runoobdb-# ON company.id = department.emp_id
runoobdb-# UNION ALL
runoobdb-# SELECT emp_id,name,dept FROM company LEFT OUTER JOIN department
runoobdb-# ON company.id = department.emp_id;
emp_id | name | dept
--------+-------+----------------------------------------------------
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
3 | Teddy | Engineering
4 | Mark | Finance
5 | David | Engineering
6 | Kim | Finance
1 | Paul | IT Billing
2 | Allen | Engineering
7 | James | Finance
3 | Teddy | Engineering
4 | Mark | Finance
5 | David | Engineering
6 | Kim | Finance
(14 行记录)
若有收获，就点个赞吧
0 人点赞
