Oracle数据库的连接查询
Oracle数据库的连接查询
准备表
Employee表:
- EMPL EMPLOYEE_NAME DEPARTMENT_ID
- ---- -------------------- -------------
- 1001 jack 1
- 1002 tom 2
- 1003 smith 2
- 1004 rose 3
- DEPARTMENT_ID DEPARTMENT_NAME DEPARTMENT_LOCATION
- ------------- -------------------- -----------------------
- 1 公寓1 ss
- 2 公寓2 sa
- 3 公寓3 sb
- 4 公寓4 sv
使用=进行条件连接的操作。
- SQL> select e.employee_name,e.department_id,d.department_name
- 2 from employee e,department d
- 3 where e.department_id=d.department_id;
- EMPLOYEE_NAME DEPARTMENT_ID DEPARTMENT_NAME
- -------------------- ------------- --------------------
- jack 1 公寓1
二、外连接
外连接分为左外连接和右外连接。
左连接以第一张表为基准,必须显示完该表的所有数据。
- SQL> select e.employee_name,e.department_id,d.department_name
- 2 from employee e,department d
- 3 where e.department_id=d.department_id(+);
- EMPLOYEE_NAME DEPARTMENT_ID DEPARTMENT_NAME
- -------------------- ------------- --------------------
- jack 1 公寓1
- smith 2 公寓2
- tom 2 公寓2
- rose 3 公寓3
右连接以第二张表为基准,必须显示完该表的所有数据。
- SQL> select e.employee_name,e.department_id,d.department_name
- 2 from employee e,department d
- 3 where e.department_id(+)=d.department_id;
- EMPLOYEE_NAME DEPARTMENT_ID DEPARTMENT_NAME
- -------------------- ------------- --------------------
- jack 1 公寓1
- tom 2 公寓2
- smith 2 公寓2
- rose 3 公寓3
- 公寓4
评论暂时关闭