SQL连接查询

使用连接查询时，必须在from子句中指定两个或两个以上的表。连接查询又称多表查询。

使用连接查询时应该在列名前加表名作前缀。如果不同表之间列名不同，可以不加表名前缀，如果不同表存在同名列，则必须加前缀。

 

1、笛卡尔积

当连接条件无效或被忽略，以及第一个表中所有行和第二个表中所有行都发生连接时，所有的行的组合都出现，这种结果称为笛卡尔积。

 

（1）emp表记录总数14

SQL> select count(*) from emp;

 

  COUNT(*)

----------

14

（2）dept表记录总数4

SQL> select count(*) from dept;

 

  COUNT(*)

----------

4

（3）笛卡尔积emp×dept记录总数14*4=56

SQL>select ename,dname from emp,dept;

 

 

2、等值连接

    两张表中必须有相等的列值，一般作为Where子句的条件，连接运算符为“=”，通常这样的条件包含一个主键和一个外键。

语法：

selecttable1.column,table2.column

from table1,table2

wheretable1.column1=table2.column2

 

使用相等连接执行主从查询。

 

例1：查询雇员姓名、工资以及所在部门名称。（不包含相同列名）

SQL> select ename,sal,dname from emp,dept whereemp.deptno=dept.deptno;

 

ENAME           SAL DNAME

---------- --------- --------------

SMITH        800.00 RESEARCH

ALLEN       1600.00 SALES

WARD        1250.00 SALES

JONES       2975.00 RESEARCH

MARTIN      1250.00 SALES

BLAKE       2850.00 SALES

CLARK       2450.00 ACCOUNTING

SCOTT       3000.00 RESEARCH

KING        5000.00 ACCOUNTING

TURNER      1500.00 SALES

ADAMS       1100.00 RESEARCH

JAMES        950.00 SALES

FORD        3000.00 RESEARCH

MILLER      1300.00 ACCOUNTING

 

例2：查询雇员姓名、工资、所在部门名称和部门编号。（包含相同列名）

SQL> select ename,sal,dname,dept.deptno

fromemp,dept

whereemp.deptno=dept.deptno;

 

ENAME            SAL DNAME          DEPTNO

---------- --------- -------------- ------

SMITH        800.00 RESEARCH           20

ALLEN       1600.00 SALES              30

WARD        1250.00 SALES              30

JONES       2975.00 RESEARCH           20

MARTIN      1250.00 SALES              30

BLAKE       2850.00 SALES              30

CLARK       2450.00 ACCOUNTING         10

SCOTT       3000.00 RESEARCH           20

KING        5000.00 ACCOUNTING         10

TURNER      1500.00 SALES              30

ADAMS       1100.00 RESEARCH           20

JAMES        950.00 SALES              30

FORD        3000.00 RESEARCH           20

MILLER      1300.00 ACCOUNTING         10

 

例3：查询雇员姓名、工资、所在部门名称和部门编号。（使用表别名）

SQL> select ename,sal,dname,d.deptno from empe,dept d where e.deptno=d.deptno;

 

ENAME           SAL DNAME          DEPTNO

---------- --------- -------------- ------

SMITH        800.00 RESEARCH           20

ALLEN       1600.00 SALES              30

WARD        1250.00 SALES              30

JONES        2975.00 RESEARCH           20

MARTIN      1250.00 SALES              30

BLAKE       2850.00 SALES              30

CLARK       2450.00 ACCOUNTING         10

SCOTT       3000.00 RESEARCH           20

KING        5000.00 ACCOUNTING         10

TURNER       1500.00 SALES              30

ADAMS       1100.00 RESEARCH           20

JAMES        950.00 SALES              30

FORD        3000.00 RESEARCH           20

MILLER      1300.00 ACCOUNTING         10

 

练习1：查询销售部门的员工姓名、工作、部门名称和部门号（不使用表别名）

练习2：查询销售部门的员工姓名、工作、部门名称和部门号（使用表别名）

 

3、不等值连接

等值连接运算符为“=”，如果采用其它运算符则称非等值连接。

SQL> select * from salgrade;

 

    GRADE      LOSAL      HISAL

