SQL表间关联查询详解

简介

本文主要讲解SQL语句中常用的表间关联查询方式，包括：左连接（left join）、右连接（right join）、全连接（full join）、内连接（inner join）、交叉连接（cross join）、自然连接（natural join）。

样例准备

以oracle数据库为例，现在有两张表TESTTABLEONE（简称a表）和TESTTABLETWO（简称b表），两张表分别有id和name两个字段，两张表数据如下两图所示。

两张表各有4条数据，其中，a表和b表都有id01和id02这两条数据，a表有id03和id04两条数据但b表没有，b表有id05和id06两条数据但a表没有。

左外连接

左外连接简称左连，语法为left join或left outer join，左表返回全部数据，右表返回满足连接条件的数据，对于左表存在而右表不存在的数据，右表对应的字段返回NULL。

执行以下语句：

--left join前面的a表就是左表，left join后面的b表就是右表，on的后面是连接条件，以下连接条件表示按id字段匹配。

select a.*,b.* from TESTTABLEONE a left join TESTTABLETWO b on a.id=b.id;

返回结果如下图：

左表（a表）返回全部4条数据，右表（b表）只返回了id01和id02，对于左表存在而右表不存在的id03和id04两条数据，左表返回全部数据，而右表则返回NULL。

右外连接

右外连接简称右连，语法为right join或right outer join，右表返回全部数据，左表返回满足连接条件的数据，对于右表存在而左表不存在的数据，左表对应的字段返回NULL。

执行以下语句：

--right join前面的a表就是左表，right join后面的b表就是右表，on的后面是连接条件，以下连接条件表示按id字段匹配。

select a.*,b.* from TESTTABLEONE a right join TESTTABLETWO b on a.id=b.id;

返回结果如下图：

右表（b表）返回全部4条数据，左表（a表）只返回了id01和id02，对于右表存在而左表不存在的id05和id06两条数据，右表返回全部数据，而左表则返回NULL。

全外连接

全外连接简称全连接，语法为full join或full outer join，返回左表和右表的全部数据，对于左表存在而右表不存在的数据，右表对应的字段返回NULL，对于右表存在而左表不存在的数据，左表对应的字段返回NULL。

执行以下语句：

--full join连接a表和b表，on的后面是连接条件，以下连接条件表示按id字段匹配。

select a.*,b.* from TESTTABLEONE a full join TESTTABLETWO b on a.id=b.id;

返回结果如下图：

左表和右表都返回全部4条数据，对于左表存在而右表不存在的id03和id04两条数据，右表则返回NULL，对于右表存在而左表不存在的id05和id06两条数据，左表则返回NULL。

内连接

内连接语法为inner join，只返回左表和右表满足连接条件的数据，对于左表存在而右表不存在的数据以及右表存在而左表不存在的数据，都不返回。

执行以下语句：

--inner join连接a表和b表，on的后面是连接条件，以下连接条件表示按id字段匹配。

select a.*,b.* from TESTTABLEONE a inner join TESTTABLETWO b on a.id=b.id;

以下写法的效果也相当于内连接：

--a表和b表直接用逗号隔开，把内连接的条件写到where条件里面。

select a.*,b.* from TESTTABLEONE a,TESTTABLETWO b where a.id=b.id;

返回结果如下图：

只返回左表和右表都存在的数据：id01和id02。

交叉连接

交叉连接语法为cross join，不带连接条件，即没有on的部分，返回左表和右表的笛卡尔积，即左表所有数据与右表所有数据组合。

执行以下语句：

--cross join连接a表和b表，不带连接条件

select a.*,b.* from TESTTABLEONE a cross join TESTTABLETWO b;

返回结果如下图：

总共返回16条数据，即左表4条数据乘以右表4条数据。

交叉连接可以加where条件，可以起到内连接的效果。

执行以下语句：

--cross join加上where条件（a.id=b.id），相当于inner join。

select a.*,b.* from TESTTABLEONE a cross join TESTTABLETWO b where a.id=b.id;

返回结果如下图：

只返回左表和右表都存在的数据：id01和id02。

自然连接

自然连接语法为natural join，不带显示连接条件，会自动对左表和右表中名称相同的字段进行连接，只返回左表和右表都存在的数据，并且，对于两张表中名称相同的连接字段，不会重复显示。

执行以下语句：

--natural join不需要带上显示的连接条件（即on后面的部分）。

select * from TESTTABLEONE a natural join TESTTABLETWO b;

返回结果如下图：

只返回左表和右表都存在的数据，并且，作为自然连接条件的ID和NAME字段没有重复显示。