笔记系列----sql复杂查询值一：多表连接技术

1.简单查询的解析方法：

全表扫描：指针从第一行开始，逐行处理，直到最后一行

横向选择+纵向投影=结果集

2.多表连接

交叉连接（笛卡尔积）

非等值连接（内连）

等值连接  内连

外连接   内连扩展，左外，右外，全连接

自连接

自然连接   内连，隐含条件连接，自动匹配连接字段

集合运算，多个结果集进行并、交、差

准备数据

SQL> select * from a
  2  ;

	ID NAME
---------- ----------
	 1 a
	 2 b
	 2 c
	 4 d

SQL> select * from b;

	ID LOC
---------- ----------
	 1 A
	 2 B
	 3 C

2.1交叉连接  笛卡尔积

两个表所有连接

SQL> select * from a cross join b;   sql99

	ID NAME 	      ID LOC
---------- ---------- ---------- ----------
	 1 a		       1 A
	 2 b		       1 A
	 2 c		       1 A
	 4 d		       1 A
	 1 a		       2 B
	 2 b		       2 B
	 2 c		       2 B
	 4 d		       2 B
	 1 a		       3 C
	 2 b		       3 C
	 2 c		       3 C

	ID NAME 	      ID LOC
---------- ---------- ---------- ----------
	 4 d		       3 C

12 rows selected.

SQL> select * from a,b;   oracle

	ID NAME 	      ID LOC
---------- ---------- ---------- ----------
	 1 a		       1 A
	 2 b		       1 A
	 2 c		       1 A
	 4 d		       1 A
	 1 a		       2 B
	 2 b		       2 B
	 2 c		       2 B
	 4 d		       2 B
	 1 a		       3 C
	 2 b		       3 C
	 2 c		       3 C

	ID NAME 	      ID LOC
---------- ---------- ---------- ----------
	 4 d		       3 C

12 rows selected.

非等值连接：

也是内连

查看员工工资处于哪个等级，此时使用非等值连接非常合适

SQL> select empno,ename,sal,grade,losal,hisal from emp,salgrade where sal between losal and hisal;  oracle写法

     EMPNO ENAME	     SAL      GRADE	 LOSAL	    HISAL
---------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH	     800	  1	   700	     1200
      7900 JAMES	     950	  1	   700	     1200
      7876 ADAMS	    1100	  1	   700	     1200
      7521 WARD 	    1250	  2	  1201	     1400
      7654 MARTIN	    1250	  2	  1201	     1400
      7934 MILLER	    1300	  2	  1201	     1400
      7844 TURNER	    1500	  3	  1401	     2000
      7499 ALLEN	    1600	  3	  1401	     2000
      7782 CLARK	    2450	  4	  2001	     3000
      7698 BLAKE	    2850	  4	  2001	     3000
      7566 JONES	    2975	  4	  2001	     3000

     EMPNO ENAME	     SAL      GRADE	 LOSAL	    HISAL
---------- ---------- ---------- ---------- ---------- ----------
      7788 SCOTT	    3000	  4	  2001	     3000
      7902 FORD 	    3000	  4	  2001	     3000
      7839 KING 	    5000	  5	  3001	     9999

14 rows selected.

salgrade表和emp表无公共字段，无法做等值连接

2.2 等值连接，典型的内连接

select * from a inner join b on a.id=b.id;  sql99

select * from a,b where a.id=b.id; oracle

结果如下：

	ID NAME 	      ID LOC
---------- ---------- ---------- ----------
	 1 a		       1 A
	 2 b		       2 B
	 2 c		       2 B

2.3外连接包括左外连接，右外连接，全外连接

1）左外连接

以左为主，左表不会丢数据

select * from a left join b on a.id=b.id;    sql 99

select * from a,b where a.id=b.id(+);  oracle   补全在右边，左边数据不丢失

结果如下：

	ID NAME 	      ID LOC
---------- ---------- ---------- ----------
	 1 a		       1 A
	 2 c		       2 B
	 2 b		       2 B
     4 d

2）右外连接

以右为主，右表数据不丢失

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

