多表连接查询

原创 2015年11月21日 15:13:13

--连接类型:

--1、内连接 inner join:

--只返回两个表中所有满足连接条件的行

--2、外连接outer join:

--除了返回两个表中所有满足连接条件的行之外,还返回

--某个表中不满足连接条件的行

--3、交叉连接

--产生笛卡尔积的连接

--如果连接条件使用等号=,就可以叫做相等连接

--1、使用natural join关键字进行内连接

--这种语法会自动地从两个表中挑选所有同名同类型的列作为连接列

--进行相等连接

 

--查询每个部门的部门编号、名称、所在位置编号以及所在城市

--名称

select department_id,department_name,

       location_id,city

  from departments

  natural join locations;

--缺点:

--1、不能自己挑选连接列

--2、代码不直观,不能看出连接条件是什么

--2、为了改进以上缺陷,可以使用using子句来做连接

--语法:using(列名),含义:使用指定的两个表中的同名列作为

--连接列进行相等连接

--查询每个部门的部门编号、名称、所在位置编号以及所在城市

--名称

select department_id,department_name,

       location_id,city

  from departments

   join locations

   using(location_id);

 

--使用using做连接,也有一个语法限制:

--using用到的连接列,在select语句的任何地方(包括using自己),

--都不允许加表别名来限定它

 

--ORA-01748: 此处只允许简单的列名

select department_id,department_name,

       location_id,city

  from departments d

   join locations l

   using(d.location_id);

 

--ORA-25154: USING 子句的列部分不能有限定词

select department_id,department_name,

       location_id,city

  from departments d

   join locations l

   using(location_id)

   where l.location_id > 1700;

 

--如果定义了表别名,那么一般都在列名前面加表别名进行限定

select d.department_id,d.department_name,

       location_id,l.city

  from departments d

   join locations l

   using(location_id);

   

--3、使用on子句指定连接条件,比using更加灵活、方便

--查询雇员的编号、姓名、所在部门号、以及部门的位置编号

selecte.employee_id,e.last_name,e.department_id,

      d.department_id,d.location_id

   from employees e

   join departments d

   on (e.department_id =d.department_id )

   where  d.location_id =1700;

 

--实际中写连接查询,老式的写法还是最多的。

--它是将所有做连接的表名都写在from子句中,然后在

--where中写连接条件

selecte.employee_id,e.last_name,e.department_id,

      d.department_id,d.location_id

   from employees e, departments d

   where e.department_id =d.department_id

     and d.location_id = 1700;

 

--老式的写法一个弊病就是:它把连接条件和过滤条件

--混写在了一起,代码可读性较差。on子句就解决了这个问题

 

--3表连接查询

--写多个join on子句

--查询雇员的编号、所在部门号、以及部门所在城市名称

selecte.employee_id,d.department_id,l.city

  from employees e

  join departments d

  on(e.department_id = d.department_id)

  join locations l

  on(l.location_id = d.location_id);

  

  

--自连接查询self join:

--一张表连接到它自己做连接查询。

--技巧:给一张表分别取两个不同的别名,看成是两张表,

--然后进行连接

 

--查询每个雇员的姓名及其对应的经理的名字

select worker.last_name,manager.last_name

  from employees worker

  join employees manager

  on (worker.manager_id =manager.employee_id);

 

--使用不等连接:连接条件不使用等号。很少见

create table job_grades(

  grade_level varchar2(1),

  lowest_sal number,

  highest_sal number

);

 

insert into job_grades 

  values('A',1000,2999);

 

insert into job_grades 

  values('B',3000,5999);

  

  insert into job_grades 

  values('C',6000,7999);

 

--查询雇员的姓名、薪水以及工资级别

select e.last_name,e.salary,j.grade_level

  from employees e

  join job_grades j

  on (e.salary between j.lowest_sal andj.highest_sal);

 

--外连接查询

--3种:

--left join:左外连接

--right join :右外连接

--full join:全外连接

 

--1)left join:左外连接

--除了返回两个表中所有满足连接条件的行之外,还返回左表中

--所有不满足连接条件的行。所谓左表,就是写在left关键字

--左边的表

 

--查询雇员的姓名、部门编号以及部门名称,要求没有部门的雇员

--也要显示

select e.last_name,e.department_id,

       d.department_name

  from employees e

  left join departments d

  on (e.department_id =d.department_id);

 

--2)right join:右外连接

--除了返回两个表中所有满足连接条件的行之外,还返回右表中

--所有不满足连接条件的行。所谓右表,就是写在rightjoin关键字

--右边的表

 

--查询雇员的姓名、部门编号以及部门名称,要求没有雇员的部门

--也要显示

