[Oracle数据库学习]四、多表查询

D2

多表查询

从多张表中获得数据。

 

笛卡尔集

产生笛卡尔集的条件：

1）省略连接条件；

2）连接条件无效；

3）所有表中的所有行相互连接。

SELECT * 
FROM employees;
EMPLOYEE_IDEMPLOYEE_NAMEDEPARTMENT_IDJOB_IDSALARYCOMMISSION_PCTHIRE_DATEDESCRIPTION
1 Alice 1 PM 10000 (null) 2007-11-15T00:00:00Z (null)
2 Bob 1 DEV 5000 0.12 2017-03-10T00:00:00Z abc%%
3 Cindy 2 QA 6000 0.1 2010-04-15T00:00:00Z abcd
4 Donald 2 AM 4000 0.3 2019-01-05T00:00:00Z ac%d
5 Elsa 2 DEV 8000 0.12 2007-11-13T00:00:00Z abc%% 
SELECT * 
FROM departments;
DEPARTMENT_IDDEPARTMENT_NAMELOCATION_ID
1 Development 1
2 Trade 2 
SELECT employees.employee_name, departments.department_id
FROM employees, departments;
EMPLOYEE_NAMEDEPARTMENT_ID
Alice 1
Bob 1
Cindy 1
Donald 1
Elsa 1
Alice 2
Bob 2
Cindy 2
Donald 2
Elsa 2

如何避免产生笛卡尔集：

在WHERE子句中加入有效的连接条件。

 

连接的类型


适用于SQL: 1999的连接:
交叉连接（Cross joins）
自然连接（Natural joins）
Using子句（Using clause）
完整或两个方向的外部连接（Full or two sided outer joins）
任意连接条件的外部连接（Arbitrary join conditions for outer joins）

适用于的连接Oracle 提供的连接(8i或更早):
等值连接（Equijoin）
不等值连接（Non-equijoin）
外连接（Outer join）
自连接（Self join）

--内连接是左边匹配右边表，有对应的则显示
--左连接是以左边表作为基准匹配右边表，如果右边表没有对应的则为null
--右连接是已右边表作为基准匹配左边表，如果左边表没有对应的则为null
--完全连接是左边表匹配右边表，如果有则显示没有则显示为null
--交叉连接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉连接也称作笛卡尔积。

 

D3

Oracle 连接

使用连接可以在多个表中查询数据：

1）在WHERE子句中写入连接条件；

2）如果多个表中有相同的列，在列名之前指定表名。

等值连接

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;

 

SELECT employees.employee_name, employees.department_id, departments.department_id, departments.location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;
EMPLOYEE_NAMEDEPARTMENT_IDDEPARTMENT_IDLOCATION_ID
Bob 1 1 1
Alice 1 1 1
Elsa 2 2 2
Donald 2 2 2
Cindy 2 2 2

 

表的别名

使用表的别名简化查询。

SELECT e.employee_name, e.department_id, d.department_id, d.location_id
FROM employees e, departments d
WHERE e.department_id = d.department_id;
EMPLOYEE_NAMEDEPARTMENT_IDDEPARTMENT_IDLOCATION_ID
Bob 1 1 1
Alice 1 1 1
Elsa 2 2 2
Donald 2 2 2
Cindy 2 2 2

 

连接多个表

连接n个表，最少需要n-1个连接条件（避免出现笛卡尔集）。

SELECT e.employee_name, e.department_id, d.department_id, d.location_id, l.location_id, l.location_city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id  
AND d.location_id = l.location_id;
EMPLOYEE_NAMEDEPARTMENT_IDDEPARTMENT_IDLOCATION_IDLOCATION_IDLOCATION_CITY
Alice 1 1 1 1 Beijing
Bob 1 1 1 1 Beijing
Cindy 2 2 2 2 Shanghai
Donald 2 2 2 2 Shanghai
Elsa 2 2 2 2 Shanghai

 

非等值连接

SELECT * 
FROM salary_grade;
SALARY_GRADELOWEST_SALARYHIGHEST_SALARY
A 20000 30000
B 10000 19999
C 5000 9999
D 0 4999

可以查询表1的数据在表2数据范围内

EMPLOYEES表中的列工资应在JOB_GRADES表中的最高工资与最低工资之间

SELECT e.employee_name, e.salary, s.salary_grade
FROM employees e, salary_grades s
WHERE e.salary 
BETWEEN s.lowest_salary AND s.highest_salary
EMPLOYEE_NAMESALARYSALARY_GRADE
Donald 4000 D
Bob 5000 C
Cindy 6000 C
Elsa 8000 C
Alice 10000 B

 

外连接

使用外连接（符号是(+)）可以查询不满足连接条件的数据。

SELECT * 
FROM departments;
DEPARTMENT_IDDEPARTMENT_NAMELOCATION_ID
1 Development 1
2 Trade 2
3 Qulity 4 
SELECT * 
FROM locations;
LOCATION_IDLOCATION_CITY
1 Beijing
2 Shanghai
3 Chongqing

可以看到DEPARTMENT_NAME是Qulity的LOCATION_ID=4没有出现在locations表中，

而LOCATION_CITY为Chongqing的LOCATION_ID=3没有出现在在departments中。

 

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

 