select * from a,b where a.id(+)=b.id;  右边数据不丢失，左边丢失，补全左边

	ID NAME 	      ID LOC
---------- ---------- ---------- ----------
	 1 a		       1 A
	 2 b		       2 B
	 2 c		       2 B
			           3 C

3）全外连接

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

	ID NAME 	      ID LOC
---------- ---------- ---------- ----------
	 1 a		       1 A
	 2 b		       2 B
	 2 c		       2 B
	 4 d
			           3 C

select * from a,b where a.id(+)=b.id

union

select * from a,b where a.id=b.id(+);

SQL> 
select * from a,b where a.id(+)=b.id
union
select * from a,b where a.id=b.id(+); 

	ID NAME 	      ID LOC
---------- ---------- ---------- ----------
	 1 a		       1 A
	 2 b		       2 B
	 2 c		       2 B
	 4 d
			           3 C

2.4 自连接

select * from a cross join a;

select * from a a1,a a2;

SQL> select * from a cross join a;  //结果与oracle写法不一致

select * from a a1,a a2;
        ID NAME               ID NAME
---------- ---------- ---------- ----------
         1 a                   1 a
         1 a                   1 a
         1 a                   1 a
         1 a                   1 a
         2 b                   2 b
         2 b                   2 b
         2 b                   2 b
         2 b                   2 b
         2 c                   2 c
         2 c                   2 c
         2 c                   2 c

        ID NAME               ID NAME
---------- ---------- ---------- ----------
         2 c                   2 c
         4 d                   4 d
         4 d                   4 d
         4 d                   4 d
         4 d                   4 d

16 rows selected.

SQL> select * from a a1,a a2;

        ID NAME               ID NAME
---------- ---------- ---------- ----------
         1 a                   1 a
         1 a                   2 b
         1 a                   2 c
         1 a                   4 d
         2 b                   1 a
         2 b                   2 b
         2 b                   2 c
         2 b                   4 d
         2 c                   1 a
         2 c                   2 b
         2 c                   2 c

        ID NAME               ID NAME
---------- ---------- ---------- ----------
         2 c                   4 d
         4 d                   1 a
         4 d                   2 b
         4 d                   2 c
         4 d                   4 d

16 rows selected.

2.5 自然连接（属于内连中的等值连接）

oracle中的natural join

select * from a natural join b;

SQL> select * from a natural join b;

        ID NAME       LOC
---------- ---------- ----------
         1 a          A
         2 b          B
         2 c          B

在两表中再添加一个列ABC，此时公共列有2个，添加数据后，再尝试自然连接。

select * from a;

select * from b;

select * from a natural join b;

SQL> alter table a add abc char(2);

Table altered.

SQL> alter table b add abc char(2);

Table altered.

SQL> update a set abc='s' where name='a';

1 row updated.

SQL> update a set abc='t' where name='b';

1 row updated.

SQL> update a set abc='u' where name='c';

1 row updated.

SQL>  update a set abc='v' where name='d';

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from b;

        ID LOC        AB
---------- ---------- --
         1 A
         2 B
         3 C

SQL> update b set abc='w' where id=1;

1 row updated.

SQL> update b set abc='t' where id=2;

1 row updated.

SQL>  update b set abc='r' where id=3;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from b;

        ID LOC        AB
---------- ---------- --
         1 A          w
         2 B          t
         3 C          r

SQL> select * from a;

        ID NAME       AB
---------- ---------- --
         1 a          s
         2 b          t
         2 c          u
         4 d          v

SQL> alter table a modify abc char(3);

Table altered.

SQL> alter table b modify abc char(3);

Table altered.

SQL> select * from b;

        ID LOC        ABC
---------- ---------- ---
         1 A          w
         2 B          t
         3 C          r

SQL> select * from a;

        ID NAME       ABC
---------- ---------- ---
         1 a          s
         2 b          t
         2 c          u
         4 d          v

SQL> select * from a natural join b;

        ID ABC NAME       LOC
---------- --- ---------- ----------
         2 t   b          B

在自然连接中可使用using关键字：