select e.last_name,e.department_id,

       d.department_name

  from employees e

  right join departments d

  on (e.department_id =d.department_id);

 

 

--3)full join:全外连接

--除了返回两个表中所有满足连接条件的行之外,还返回两个表中

--所有不满足连接条件的行。

 

--查询雇员的姓名、部门编号以及部门名称,要求没有雇员的部门

--以及没有部门的雇员也要显示

select e.last_name,e.department_id,

       d.department_name

  from employees e

  full join departments d

  on (e.department_id =d.department_id);

 

--连接查询时,应该避免产生笛卡尔积:

--当一个连接查询没有写连接条件或者连接条件非法时,其产生的结果集的行数

--就等于第一个表的总行数乘上第二个表的总行数,这个

--结果集叫做笛卡尔积。

 

select last_name,department_name

  from employees, departments ;

 

--连接条件非法  

select last_name,department_name

  from employees 

  join departments

  on (1=1);  

 

--如果想专门产生笛卡尔积,可以使用cross join

select last_name,department_name

  from employees

  cross join departments ;

版权声明:本文为博主原创文章,未经博主允许不得转载。

USING与 ORA-25154 ,ORA-01748

USING中比较特殊的一点是:使用USING的SQL中对于两张表中共有的同名列,不能指定该列的表名。例如:t1与t2表中ID列同名.如果在使用using的sql中明确指定了ID列        所属的...
  • lwei_998
  • lwei_998
  • 2011年01月02日 21:43
  • 2858

SQL多表连接查询(详细实例)

本文主要列举两张和三张表来讲述多表连接查询。 新建两张表: 表1:student  截图如下: 表2:course  截图如下: (此时这样建表只是为了演示连接SQ...
  • Mr_Tim
  • Mr_Tim
  • 2016年04月12日 17:24
  • 11645

oracle 多表联合查询总结归纳

本次预计讲解的知识点 1、 多表查询的操作、限制、笛卡尔积的问题; 2、 统计函数及分组统计的操作; 3、 子查询的操作,并且结合限定查询、数据排序、多表查询、统计查询一起完成各个复杂查询的操作...
  • rosekin
  • rosekin
  • 2014年09月15日 20:57
  • 41239

分析比较多表查询中的IN与JOIN

IN 是子查询的关键字,JOIN 是连接的关键字,项目开发中经常会使用到多表查询,而子查询与连接正是实现多表查询的重要途径。那两者的实质有什么区别?IN与JOIN谁更好?下面就来分析与比较。现在有te...
  • qq_33290787
  • qq_33290787
  • 2016年07月17日 10:56
  • 8002

多表连接查询笔记之二 -- 外连接

外连接 在内连接操作中,只有满足连接条件中的元组才能出现在查询结果集中,但有时我们也希望得到哪些不满足连接条件的元组的信息,比如查看 全部课程的被选修情况,包括有学生的课程和没有学生选的课程。如果...
  • shehun11
  • shehun11
  • 2014年09月08日 11:09
  • 753

多表连接原理!

学习数据库查询的时候对多表连接查询的有些概念还比较模糊。而连接查询是在数据库查询操作的时候肯定要用到的。对于此概念 我用通俗一些的语言和例子来进行讲解。这个例子是我讲课的时候经常采用的例子。 首先我们...
  • yangjixiang_hao123
  • yangjixiang_hao123
  • 2010年01月17日 15:32
  • 1990

hibernate 实现多表连接查询

Hibernate主要支持两种查询方式:HQL查询和Criteria查询。前者应用较为广发,后者也只是调用封装好的接口。现在有一个问题,就是实现多表连接查询,且查询结果集不与任何一个实体类对应,怎么解...
  • qjyue77
  • qjyue77
  • 2016年05月31日 16:15
  • 1184

表的复杂查询-—多表查询

员工表emp:     字段:empno(编号),ename(姓名),job(职位),mgr(上级的编号),hiredate(入职时间),sal(薪水),comm(奖金),deptno(部门编号...
  • yangying1990
  • yangying1990
  • 2016年05月19日 15:12
  • 119

多表连接查询解析

/*******************************************************************/ >多表联接查询解析 /*******************...
  • zyb243380456
  • zyb243380456
  • 2012年04月05日 22:28
  • 1111

多表连接查询笔记之一 -- 内连接

多表连接查询: 如果查询同时涉及到两个或两个以上的表,则称为连接查询。连接查询时关系数据库中最主要的查询, 主要包括内连接,左外连接,右外连接,全外连接和交叉连接等。     1、内连接  内...
  • shehun11
  • shehun11
  • 2014年09月08日 10:09
  • 697
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:多表连接查询
举报原因:
原因补充:

(最多只允许输入30个字)