使用联结从多个表中检索数据

?

联结join是一种将来自两个或更多表中的数据组合起来的查询。

?

查询标识出哪些是经理的职员

select * from org

?

?DEPTNUMB DEPTNAME?????? MANAGER DIVISION?? LOCATION????
-------- -------------- ------- ---------- -------------
????? 10 Head Office??????? 160 Corporate? New York????
????? 15 New England???????? 50 Eastern??? Boston??????
????? 20 Mid Atlantic??????? 10 Eastern??? Washington??
????? 38 South Atlantic????? 30 Eastern??? Atlanta?????
????? 42 Great Lakes??????? 100 Midwest??? Chicago?????
????? 51 Plains???????????? 140 Midwest??? Dallas??????
????? 66 Pacific??????????? 270 Western??? San Francisco
????? 84 Mountain?????????? 290 Western??? Denver??????

? 8 record(s) selected.

?

select * from staff

?

ID???? NAME????? DEPT?? JOB?? YEARS? SALARY??? COMM????
------ --------- ------ ----- ------ --------- ---------
??? 10 Sanders?????? 20 Mgr??????? 7? 98357.50???????? -
??? 20 Pernal??????? 20 Sales????? 8? 78171.25??? 612.45
??? 30 Marenghi????? 38 Mgr??????? 5? 77506.75???????? -
??? 40 O'Brien?????? 38 Sales????? 6? 78006.00??? 846.55
??? 50 Hanes???????? 15 Mgr?????? 10? 80659.80???????? -
??? 60 Quigley?????? 38 Sales????? -? 66808.30??? 650.25
??? 70 Rothman?????? 15 Sales????? 7? 76502.83?? 1152.00
??? 80 James???????? 20 Clerk????? -? 43504.60??? 128.20
??? 90 Koonitz?????? 42 Sales????? 6? 38001.75?? 1386.70
?? 100 Plotz???????? 42 Mgr??????? 7? 78352.80???????? -
?? 110 Ngan????????? 15 Clerk????? 5? 42508.20??? 206.60
?? 120 Naughton????? 38 Clerk????? -? 42954.75??? 180.00
?? 130 Yamaguchi???? 42 Clerk????? 6? 40505.90???? 75.60
?? 140 Fraye???????? 51 Mgr??????? 6? 91150.00???????? -
?? 150 Williams????? 51 Sales????? 6? 79456.50??? 637.65
?? 160 Molinare????? 10 Mgr??????? 7? 82959.20???????? -
?? 170 Kermisch????? 15 Clerk????? 4? 42258.50??? 110.10
?? 180 Abrahams????? 38 Clerk????? 3? 37009.75??? 236.50
?? 190 Sneider?????? 20 Clerk????? 8? 34252.75??? 126.50
?? 200 Scoutten????? 42 Clerk????? -? 41508.60???? 84.20
?? 210 Lu??????????? 10 Mgr?????? 10? 90010.00???????? -
?? 220 Smith???????? 51 Sales????? 7? 87654.50??? 992.80
?? 230 Lundquist???? 51 Clerk????? 3? 83369.80??? 189.65
?? 240 Daniels?????? 10 Mgr??????? 5? 79260.25???????? -
?? 250 Wheeler?????? 51 Clerk????? 6? 74460.00??? 513.30
?? 260 Jones???????? 10 Mgr?????? 12? 81234.00???????? -
?? 270 Lea?????????? 66 Mgr??????? 9? 88555.50???????? -
?? 280 Wilson??????? 66 Sales????? 9? 78674.50??? 811.50
?? 290 Quill???????? 84 Mgr?????? 10? 89818.00???????? -
?? 300 Davis???????? 84 Sales????? 5? 65454.50??? 806.10
?? 310 Graham??????? 66 Sales???? 13? 71000.00??? 200.30
?? 320 Gonzales????? 66 Sales????? 4? 76858.20??? 844.00
?? 330 Burke???????? 66 Clerk????? 1? 49988.00???? 55.50
?? 340 Edwards?????? 84 Sales????? 7? 67844.00?? 1285.00
?? 350 Gafney??????? 84 Clerk????? 5? 43030.50??? 188.00

? 35 record(s) selected.

?

联结查询（内联结查询，返回差集中满足联结条件的行）?

SELECT deptnumb, deptname, id AS manager_id, name AS manager

????? FROM org, staff? WHERE manager = id? ORDER BY deptnumb

?

?

也可以写为:

SELECT?deptnumb,deptname,id?AS manager_id,name?AS manager

??????FROM org INNER JOIN staff ON manager = id?ORDER?BY deptnumb

?

DEPTNUMB DEPTNAME?????? MANAGER_ID MANAGER?
-------- -------------- ---------- ---------
????? 10 Head Office?????????? 160 Molinare
????? 15 New England??????????? 50 Hanes???
????? 20 Mid Atlantic?????????? 10 Sanders?
????? 38 South Atlantic???????? 30 Marenghi
????? 42 Great Lakes?????????? 100 Plotz???
????? 51 Plains??????????????? 140 Fraye???
????? 66 Pacific?????????????? 270 Lea?????
????? 84 Mountain????????????? 290 Quill???

? 8 record(s) selected.

?

?

外部联结Outer?joi