oracle中使用natural join时，会自动将所有相同名称和数据类型匹配；若名称相同，类型不通，或者当需要在多个字段同时满足连接条件的情况下，想认为指定某个（些）字段做连接，那么可以使用using关键字。

在oracle 连接join中使用using关键字

select id ,a.abc,name,loc from a join b using(id);  //loc可以写成b.loc，oracle不要去a或b表中去找信息，有时候优化需要注意这种地方

using中也可以两列

select id ,abc,name,loc from a join b using(id,abc);

SQL>  select id,a.name,b.loc from a join b using(id);

        ID NAME       LOC
---------- ---------- ----------
         1 a          A
         2 b          B
         2 c          B

SQL> select * from a join b using(id);

        ID NAME       ABC LOC        ABC
---------- ---------- --- ---------- ---
         1 a          s   A          w
         2 b          t   B          t
         2 c          u   B          t

SQL> select id,abc,a.name,b.loc from a join b using(id,abc);

        ID ABC NAME       LOC
---------- --- ---------- ----------
         2 t   b          B

SQL> select a.id,a.name,b.loc from a join b using(id);
select a.id,a.name,b.loc from a join b using(id)
       *
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier

总结：

1.使用using关键字时，若select结果列表中包含using关键字指明的列，则不需要在select后指明该关键字属于哪个表

2.using 中可以指定多个列名。

3.natural 和using关键字是互斥的，即不能同时出现。

实际工作中，内连接，左外连接，以及自然连接用的多，且两表连接时一般时多对一的情况居多，

三、集合运算

union 并，不包括重复行，默认规则排序

union all，并，包含重复行，不进行排序

intersect 交，不包括重复行，同时默认规则排序

minus，差，不包括重复行，同时进行默认规则的排序。

create table dept1 as select * from dept where rownum<=1;

insert into dept1 values (80,'marry','beijing');

select * from dept;

select * from dept1;

SQL> create table dept1 as select * from dept where rownum<=1;

Table created.

SQL> insert into dept1 values (80,'marry','beijing');

1 row created.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select * from dept1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        80 marry          beijing

3.1union

select * from dept

union

select * from dept1;

SQL> select * from dept union select * from dept1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        80 marry          beijing

3.2 union all

select * from dept

union all

select * from dept1;

SQL> select * from dept union all select * from dept1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        10 ACCOUNTING     NEW YORK
        80 marry          beijing

6 rows selected.

特别注意：可以看出只有union all结果不排序的（不排序就会出现“6 rows selected”.）

3.3 intersect

select * from dept

intersect

select * from dept1;

SQL> select * from dept intersect select * from dept1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

3.4 minus

select * from dept

minus

select * from dept1;

select * from dept1

minus

select * from dept;

SQL> select * from dept minus select * from dept1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select * from dept1 minus select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        80 marry          beijing

4.集合运算中几点注意事项

1）集合运算中列明不必相同，但要类型匹配且顺序对应，大类型对上。如char对varchar2，date对timestamp都可，字段数需等同，不等同需补全。

create table

2)多表可以符合集合运算，四种运算符按自然先后顺序，若有特殊要求可使用（）

3）关于order by使用别名排序的问题；

a）缺省情况下按所有字段的组合进行排序的

若不希望缺省排序，也可以使用order by显示排序

b）显式order by是参照第一个select语句的列元素。故order by后的列名只能是第一个select使用的列名，别名，列号

如果补全的null值需要orderby，需要使用别名

select id_a,name_a name,to_number(null) from a

union

select id_b,name_b name,sal from b

order by sal;

ORA-00904:'sal'标识符无效

select id_a,name_a name,t

o_number(null) from a

union

select id_b,name_b name,sal from b

order by 3；

//可正常排序

select id_b,name_b name,sal from b

union

select id_a,name_a name,to_number(null) from a

order by sal;

//正常

select id_a,name_a name,to_number(null) aa  from a

union

select id_b,name_b name,sal aa from b

order by aa;

4)排序是对结果集的排序（包括符合集合运算），不能分别对个别表排序，order by只能出现一次且在最后一行；