join方式

在ORACLE数据库中，表与表之间的SQL JOIN方式有多种（不仅表与表，还可以表与视图、物化视图等联结），官方的解释如下所示

A join is a query that combines rows from two or more tables, views, or materialized views. Oracle Database performs a join whenever multiple tables appear in the FROM clause of the query. The select list of the query can select any columns from any of these tables. If any two of these tables have a column name in common, then you must qualify all references to these columns throughout the query with table names to avoid ambiguity.

SQL JOIN 归纳起来有下面几种方式，下面一起来梳理一下这些概念。SQL JOIN其实是一个逻辑概念，像NEST LOOP JOIN、 HASH JOIN等是表连接的物理实现方式。

clip_image001

我们先准备一个两个测试表M与N（仅仅是为了演示需要），如下脚本所示

SQL> CREATE TABLE M
2 (
3 NAME VARCHAR2(12)
4 ,SEX VARCHAR2(6)
5 );

Table created.

SQL> CREATE TABLE N
2 (
3 NAME VARCHAR2(12)
4 ,GRADE NUMBER(2)
5 );

Table created.

SQL> INSERT INTO M
2 SELECT ‘kerry’, ‘male’ FROM DUAL UNION ALL
3 SELECT ‘jimmy’, ‘male’ FROM DUAL UNION ALL
4 SELECT ‘tina’ , ‘female’ FROM DUAL UNION ALL
5 SELECT ‘wendy’, ‘female’ FROM DUAL;

4 rows created.

SQL> COMMIT;

Commit complete.

SQL> INSERT INTO N
2 SELECT ‘kerry’, 3 FROM DUAL UNION ALL
3 SELECT ‘jimmy’, 2 FROM DUAL UNION ALL
4 SELECT ‘ken’ , 6 FROM DUAL UNION ALL
5 SELECT ‘richard’,5 FROM DUAL;

4 rows created.

SQL> COMMIT;

Commit complete.
内连接：INNER JOIN

INNER JOIN 它表示返回两个表或记录集连接字段的匹配记录。如下所示，INNER JOIN 可以有三种实现方式：

SQL> SELECT M.NAME, M.SEX, N.GRADE
2 FROM M INNER JOIN N ON M.NAME=N.NAME;

NAME SEX GRADE

kerry male 3
jimmy male 2

SQL> SELECT M.NAME, M.SEX, N.GRADE
2 FROM M, N
3 WHERE M.NAME=N.NAME;

NAME SEX GRADE

kerry male 3
jimmy male 2

第三种方式，使用USING，如下所示，这种写法一般较少人使用。

SQL> SELECT NAME, M.SEX,N.GRADE
2 FROM M INNER JOIN N USING(NAME);

NAME SEX GRADE

kerry male 3
jimmy male 2

SQL>

注意，INNER JOIN可以用使用简写JOIN方式，如下所示，但是建议使用INNER JOIN 而不是JOIN这样的语法。

如果我们用韦恩图来解释INNER JOIN，则非常一目了然、形象生动。可以用下面图来表示（此图以及下面的韦恩图均来自链接http://pafumi.net/SQL_Joins.html ，本来想自己画，无奈有些图使用word不好实现，R语言不会。故在此借其图用用）

外连接：OUTER JOIN

1 全连接：full join

全连接 ：包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。不符合条件的，以空值代替。如下所示：

SQL> SELECT M.NAME, N.NAME, M.SEX, N.GRADE
2 FROM M FULL OUTER JOIN N ON M.NAME=N.NAME;

NAME NAME SEX GRADE

kerry kerry male 3
jimmy jimmy male 2
ken 6
richard 5
tina female
wendy female

6 rows selected.
clip_image005

FULL OUTER JOIN的韦恩图如下所示：

clip_image006

2 左外连接：LEFT JOIN

左外连接又叫左连接 ：意思是包含左边表所有记录，右边所有的匹配的记录，如果没有则用空补齐。换句话说就是，列出左边表全部的，及右边表符合条件的，不符合条件的以空值代替。

SQL> SELECT M.NAME, N.NAME, M.SEX, N.GRADE
2 FROM M LEFT OUTER JOIN N ON M.NAME=N.NAME;

NAME NAME SEX GRADE

kerry kerry male 3
jimmy jimmy male 2
tina female
wendy female

SQL> SELECT M.NAME, N.NAME, M.SEX, N.GRADE
2 FROM M LEFT JOIN N ON M.NAME=N.NAME;

NAME NAME SEX GRADE

kerry kerry male 3
jimmy jimmy male 2
tina female
wendy female

在ORACLE 9i以及之前，使用在(+)来表示左连接，哪个带(+)哪个需要条件符合的，另一个全部的。即放左表示右连接，放右表示左连接。这种写法，如果不熟悉，就会有点陌生。其实也不是什么新鲜事物，只是你不太熟悉而已。

SQL> SELECT M.NAME, N.NAME, M.SEX, N.GRADE
2 FROM M, N
3 WHERE M.NAME=N.NAME(+);

NAME NAME SEX GRADE

kerry kerry male 3
jimmy jimmy male 2
tina female
wendy female
SQL>
clip_image007

LEFT OUTER JOIN的韦恩图如下所示：

clip_image008
create table l as
select ‘left_1’ as str,’1’ as v from dual union all
select ‘left_2’ ,’2’ as v from dual union all
select ‘left_3’ ,’3’ as v from dual union all
select ‘left_4’ ,’4’ as v from dual ;

create table r as
select ‘right_3’ as str,’3’ as v, 1 as status from dual union all
select ‘right_4’ as str,’4’ as v, 0 as status from dual union all
select ‘right_5’ as str,’5’ as v, 0 as status from dual union all
select ‘right_6’ as str,’6’ as v, 0 as status from dual ;

select * from l; –where l.v not in (select r.v from r )

select * from r

–1. inner join 返回两表相匹配的数据 即 交集
select l.str as left_str,r.str as right_str from l
inner join r on l.v=r.v
order by 1,2;

select l.str as left_str,r.str rigth_str from l,r
where l.v=r.v
order by 1,2;

–2.left join 左表为主表，左表返回全部数据，右表只返回与左表相匹配的数据
select l.str as left_str,r.str right_str from l
left join r on l.v=r.v
order by 1,2;

select l.str as left_str,r.str as right_str from l,r
where l.v=r.v(+)
order by 1,2;

select l.str as left_str,r.str as right_str from l,r
where l.v=r.v
order by 1,2;

–3.right join 右表为主表，左表只返回与右表相匹配的数据
select l.str as left_str,r.str as right_str from l
right join r on l.v=r.v
order by 1,2;

select l.str as left_str,r.str right_str from l,r
where l.v(+)=r.v
order by 1,2;

–4. 左右表均返回全部的数据，但是只有相匹配的数据显示在一行，非匹配的数据只显示一个表的数据
select l.str as left_str,r.str as right_str from l
full join r on r.v=l.v
order by 1,2;

最后

以上就是诚心蜜蜂为你收集整理的join方式的全部内容，希望文章能够帮你解决join方式所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。