sql学习笔记4:多表查询

笛卡尔积join

将两张表jion成一个表进行查询

select  e.fname, e.lname, d.namefrom employee as e join department as d

内连接

select e.fname, e.lname, d.namefrom employee e inner join department don e.dept_id = d.dept_id

如果, 两个表的列明相同,可以用using

select e.fname, e.lname, d.namefrom employee e inner join department dusing(dept_id)

ANSI连接语法

ANSI标准的优点是:
- 连接条件和过滤条件被分隔到两个字句中(on字句和where字句), 使查询语句更易于理解;
- 每两个表之间的连接条件都在他们自己的on自剧中列出, 这样不容易错误滴忽略了某些连接条件
- 使用SQL92连接语法的查询语句可以在各种数据库服务器中通用.
不标准的语句:

select a.account_id, a.cust_id, a.open_date, a.product_cd  FROM account as a, baranch as b, employee e where a.open_emp_id = e.emp_id #链接条件and e.start_date < '2007-01-01'and e.assigned_branch_id = b.branch_id #链接条件and (e.title = 'Teller' or e.title = 'Head Teller')and b.name = 'Woburn Branch';

使用这种查询不容易识别连接条件

select a.account_id, a.cust_id, a.open_date, a.product_cd  FROM account as a inner join employee as eon a.open_emp_id = e.emp_idinner join branch as bON e.assigned_branch_id = b.branch_idwhere e.start_date < '2007-01-01'and (e.title = 'Teller' or e.title = 'Head Teller')and b.name = 'Woburn Branch';

连续两次使用同一个表

branch表里有account(账户的开户支行)和employee(雇员所在支行)的表的外键, 若要提取这两个信息需要对branch表进行两次链接.

select a.account_id, e.emp_id,b_a.name as open_branch, b_e.name emp_branchfrom account as a inner join branch as b_aon b_a.branch_id = a.open_branch_idinner join employee as eon a.open_emp_id = e.emp_idinner join branch as b_e on e.assigned_branch_id = b_e.branch_idwhere a.product_cd = 'CHK'

自连接

employee表包含了一个指向自身的外键, 即指向本表自身的的列(superiir_emp_id) . 该列指向了雇员的主管. 使用自连接, 可已在列出每个雇员姓名的同时列出主管的姓名

select e.fname, e.lname, e_mgr.fname, e_mgr.lnamefrom employee as e inner join employee e_mgron e.superior_emp_id = e_mgr.emp_id

相等链接和不等链接

可以通过限定值的范围实现对表的链接,即不等链接

select e.emp_id, e.fname, e.lname, e.start_datefrom employee as e inner join product as pon e.start_date >= p.date_offeredAND e.start_date <= p.date_retiredwhere p.name = 'no-fee checking'

该查询链接没有外键关联, 为了找出连个日期之间的数据.

还可以对自身使用不等链接. 例如, 找出employee表中的不同的两个人为一行输出

select e1.fname, e1.lname,  'vs' as vs , e2.fname, e2.lnamefrom employee as e1 inner join employee as e2 ON e1.emp_id != e2.emp_idwhere e1.title='Teller' and e2.title='Teller'

但是返现表中有重复， 每对名字都有一对名字相反的与之对应

select e1.fname, e1.lname,  'vs' as vs , e2.fname, e2.lnamefrom employee as e1 inner join employee as e2 ON e1.emp_id < e2.emp_idwhere e1.title='Teller' and e2.title='Teller'

问题解决.