---------- ---------- ----------

        1        700       1200

        2       1201       1400

        3       1401       2000

        4       2001       3000

        5       3001       9999

 

工资等级表（salgrade）中定义了5级工资等级，每个等级规定了最低工资（losal）和最高工资（hisal），雇员的工资可以参照该表中记录查找出相应等级。观察emp表和salgrade表发现两张表没有等值列，但是emp表sal取值必须在salgrade表的losal和hisal之间。这种关系必须使用不同于等号的运算符。

 

例：根据salgrade表的数据查询雇员的工资等级。

SQL> select ename,sal,grade

from emp,salgrade 

where sal between losal and hisal;

 

ENAME           SAL      GRADE

---------- --------- ----------

SMITH        800.00          1

JAMES        950.00          1

ADAMS       1100.00          1

WARD         1250.00          2

MARTIN      1250.00          2

MILLER      1300.00          2

TURNER      1500.00          3

ALLEN       1600.00          3

CLARK       2450.00          4

BLAKE       2850.00          4

JONES       2975.00          4

SCOTT        3000.00          4

FORD        3000.00          4

KING        5000.00          5

 

练习：根据salgrade表的数据查询雇员的工资等级，并显示该等级最低工资和最高工资。

 

4、自连接

把某个表和其自身相连接。

 

例：查询每个雇员的姓名、上级主管的姓名、职务和工资。

SQL> select worker.enameas雇员,

      manager.enameas主管,

      manager.jobas主管职务,

      manager.salas主管工资

from empworker,emp manager

whereworker.mgr=manager.empno;

 

雇员      主管       主管职务    主管工资

---------- ---------- ---------   ---------

SMITH     FORD       ANALYST     3000.00

ALLEN     BLAKE      MANAGER     2850.00

WARD      BLAKE      MANAGER     2850.00

JONES     KING       PRESIDENT   5000.00

MARTIN    BLAKE      MANAGER     2850.00

BLAKE     KING       PRESIDENT   5000.00

CLARK     KING       PRESIDENT   5000.00

SCOTT     JONES      MANAGER     2975.00

TURNER    BLAKE      MANAGER     2850.00

ADAMS     SCOTT      ANALYST     3000.00

JAMES     BLAKE      MANAGER     2850.00

FORD      JONES      MANAGER     2975.00

MILLER    CLARK      MANAGER     2450.00

 

练习1：查询部门名称为“SALES”的部门每个员工的姓名、主管姓名、主管职务、主管工资。

SQL> select worker.enameas雇员,

      manager.enameas主管,

      manager.jobas主管职务,

      manager.salas主管工资

from empworker,emp manager,dept

whereworker.mgr=manager.empno

      andworker.deptno=dept.deptno

      anddept.dname='SALES';

 

雇员      主管       主管职务    主管工资

---------- ---------- --------- ---------

ALLEN     BLAKE      MANAGER     2850.00

WARD      BLAKE      MANAGER     2850.00

MARTIN    BLAKE      MANAGER     2850.00

BLAKE     KING       PRESIDENT   5000.00

TURNER    BLAKE      MANAGER    2850.00

JAMES     BLAKE      MANAGER     2850.00

 

练习2：查询每个员工的姓名、主管姓名、主管职务、主管工资和主管工资等级。

SQL> select worker.enameas雇员,

      manager.enameas主管,

      manager.jobas主管职务,

      manager.salas主管工资,

      salgrade.gradeas主管工资等级

from empworker,emp manager,salgrade

whereworker.mgr=manager.empno

      andmanager.salbetween salgrade.losalandsalgrade.hisal;

 

雇员      主管       主管职务    主管工资       主管工资等级

---------- ---------- --------- ---------------------

CLARK     KING       PRESIDENT   5000.00            5

BLAKE     KING       PRESIDENT   5000.00            5

JONES     KING       PRESIDENT   5000.00            5

ADAMS     SCOTT      ANALYST     3000.00            4

SMITH     FORD       ANALYST     3000.00            4

FORD       JONES     MANAGER     2975.00            4

SCOTT     JONES      MANAGER     2975.00            4

