[玩转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.

NATURALJOIN与USING互斥.

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.youkuaiyun.com/softomg