假设我们有两张表,分别是employees和customers。存储内容如下:
![employees](https://i-blog.csdnimg.cn/blog_migrate/caf659a1e6f7af92fd8cae6a7fa9c097.png)
![](https://i-blog.csdnimg.cn/blog_migrate/34f83cc1f671884b2d7479efd5c1a3fb.png)
1. 内连接
select …… from 表1 inner join 表2 on 表1.A=表2.B
e.g:
select * from employees inner join customers on empID=custID;
2. 自然连接
select …… from 表1 natural join 表2
等同于
select …… from 表1 inner join 表2 on 表1.A=表2.B
e.g:
select * from employees natural join customers;
自然连接会根据名称一样的属性自动链接,无需添加连接条件。而内连接可以自己指定属性。
3. 外连接
左外:
select …… from 表1 left join 表2 on 表1.A=表2.B
左外会完整保留表1
右外:
select …… from 表1 right join 表2 on 表1.A=表2.B
右外会完整保留表2
e.g:
select firstName, lastName, custID, customers.city from employees left join customers on employees.city=customers.city
select firstName, lastName, custID, customers.city from employees right join customers on employees.city=customers.city;
与内连接对比:
select firstName, lastName, custID, customers.city from employees inner join customers on employees.city=customers.city;
内连接会将不符合匹配条件的元组舍弃,而外连接会保留。
4. Union
将两次select查询得到的表结合。要求:1)列数相同。2)数据类型相同。3)顺序相同。
Union得到的元组们是无序的。
e.g:
SELECT firstName, lastName, custID, customers.city
FROM employees
LEFT JOIN customers
ON employees.city = customers.city
UNION
SELECT firstName, lastName, custID, customers.city
FROM employees
RIGHT JOIN customers
ON employees.city = customers.city;