MARTIN    BLAKE      MANAGER     2850.00            4

TURNER    BLAKE      MANAGER     2850.00            4

JAMES     BLAKE      MANAGER     2850.00           4

WARD      BLAKE      MANAGER     2850.00            4

ALLEN     BLAKE      MANAGER     2850.00            4

MILLER    CLARK      MANAGER     2450.00            4

 

5、内连接和外连接

内连接返回满足条件的记录，外连接返回满足和不满足条件的记录。等值连接是内连接，外连接包含左外连接、右外连接和完全外连接。

 

语法：

selecttable1.column,table2.column

from table1[inner|left|right|full]join table2

ontable1.column1=table2.column2

 

5.1 内连接

显示table1表和table2表中满足列相等条件的共同记录。

例：查询部门编号为10的雇员姓名和部门名称。

SQL> select emp.ename,dept.dname,dept.deptno

from deptinner join emp

on dept.deptno=emp.deptnoand dept.deptno=10;

 

ENAME     DNAME          DEPTNO

---------- -------------- ------

CLARK     ACCOUNTING         10

KING      ACCOUNTING         10

MILLER    ACCOUNTING         10

 

练习：查询部门名称为“SALES”的部门每个员工的姓名、工资。

 

5.2、左外连接

显示table1表和table2表满足列相等条件的共同记录，以及左边table1表的其它记录。

例1：显示部门编号为10的部门名称、雇员名称，以及其它部门名称。

SQL>select dname,ename

from deptleft join emp

ondept.deptno=emp.deptnoand dept.deptno=10;

 

DNAME         ENAME

-------------- ----------

ACCOUNTING    CLARK

ACCOUNTING    KING

ACCOUNTING    MILLER

RESEARCH      

SALES         

OPERATIONS

 

例2：使用（+）操作符执行左外连接。

（+）操作符要放在行数较少的一端。本例中部门多雇员少，（+）放在雇员一边。

SQL>select dname,ename

     from dept,emp

     wheredept.deptno=emp.deptno(+)andemp.deptno(+)=10;

 

练习：查询部门名称为“SALES”的部门名称、雇员名称，以及其它部门名称。（使用两种方法）

 

5.3、右外连接

显示table1表和table2表满足列相等条件的共同记录，以及右边table2表的其它记录。

例1：显示部门编号为10的部门名称、雇员名称，以及其它雇员姓名。

SQL> select dname,ename

     from deptrightjoin emp

     ondept.deptno=emp.deptnoand dept.deptno=10;

 

DNAME         ENAME

-------------- ----------

              SMITH

              ALLEN

              WARD

              JONES

              MARTIN

              BLAKE

ACCOUNTING    CLARK

              SCOTT

ACCOUNTING    KING

              TURNER

              ADAMS

               JAMES

              FORD

ACCOUNTING    MILLER

 

例2：使用（+）操作符执行右外连接。

（+）操作符要放在行数较少的一端。本例中雇员多部门少，（+）放在部门一边。

SQL>select dname,ename

     from dept,emp

     wheredept.deptno(+)=emp.deptnoanddept.deptno(+)=10;

 

 

练习：查询部门名称为“SALES”的部门名称、雇员名称，以及其它雇员姓名。（使用两种方法）

 

5.4、完全外连接

显示table1表和table2表满足列相等条件的共同记录，以及table1表和table2表的其它记录。

例：显示部门编号为10的部门名称、雇员名称，以及其它部门名称和其它雇员姓名。

SQL>select dname,ename

     from deptfulljoin emp

     ondept.deptno=emp.deptnoand dept.deptno=10;

 

DNAME         ENAME

-------------- ----------

ACCOUNTING    CLARK

ACCOUNTING    KING

ACCOUNTING    MILLER

RESEARCH      

SALES         

OPERATIONS    

              SMITH

              ALLEN

              WARD

              JONES

              MARTIN

              BLAKE

               SCOTT

              TURNER

              ADAMS

              JAMES

              FORD

 

练习：查询部门名称为“SALES”的部门名称、雇员名称，以及其它雇员姓名和其它部门的名称。