SELECT l.location_id, l.location_city, d.department_id
FROM locations l, departments d
WHERE l.location_id(+) = d.location_id;
LOCATION_IDLOCATION_CITYDEPARTMENT_ID
1 Beijing 1
2 Shanghai 2
(null) (null) 3

左表无对应数据显示空。

 

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

 

SELECT l.location_id, l.location_city, d.department_id
FROM locations l, departments d
WHERE l.location_id = d.location_id(+);
LOCATION_IDLOCATION_CITYDEPARTMENT_ID
1 Beijing 1
2 Shanghai 2
3 Chongqing (null)

右表无对应数据显示空。

 

自连接

SELECT worker.employee_name, worker.manager_id, manager.employee_id, manager.employee_name
FROM employees worker, employees manager
WHERE worker.manager_id = manager.employee_id;
EMPLOYEE_NAMEMANAGER_IDEMPLOYEE_IDEMPLOYEE_NAME
Elsa 1 1 Alice
Bob 1 1 Alice
Cindy 2 2 Bob

worker和manager都保存在employees表中，用自连接得到对应关系数据。

 

使用SQL: 1999 语法连接

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

 

叉集

等同于笛卡尔集；关键字为CROSS JOIN

SELECT departments.location_id, locations.location_id
FROM departments CROSS JOIN locations;
LOCATION_IDLOCATION_ID
1 1
1 2
1 3
2 1
2 2
2 3
4 1
4 2
4 3

 

自然连接

自然连接使用NATURAL JOIN子句。
会以两个表中具有相同名字的列为条件创建等值连接，在表中查询满足等值条件的数据。
如果只是列名相同而数据类型不同，则会产生错误。

SELECT department_name, location_id, location_city
FROM departments NATURAL JOIN locations;
DEPARTMENT_NAMELOCATION_IDLOCATION_CITY
Development 1 Beijing
Trade 2 Shanghai

采用相同的location_id列进行等值连接。

注意：不能使用表名或表的别名修饰相同的列。

SELECT department_name, locations.location_id, location_city
FROM departments NATURAL JOIN locations;

ORA-25155: column used in NATURAL join cannot have qualifier

 

使用USING子句创建连接

NATURAL JOIN和USING子句经常同时使用。
在NATURAL JOIN子句创建等值连接时，可以使用USING子句，在有多个列满足条件时，指定等值连接中需要用到的列。
不要给选中的列中加上表名前缀或别名。

SELECT e.employee_name, d.location_id
FROM employees e JOIN departments d
USING (department_id);
EMPLOYEE_NAMELOCATION_ID
Alice 1
Bob 1
Cindy 2
Donald 2
Elsa 2

 

使用ON子句创建连接

自然连接中是以具有相同名字的列为连接条件的。
可以使用ON子句指定额外的连接条件，这个连接条件是与其它条件分开的。
ON子句使语句具有更高的易读性。

SELECT e.employee_name, d.location_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
EMPLOYEE_NAMELOCATION_ID
Alice 1
Bob 1
Cindy 2
Donald 2
Elsa 2

 

使用ON子句创建多表连接

SELECT e.employee_name, d.department_name, l.location_city
FROM employees e 
JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;
EMPLOYEE_NAMEDEPARTMENT_NAMELOCATION_CITY
Alice Development Beijing
Bob Development Beijing
Cindy Trade Shanghai
Donald Trade Shanghai
Elsa Trade Shanghai

 

外连接

在SQL: 1999中，内连接只返回满足连接条件的数据。
两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行，这种连接称为左（或右）外联接。
两个表在连接过程中除了返回满足连接条件的行以外还返回两个表中不满足条件的行，这种连接称为满外联接。

 

左外连接

使用LEFT OUTER JOIN

SELECT l.location_id, l.location_city, d.department_id
FROM locations l 
LEFT OUTER JOIN departments d
ON l.location_id = d.location_id;
LOCATION_IDLOCATION_CITYDEPARTMENT_ID
1 Beijing 1
2 Shanghai 2
3 Chongqing (null)

 

右外连接

使用RIGHT OUTER JOIN

SELECT l.location_id, l.location_city, d.department_id
FROM locations l 
RIGHT OUTER JOIN departments d
ON l.location_id = d.location_id;
LOCATION_IDLOCATION_CITYDEPARTMENT_ID
1 Beijing 1
2 Shanghai 2
(null) (null) 3

 

满外连接

使用

FULL OUTER JOIN
SELECT l.location_id, l.location_city, d.department_id
FROM locations l 
FULL OUTER JOIN departments d
ON l.location_id = d.location_id;
LOCATION_IDLOCATION_CITYDEPARTMENT_ID
1 Beijing 1
2 Shanghai 2
(null) (null) 3
3 Chongqing (null)

 

增加连接条件

SELECT e.employee_name, d.location_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id
AND e.manager_id = 1;
EMPLOYEE_NAMELOCATION_ID
Bob 1
Elsa 2

 

总结：

本节介绍多表连接查询。

1）Oracle连接：笛卡尔集、等值连接、非等值连接、外连接、自连接；

2）SQL连接：叉集（同笛卡尔集）、自然连接（USING子句）、ON子句、外连接（左外连接、右外连接、满外连接）。

 

posted @ 2020-07-10 13:59  workingdiary  阅读(177)  评论(0)    收藏  举报