数据库的各种表连接操作

数据库的各种表连接操作

1、内连接——INNER JOIN

inner join主要是获取两个或多个表的交集，只返回匹配的行；多张表之间没有主表附表之分。

--规范写法
select column_list 
from table1 t1
inner join table2 t2 on t1.column_name = t2.column_name

--常用写法1
select column_list 
from table1 t1
join table2 t2 on t1.column_name = t2.column_name

--常用写法2
select column_list 
from table1 t1, table2 t2
where t1.column_name = t2.column_name

2、左外部连接——LEFT JOIN

left join以左表为主表，右表为附表，返回左表的全部行和右表满足on条件的行；

如果左表的行在右表中没有匹配，那么这一行右表中对应数据用NULL代替；

可以是一主一附(一个左表，一个右表)，也可以是一主多附(一个左表，多个右表)。

--规范写法
select column_list 
from table1 t1
left join table2 t2 on t1.column_name = t2.column_name

--常用写法
select column_list 
from table1 t1
left outer join table2 t2 on t1.column_name = t2.column_name

3、右外部连接——RIGHT JOIN

right join以右表为主表，左表为附表，返回右表的全部行和左表满足on条件的行；

如果右表的行在左表中没有匹配，那么这一行左表中的对应数据用NULL代替。

--规范写法
select column_list 
from table1 t1
right join table2 t2 on t1.column_name = t2.column_name

--常用写法
select column_list 
from table1 t1
right outer join table2 t2 on t1.column_name = t2.column_name

4、全连接——FULL JOIN

full join 会从左表和右表返回符合条件的所有行；

只要左表(table 1)和右表(table 2)其中一个表中存在匹配，就返回结果；

如果其中一个表的数据行在另一个表中没有匹配的行，那么对面的数据用NULL代替；

FULL JOIN 结合了 LEFT JOIN 和 RIGHT JOIN 的结果;

SQLServer 和Oracle支持FULL JOIN；

MySQL不支持FULL JOIN,可以使用LEFT JOIN + UNION + RIGHT JOIN来达到效果

--规范写法
select column_list 
from table1 t1
full join table2 t2 on t1.column_name = t2.column_name

--规范写法
select column_list 
from table1 t1
full outer join table2 t2 on t1.column_name = t2.column_name

5、交叉连接——CROSS JOIN

FULL JOIN 返回左表中的所有行，左表中的每一行与右表中的所有行组合；

交叉联接也称作笛卡尔积；

如果table 1有5行记录，table 2有6行记录，交叉连接结果为5*6=30条记录；

--规范写法
select column_list 
from table1 t1
cross join table2 t2

--根据on条件来过滤
select column_list 
from table1 t1
cross join table2 t2 on t1.column_name = t2.column_name

6、自然连接——NATURAL JOIN

NATURAL JOIN 等同于INNER JOIN或INNER USING 匹配两个表中相同名称的列；

具有相同名称的关联表的列将仅出现一次;

关联的表具有一对或多对相同名称的列，这些列必须是相同的数据类型;

不要在自然连接中使用ON子句;

--规范写法
select column_list 
from table1 t1
natural join table2 t2

7、自连接——SELF JOIN

SELF JOIN 是表与自身联接(一元关系)的连接，特别是当表具有引用其自身主键的外键时；

连接表本身意味着表的每一行都与自身以及表的其他每一行结合在一起；

自连接可以看作是同一表的两个副本的连接；

--规范写法
select column_list 
from table t1, table t2
where t1.column_name = t2.column_name

select column_list 
from table t1
inner join table t2 on t1.column_name = t2.column_name

8、联合查询

UNION 操作符用于合并两个或多个 SELECT 语句的结果集;

UNION 内部的每个 SELECT 语句必须拥有相同数量的列，相似的数据类型，相同的列顺序；

--union默认选取不同的值
select column_list
from table1 
union select column_list from table2

--union all允许选取重复的值
select column_list
from table1
union all select column_list from table2