澳门金沙vip 7

SQL中的连接(1)——SQL读书笔记

 

《SQL学习指南》中的第5章

  

1.连接:

   
 在某种机制下,需要将多个表中的数据进行整合到一起,即同一个查询的结果集中包含来自两个或者两个以上的表,这种机制被称为连接(join).

 

 1.1
ANSI连接语法

   
 本文采用的都是符合SQL92版本的ANSI
SQL标准,所有的主流数据库都采用了SQL92的连接语法。由于这些数据库都出现SQL92标准发布之前,同时存在一些旧的连接语法,如下所示:

   
 例子1:

   
 SELECT e.fname, e.lname, d.`name`

     FROM employee e , department
d

     WHERE e.dept_id =
d.dept_id;

 
 

     推荐使用SQL92标准 的连接方式,不推荐使用上面的连接方式,主要原因是:

     1)连接条件和过滤条件被分隔到两个子句中(ON子句和WHERE子句),使得查询语句更利于理解;

   
 2)每两个表的连接条件都在ON子句中列出,容易找出连接条件;

   
 3)SQL92标准可以在各种数据库中通用,而旧的语法在不同的数据中的表现可能略有不同; 

       

 1.2
笛卡尔积

   
 例子 1: 直接使用from连接employee 和 department表

   
 方式一:

     SELECT e.fname, e.lname,
d.`name`

     FROM employee e JOIN
department 

   
 

   
 一个有18个雇员和3个部门,会产生54行数据,由于查询没有明确的指定两个表是如何连接的造成的,这时会默认两张表置换。这样的连接被称为交叉连接(cross
join).

 1.3
内连接

   
 

     1)两表连接

   
 例子1: 查询每个雇员所属的部门信息

 

   
 SELECT e.fname, e.lname, d.`name`

     FROM employee e JOIN department
d

    
     ON e.dept_id = d.dept_id

   
 

   
 结果如图所示

   
 澳门金沙vip 1

 

     
   
 此时通过描述上面两个表是通过何种方式关联的,是通过dept_id来关联两个表的。

   
 如果在一个表中的dept_id列中存在某个值,但该值在另一张表的dept_id列中不存在,那么此时相关的行的连接会失败,在结果集中的相关行的连接会失败,在结果集中将会排除包含该值的行,这种类型的连接被称为内连接

 
 

若想要包含某个表中的所有行,并不需要考虑该表的每一行都与另一张表匹配,那么可以使用外连接

 

  
 2)多表内连接

     —
例子2.查询Woburn支行中所有熟练柜员(在2007年以前入职的柜员)开设的账户

 

SELECT
a.account_id, a.product_cd,a.open_date

FROM
account a

INNER
JOIN employee e

ON a.open_emp_id =
e.emp_id

INNER
JOIN branch b

ON e.assigned_branch_id =
b.branch_id

WHERE
e.start_date < ‘2007-01-01’

AND
(e.title = ‘Teller’ OR e.title = ‘Head Teller’)

AND
b.`name` = ‘Woburn Branch’;

 

结果如图所示:

澳门金沙vip 2

 

   
 
现在FROM子句中包含3个表,两种连接类型和两个ON子句,看上去连接的顺序是account表,employee表,然后branch表,那么如果交换employ表和account表的连接顺序会出现什么情况呢?

   
 

SELECT
a.account_id, a.product_cd,a.open_date

FROM
employee e

INNER
JOIN account a

ON a.open_澳门金沙vip,emp_id =
e.emp_id

INNER
JOIN branch b

ON e.assigned_branch_id =
b.branch_id

WHERE
e.start_date < ‘2007-01-01’

AND
(e.title = ‘Teller’ OR e.title = ‘Head Teller’)

AND
b.`name` = ‘Woburn Branch’;

 

结果如图所示—无改变

澳门金沙vip 3

那么在更换employee和branch的顺序呢?

 

SELECT
a.account_id, a.product_cd,a.open_date

FROM
branch b

INNER
JOIN account a

ON a.open_branch_id =
b.branch_id

INNER
JOIN employee e

ON a.open_emp_id =
e.emp_id

WHERE
e.start_date < ‘2007-01-01’

AND
(e.title = ‘Teller’ OR e.title = ‘Head Teller’)

AND
b.`name` = ‘Woburn Branch’;

 

结果如图所示—无改变

澳门金沙vip 4 

那么更换ON语句的位置呢

 

SELECT
a.account_id, a.product_cd,a.open_date

FROM
branch b

INNER
JOIN account a

INNER
JOIN employee e

ON a.open_branch_id =
b.branch_id

ON a.open_emp_id =
e.emp_id

WHERE
e.start_date < ‘2007-01-01’

AND
(e.title = ‘Teller’ OR e.title = ‘Head Teller’)

AND
b.`name` = ‘Woburn Branch’;

 

结果如图所示—报错!错误原因:ON语句连接关系要紧跟
JOIN语句

澳门金沙vip 5 

  
例子二多种表达方式的原因:SQL是一种非过程化的语言,也就是说只需要描述要获取的数据对象,

而执行过程是数据库服务器负责(我只要结果集*_*)(但是左连接,右连接的问题,还不是很确定,稍后还要进行测试一下)

 

3)子查询作为结果集的内连接

例子二中的另一个版本:使用“连接 ”连接子查询,也就是将子查询的结果集作为查询表进行连接

 

1) 

   
 SELECT e.emp_id

FROM
employee e

WHERE
e.start_date < ‘2007-01-01’

AND
(e.title = ‘Teller’ OR e.title = ‘Head Teller’);

 

2) 
SELECT b.branch_id

FROM 
branch b

WHERE
b.`name` = ‘Woburn Branch’;

 

3)
 SELECT a.account_id, a.product_cd,a.open_date

FROM
account a

INNER
JOIN (SELECT e.emp_id

FROM
employee e

WHERE
e.start_date < ‘2007-01-01’

AND
(e.title = ‘Teller’ OR e.title = ‘Head Teller’) ) em

ON
a.open_emp_id = em.emp_id

INNER
JOIN (SELECT b.branch_id

FROM 
branch b

WHERE
b.`name` = ‘Woburn Branch’) br

ON
a.open_branch_id = br.branch_id;

 

4)自连接

例子3.列出雇员的姓名同时列出主管的姓名:

 

SELECT
emp.emp_id AS emp_id,CONCAT(emp.fname,’ ‘,emp.lname) AS
em,

e.emp_id AS
super_em_id,CONCAT(e.fname,’ ‘,e.lname) AS super_em

FROM
employee e INNER JOIN employee emp

ON
e.emp_id = emp.superior_emp_id;

 

结果如图所示:

 
  澳门金沙vip 6

 

 

上面查询实现了对表自身进行连接,自连接,employee表中包含了指向自身的外键(superior_emp_id)

同时,employee表一共18行,但此查询返回了17行,这是由于银行的总经理MIchael
Smith并没

自己的主管,它的superior_emp_id列为null,因此在改行上的内连接失败,在结果集中并不会显示。

 

5)不等连接

例子4.假如执行经理决定举办一次面向银行柜员的象棋比赛,现在要创建所有对弈者的列表

   
 SELECT CONCAT(e.fname,’ ‘,e.lname) as TEAM_A,

‘VS’,
CONCAT(emp.fname,’ ‘,emp.lname) as TEAM_B

FROM
employee e INNER JOIN employee emp

ON
e.emp_id < emp.emp_id

WHERE
e.title = ‘Teller’ AND emp.title = ‘Teller’;

结果如图所示:

 澳门金沙vip 7