数据库表连接之七种Join

techbridge

1. 内连接 连接结果为左右表的交集

select * from A inner join B where A.key=B.key;

2. 左连接 连接结果含左表的全部

select * from A left join B on A.key=B.key;

3. 右连接 连接结果含右表的全部

select * from A right join B on A.key=B.key

4. 左外连接 连接结果为左表独有(右表为null)

select * from A left join B on A.key=B.key where B.key is null;

5. 右外连接 连接结果为右表独有(左表为null)

select * from A right join B on A.key=B.key where A.key is null;

6. 全外连接 连接结果为左右表全部

(MySQL) select * from A left join B where A.key=B.key union select * from A right join B where A.key=B.key;
(Oracle) select * from A full outer join B on A.key = B.key;

7. 两表独有的数据集 连接结果为左表独有和右表独有

(MySQL) select * from A left join B on A.key=B.key where B.key is null
union
select * from A right join B on A.key=B.key where A.key is null;
(Oracle)  select * from A full outer join B on A.key = B.key where A.key is null or B.key is null;