[玩转SQL]:05 多表查询

多表查询


SQL 1999标准多表查询语法

SELECT        table1.column, table2.column
FROM          table1
[NATURAL JOIN table2] |
[JOIN         table2 USING (column_name)] |
[JOIN         table2
   ON        (table1.column_name = table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2
   ON        (table1.column_name = table2.column_name)] |
[CROSS JOIN table2];

ORACLE标准多表查询语法

SELECT table1.column, table2.column
FROM   table1, table2
WHERE  table1.column1 = table2.column2;
--查询员工职位(ORACLE)
SELECT employee_id, last_name, department_name
FROM   employees, departments
WHERE  employees.department_id = departments.department_id;

如果两个表中有相同的列名并且要使用这个列名需要使用表前缀.

建议书写程序时每个列名都加上表前缀,有助于提供程序执行速度.

--表名可以使用别名(ORACLE)
SELECT employee_id, last_name, e.department_id, department_name
FROM   employees e, departments d
WHERE  e.department_id = d.department_id;

如使用表的别名后,所有调用表名时必须使用别名.


NATURAL JOIN

两个表至少有一列有相同名字并且类型相同.

--基于locations_id来连接locations与departments表(SQL 1999)
SELECT       department_id, department_name, location_id, city
FROM         departments
NATURAL JOIN locations;

--基于locations_id来连接locations与departments表(ORACLE)
SELECT d.department_id, d.department_name, d.location_id, l.city
FROM   departments d, locations l
WHERE  d.location_id = l.location_id;


USING

若名字相同但类型不同.可使用USING来指定用那列来进行JOIN.

NATURALJOINUSING互斥.

SELECT employee_id, last_name, location_id, department_id
FROM   employees JOIN departments
USING  (department_id);
等同于

SELECT employee_id, last_name, location_id, departments.department_id
FROM   employees, departments
WHERE  employees.department_id = departments.department_id;

USING内不要使用表前缀.

USING包含的列,USING后WHERE中使用时不能使用表前缀.


ON

若名字不同、类型不同那么使用ON子句.

SELECT e.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);

等同于

SELECT e.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;

--三张表JOIN(SQL 1999)
SELECT employee_id, city, department_name
FROM   employees e
JOIN   departments d
ON     d.department_id = e.department_id
JOIN   locations l
ON     d.location_id = l.location_id;

--三张表JOIN(ORACLE)
SELECT employee_id, city, department_name
FROM   employees e, departments d, locations l
WHERE  e.department_id = d.department_id AND d.location_id = l.location_id;



SELF-JOIN

--SQL 1999
SELECT worker.last_name emp, manager.last_name mgr
FROM   employees worker JOIN employees manager
ON     (worker.manager_id = manager.employee_id);
等同于

--ORACLE
SELECT worker.last_name || ' works for ' || manager.last_name
FROM   employees worker, employees manager
WHERE  worker.manager_id = manager.employee_id;


NONEQUIJOINS

--SQL 1999
SELECT  e.last_name, e.salary, j.grade_level
FROM    employees e JOIN job_grades j
ON      e.salary
        BETWEEN j.lowest_sal AND j.highest_sal;
--ORACLE
SELECT e.last_name, e.salary, j.grade_level
FROM   employees e, job_grades j
WHERE  e.salary
       BETWEEN j.lowest_sal AND j.highest_sal;
NONEQUIJOINS常用于范围性查询.

OUTER-JOINS

SQL1999 

INNER只返回匹配上的行

Outer返回非匹配上的行

 

左外连接

显示左边表非匹配的行

--SQL 1999
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e LEFT OUTER JOIN departments d
ON    (e.department_id = d.department_id);

右外连接

显示右边表非匹配的行

--SQL 1999
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e RIGHT OUTER JOIN departments d
ON    (e.department_id = d.department_id);


ORACLE中只需要加上一个(+)来实现左右外连接.

左边(+)是右外连接.

右边(+)是左外连接.


右外连接

SELECT table1.column, table2.column
FROM   table1, table2
WHERE  table1.column(+) = table2.cloumn;
左外连接

SELECT table1.column, table2.column
FROM   table1, table2
WHERE  table1.column = table2.cloumn(+);

CROSS-JOINS

使用表1的每一列与表2的每一列进行JOIN.

可以利用此特性产生大量数据.


--------------------------------------------------------------------------------------
版权所有,转载时必须以链接方式注明源地址,否则追究法律责任!

QQ    :   413844436
Email  :   softomg@163.com
Blog   :   http://blog.csdn.net/softomg

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值