Chapter 06-Displaying Data From multiple Tables - 02

Creating Natural Joins

  • The NATURAL JOIN clause is based on all columns in the two tables that have the same name.
  • It selects rows from the two tables that have equal values in all matched columns.
  • If the columns having the same names have different data types,an error is returned.

Retrieving Records with Natural Joins

Natural Joins,不用指定用那一列进行关联,由数据库进行自动匹配.不过为实现自定义关联字段,可以通过USING Clause进行设置.

View Code
SQL> SELECT department_id,department_name,location_id,city FROM departments NATURAL JOIN locations;

DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
------------- -------------------- ----------- ------------------------------
           60 IT                          1400 Southlake
           50 Shipping                    1500 South San Francisco
           10 Administration              1700 Seattle
           30 Purchasing                  1700 Seattle
           90 Executive                   1700 Seattle
          100 Finance                     1700 Seattle
          110 Accounting                  1700 Seattle
          120 Treasury                    1700 Seattle
          130 Corporate Tax               1700 Seattle
          140 Control And Credit          1700 Seattle
          150 Shareholder Services        1700 Seattle

DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
------------- -------------------- ----------- ------------------------------
          160 Benefits                    1700 Seattle
          170 Manufacturing               1700 Seattle
          180 Construction                1700 Seattle
          190 Contracting                 1700 Seattle
          200 Operations                  1700 Seattle
          210 IT Support                  1700 Seattle
          220 NOC                         1700 Seattle
          230 IT Helpdesk                 1700 Seattle
          240 Government Sales            1700 Seattle
          250 Retail Sales                1700 Seattle
          260 Recruiting                  1700 Seattle

DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
------------- -------------------- ----------- ------------------------------
          270 Payroll                     1700 Seattle
           20 Marketing                   1800 Toronto
           40 Human Resources             2400 London
           80 Sales                       2500 Oxford
           70 Public Relations            2700 Munich

27 rows selected.

Retrieving Records with Equijoins:Oracle Syntax

Oracle Syntax
SQL> SELECT d.department_id,d.department_name,d.location_id,l.city FROM departments d,locations l WHERE d.location_id = l.location_id;

DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
------------- -------------------- ----------- ------------------------------
           60 IT                          1400 Southlake
           50 Shipping                    1500 South San Francisco
           10 Administration              1700 Seattle
           30 Purchasing                  1700 Seattle
           90 Executive                   1700 Seattle
          100 Finance                     1700 Seattle
          110 Accounting                  1700 Seattle
          120 Treasury                    1700 Seattle
          130 Corporate Tax               1700 Seattle
          140 Control And Credit          1700 Seattle
          150 Shareholder Services        1700 Seattle

DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
------------- -------------------- ----------- ------------------------------
          160 Benefits                    1700 Seattle
          170 Manufacturing               1700 Seattle
          180 Construction                1700 Seattle
          190 Contracting                 1700 Seattle
          200 Operations                  1700 Seattle
          210 IT Support                  1700 Seattle
          220 NOC                         1700 Seattle
          230 IT Helpdesk                 1700 Seattle
          240 Government Sales            1700 Seattle
          250 Retail Sales                1700 Seattle
          260 Recruiting                  1700 Seattle

DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
------------- -------------------- ----------- ------------------------------
          270 Payroll                     1700 Seattle
           20 Marketing                   1800 Toronto
           40 Human Resources             2400 London
           80 Sales                       2500 Oxford
           70 Public Relations            2700 Munich

27 rows selected.

Creating Joins with the USING Clause

  • If serval columns have the same names but the data types do not match,natural join can be applied using the USING clause to specify the columns that should be used for an enquijoin.
  • Use the USING clause to match only one column when more than one column matches.
  • The NATURAL JOIN and USING clauses are mutually execusive.(NATURAL JOIN 和 USING是互斥的,不能同时使用.)

HR.employees和HR.departments表,两张表有两列相同(名称相同,数据类型相同) ,在使用NATRURAL JOIN进行关联的时候,就会将department_id和manager_id进行一起关联.

NATURAL JOIN
SQL> SELECT employee_id,last_name,department_id,department_name FROM employees NATURAL JOIN departments;

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        202 Fay                                  20 Marketing
        206 Gietz                               110 Accounting
        101 Kochhar                              90 Executive
        102 De Haan                              90 Executive
        104 Ernst                                60 IT
        105 Austin                               60 IT
        106 Pataballa                            60 IT
        107 Lorentz                              60 IT
        109 Faviet                              100 Finance
        110 Chen                                100 Finance
        111 Sciarra                             100 Finance

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        112 Urman                               100 Finance
        113 Popp                                100 Finance
        115 Khoo                                 30 Purchasing
        116 Baida                                30 Purchasing
        117 Tobias                               30 Purchasing
        118 Himuro                               30 Purchasing
        119 Colmenares                           30 Purchasing
        129 Bissot                               50 Shipping
        130 Atkinson                             50 Shipping
        131 Marlow                               50 Shipping
        132 Olson                                50 Shipping

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        150 Tucker                               80 Sales
        151 Bernstein                            80 Sales
        152 Hall                                 80 Sales
        153 Olsen                                80 Sales
        154 Cambrault                            80 Sales
        155 Tuvault                              80 Sales
        184 Sarchand                             50 Shipping
        185 Bull                                 50 Shipping
        186 Dellinger                            50 Shipping
        187 Cabrio                               50 Shipping

32 rows selected.
USING CLAUSE
SQL> SELECT employee_id,last_name,department_id,department_name FROM employees JOIN departments USING(department_id);

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        200 Whalen                               10 Administration
        201 Hartstein                            20 Marketing
        202 Fay                                  20 Marketing
        114 Raphaely                             30 Purchasing
        119 Colmenares                           30 Purchasing
        115 Khoo                                 30 Purchasing
        116 Baida                                30 Purchasing
        117 Tobias                               30 Purchasing
        118 Himuro                               30 Purchasing
        203 Mavris                               40 Human Resources
        198 OConnell                             50 Shipping

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        199 Grant                                50 Shipping
        120 Weiss                                50 Shipping
        121 Fripp                                50 Shipping
        122 Kaufling                             50 Shipping
        123 Vollman                              50 Shipping
        124 Mourgos                              50 Shipping
        125 Nayer                                50 Shipping
        126 Mikkilineni                          50 Shipping
        127 Landry                               50 Shipping
        128 Markle                               50 Shipping
        129 Bissot                               50 Shipping

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        130 Atkinson                             50 Shipping
        131 Marlow                               50 Shipping
        132 Olson                                50 Shipping
        133 Mallin                               50 Shipping
        134 Rogers                               50 Shipping
        135 Gee                                  50 Shipping
        136 Philtanker                           50 Shipping
        137 Ladwig                               50 Shipping
        138 Stiles                               50 Shipping
        139 Seo                                  50 Shipping
        140 Patel                                50 Shipping

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        141 Rajs                                 50 Shipping
        142 Davies                               50 Shipping
        143 Matos                                50 Shipping
        144 Vargas                               50 Shipping
        180 Taylor                               50 Shipping
        181 Fleaur                               50 Shipping
        182 Sullivan                             50 Shipping
        183 Geoni                                50 Shipping
        184 Sarchand                             50 Shipping
        185 Bull                                 50 Shipping
        186 Dellinger                            50 Shipping

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        187 Cabrio                               50 Shipping
        188 Chung                                50 Shipping
        189 Dilly                                50 Shipping
        190 Gates                                50 Shipping
        191 Perkins                              50 Shipping
        192 Bell                                 50 Shipping
        193 Everett                              50 Shipping
        194 McCain                               50 Shipping
        195 Jones                                50 Shipping
        196 Walsh                                50 Shipping
        197 Feeney                               50 Shipping

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        104 Ernst                                60 IT
        103 Hunold                               60 IT
        107 Lorentz                              60 IT
        106 Pataballa                            60 IT
        105 Austin                               60 IT
        204 Baer                                 70 Public Relations
        176 Taylor                               80 Sales
        177 Livingston                           80 Sales
        179 Johnson                              80 Sales
        175 Hutton                               80 Sales
        174 Abel                                 80 Sales

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        173 Kumar                                80 Sales
        172 Bates                                80 Sales
        171 Smith                                80 Sales
        170 Fox                                  80 Sales
        169 Bloom                                80 Sales
        168 Ozer                                 80 Sales
        145 Russell                              80 Sales
        146 Partners                             80 Sales
        147 Errazuriz                            80 Sales
        148 Cambrault                            80 Sales
        149 Zlotkey                              80 Sales

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        150 Tucker                               80 Sales
        151 Bernstein                            80 Sales
        152 Hall                                 80 Sales
        153 Olsen                                80 Sales
        154 Cambrault                            80 Sales
        155 Tuvault                              80 Sales
        156 King                                 80 Sales
        157 Sully                                80 Sales
        158 McEwen                               80 Sales
        159 Smith                                80 Sales
        160 Doran                                80 Sales

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        161 Sewall                               80 Sales
        162 Vishney                              80 Sales
        163 Greene                               80 Sales
        164 Marvins                              80 Sales
        165 Lee                                  80 Sales
        166 Ande                                 80 Sales
        167 Banda                                80 Sales
        101 Kochhar                              90 Executive
        100 King                                 90 Executive
        102 De Haan                              90 Executive
        110 Chen                                100 Finance

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        108 Greenberg                           100 Finance
        111 Sciarra                             100 Finance
        112 Urman                               100 Finance
        113 Popp                                100 Finance
        109 Faviet                              100 Finance
        206 Gietz                               110 Accounting
        205 Higgins                             110 Accounting

106 rows selected.
等同于NATURAL JOIN
SQL> SELECT e.employee_id,e.last_name,d.department_id,d.department_name FROM employees e,departments d WHERE e.department_id = d.department_id AND e.manager_id = d.manager_id;

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        202 Fay                                  20 Marketing
        206 Gietz                               110 Accounting
        101 Kochhar                              90 Executive
        102 De Haan                              90 Executive
        104 Ernst                                60 IT
        105 Austin                               60 IT
        106 Pataballa                            60 IT
        107 Lorentz                              60 IT
        109 Faviet                              100 Finance
        110 Chen                                100 Finance
        111 Sciarra                             100 Finance

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        112 Urman                               100 Finance
        113 Popp                                100 Finance
        115 Khoo                                 30 Purchasing
        116 Baida                                30 Purchasing
        117 Tobias                               30 Purchasing
        118 Himuro                               30 Purchasing
        119 Colmenares                           30 Purchasing
        129 Bissot                               50 Shipping
        130 Atkinson                             50 Shipping
        131 Marlow                               50 Shipping
        132 Olson                                50 Shipping

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        150 Tucker                               80 Sales
        151 Bernstein                            80 Sales
        152 Hall                                 80 Sales
        153 Olsen                                80 Sales
        154 Cambrault                            80 Sales
        155 Tuvault                              80 Sales
        184 Sarchand                             50 Shipping
        185 Bull                                 50 Shipping
        186 Dellinger                            50 Shipping
        187 Cabrio                               50 Shipping

32 rows selected.
Oracle Syntax
SQL> SELECT e.employee_id,e.last_name,d.department_id,d.department_name FROM employees e,departments d WHERE e.department_id = d.department_id;

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        200 Whalen                               10 Administration
        201 Hartstein                            20 Marketing
        202 Fay                                  20 Marketing
        114 Raphaely                             30 Purchasing
        119 Colmenares                           30 Purchasing
        115 Khoo                                 30 Purchasing
        116 Baida                                30 Purchasing
        117 Tobias                               30 Purchasing
        118 Himuro                               30 Purchasing
        203 Mavris                               40 Human Resources
        198 OConnell                             50 Shipping

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        199 Grant                                50 Shipping
        120 Weiss                                50 Shipping
        121 Fripp                                50 Shipping
        122 Kaufling                             50 Shipping
        123 Vollman                              50 Shipping
        124 Mourgos                              50 Shipping
        125 Nayer                                50 Shipping
        126 Mikkilineni                          50 Shipping
        127 Landry                               50 Shipping
        128 Markle                               50 Shipping
        129 Bissot                               50 Shipping

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        130 Atkinson                             50 Shipping
        131 Marlow                               50 Shipping
        132 Olson                                50 Shipping
        133 Mallin                               50 Shipping
        134 Rogers                               50 Shipping
        135 Gee                                  50 Shipping
        136 Philtanker                           50 Shipping
        137 Ladwig                               50 Shipping
        138 Stiles                               50 Shipping
        139 Seo                                  50 Shipping
        140 Patel                                50 Shipping

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        141 Rajs                                 50 Shipping
        142 Davies                               50 Shipping
        143 Matos                                50 Shipping
        144 Vargas                               50 Shipping
        180 Taylor                               50 Shipping
        181 Fleaur                               50 Shipping
        182 Sullivan                             50 Shipping
        183 Geoni                                50 Shipping
        184 Sarchand                             50 Shipping
        185 Bull                                 50 Shipping
        186 Dellinger                            50 Shipping

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        187 Cabrio                               50 Shipping
        188 Chung                                50 Shipping
        189 Dilly                                50 Shipping
        190 Gates                                50 Shipping
        191 Perkins                              50 Shipping
        192 Bell                                 50 Shipping
        193 Everett                              50 Shipping
        194 McCain                               50 Shipping
        195 Jones                                50 Shipping
        196 Walsh                                50 Shipping
        197 Feeney                               50 Shipping

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        104 Ernst                                60 IT
        103 Hunold                               60 IT
        107 Lorentz                              60 IT
        106 Pataballa                            60 IT
        105 Austin                               60 IT
        204 Baer                                 70 Public Relations
        176 Taylor                               80 Sales
        177 Livingston                           80 Sales
        179 Johnson                              80 Sales
        175 Hutton                               80 Sales
        174 Abel                                 80 Sales

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        173 Kumar                                80 Sales
        172 Bates                                80 Sales
        171 Smith                                80 Sales
        170 Fox                                  80 Sales
        169 Bloom                                80 Sales
        168 Ozer                                 80 Sales
        145 Russell                              80 Sales
        146 Partners                             80 Sales
        147 Errazuriz                            80 Sales
        148 Cambrault                            80 Sales
        149 Zlotkey                              80 Sales

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        150 Tucker                               80 Sales
        151 Bernstein                            80 Sales
        152 Hall                                 80 Sales
        153 Olsen                                80 Sales
        154 Cambrault                            80 Sales
        155 Tuvault                              80 Sales
        156 King                                 80 Sales
        157 Sully                                80 Sales
        158 McEwen                               80 Sales
        159 Smith                                80 Sales
        160 Doran                                80 Sales

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        161 Sewall                               80 Sales
        162 Vishney                              80 Sales
        163 Greene                               80 Sales
        164 Marvins                              80 Sales
        165 Lee                                  80 Sales
        166 Ande                                 80 Sales
        167 Banda                                80 Sales
        101 Kochhar                              90 Executive
        100 King                                 90 Executive
        102 De Haan                              90 Executive
        110 Chen                                100 Finance

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------- ------------- --------------------
        108 Greenberg                           100 Finance
        111 Sciarra                             100 Finance
        112 Urman                               100 Finance
        113 Popp                                100 Finance
        109 Faviet                              100 Finance
        206 Gietz                               110 Accounting
        205 Higgins                             110 Accounting

106 rows selected.

Using Table Aliases with the USING Clause

  • Do not qualify a column that is used in the USING clause
  • If the same column is used elsewhere in the SQL statement,do not alias it.
ERRORS DISPLAY
SQL> SELECT l.location_id,d.department_name FROM locations l JOIN departments d USING(location_id) WHERE d.location_id = 1400;
SELECT l.location_id,d.department_name FROM locations l JOIN departments d USING(location_id) WHERE d.location_id = 1400
                                                                                                    *
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier
RIGHT DISPLAY
SQL> SELECT location_id,d.department_name FROM locations l JOIN departments d USING(location_id) WHERE location_id = 1400;

LOCATION_ID DEPARTMENT_NAME
----------- --------------------
       1400 IT

通过以上SQL实验得出:USING(column)子句中的column,不能加表前缀.

Creating Joins with the ON Cluase

  • The join condition for the natural join is basically an equijoin of all columns with the same name.
  • Use the ON clause to specify arbitrary conditions or specify columns to join.
  • The join condition is separated from other search conditions.
  • The ON clause makes code easy to understand.

NATURAL JOIN 要求列名称相同,数据类型相同.

USING子句,要求列名称相同,不一定数据类型相同.

Retrieving Records with the ON Clause

ORACLE ON CLAUSE
SQL> 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);

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
        200 Whalen                               10            10        1700
        201 Hartstein                            20            20        1800
        202 Fay                                  20            20        1800
        114 Raphaely                             30            30        1700
        119 Colmenares                           30            30        1700
        115 Khoo                                 30            30        1700
        116 Baida                                30            30        1700
        117 Tobias                               30            30        1700
        118 Himuro                               30            30        1700
        203 Mavris                               40            40        2400
        198 OConnell                             50            50        1500

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
        199 Grant                                50            50        1500
        120 Weiss                                50            50        1500
        121 Fripp                                50            50        1500
        122 Kaufling                             50            50        1500
        123 Vollman                              50            50        1500
        124 Mourgos                              50            50        1500
        125 Nayer                                50            50        1500
        126 Mikkilineni                          50            50        1500
        127 Landry                               50            50        1500
        128 Markle                               50            50        1500
        129 Bissot                               50            50        1500

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
        130 Atkinson                             50            50        1500
        131 Marlow                               50            50        1500
        132 Olson                                50            50        1500
        133 Mallin                               50            50        1500
        134 Rogers                               50            50        1500
        135 Gee                                  50            50        1500
        136 Philtanker                           50            50        1500
        137 Ladwig                               50            50        1500
        138 Stiles                               50            50        1500
        139 Seo                                  50            50        1500
        140 Patel                                50            50        1500

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
        141 Rajs                                 50            50        1500
        142 Davies                               50            50        1500
        143 Matos                                50            50        1500
        144 Vargas                               50            50        1500
        180 Taylor                               50            50        1500
        181 Fleaur                               50            50        1500
        182 Sullivan                             50            50        1500
        183 Geoni                                50            50        1500
        184 Sarchand                             50            50        1500
        185 Bull                                 50            50        1500
        186 Dellinger                            50            50        1500

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
        187 Cabrio                               50            50        1500
        188 Chung                                50            50        1500
        189 Dilly                                50            50        1500
        190 Gates                                50            50        1500
        191 Perkins                              50            50        1500
        192 Bell                                 50            50        1500
        193 Everett                              50            50        1500
        194 McCain                               50            50        1500
        195 Jones                                50            50        1500
        196 Walsh                                50            50        1500
        197 Feeney                               50            50        1500

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
        104 Ernst                                60            60        1400
        103 Hunold                               60            60        1400
        107 Lorentz                              60            60        1400
        106 Pataballa                            60            60        1400
        105 Austin                               60            60        1400
        204 Baer                                 70            70        2700
        176 Taylor                               80            80        2500
        177 Livingston                           80            80        2500
        179 Johnson                              80            80        2500
        175 Hutton                               80            80        2500
        174 Abel                                 80            80        2500

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
        173 Kumar                                80            80        2500
        172 Bates                                80            80        2500
        171 Smith                                80            80        2500
        170 Fox                                  80            80        2500
        169 Bloom                                80            80        2500
        168 Ozer                                 80            80        2500
        145 Russell                              80            80        2500
        146 Partners                             80            80        2500
        147 Errazuriz                            80            80        2500
        148 Cambrault                            80            80        2500
        149 Zlotkey                              80            80        2500

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
        150 Tucker                               80            80        2500
        151 Bernstein                            80            80        2500
        152 Hall                                 80            80        2500
        153 Olsen                                80            80        2500
        154 Cambrault                            80            80        2500
        155 Tuvault                              80            80        2500
        156 King                                 80            80        2500
        157 Sully                                80            80        2500
        158 McEwen                               80            80        2500
        159 Smith                                80            80        2500
        160 Doran                                80            80        2500

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
        161 Sewall                               80            80        2500
        162 Vishney                              80            80        2500
        163 Greene                               80            80        2500
        164 Marvins                              80            80        2500
        165 Lee                                  80            80        2500
        166 Ande                                 80            80        2500
        167 Banda                                80            80        2500
        101 Kochhar                              90            90        1700
        100 King                                 90            90        1700
        102 De Haan                              90            90        1700
        110 Chen                                100           100        1700

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
        108 Greenberg                           100           100        1700
        111 Sciarra                             100           100        1700
        112 Urman                               100           100        1700
        113 Popp                                100           100        1700
        109 Faviet                              100           100        1700
        206 Gietz                               110           110        1700
        205 Higgins                             110           110        1700

106 rows selected.
ORACLE ON CLAUSE 模仿NATURAL CLAUSE
SQL> 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) AND (e .manager_id = d.manager_id);

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
        202 Fay                                  20            20        1800
        206 Gietz                               110           110        1700
        101 Kochhar                              90            90        1700
        102 De Haan                              90            90        1700
        104 Ernst                                60            60        1400
        105 Austin                               60            60        1400
        106 Pataballa                            60            60        1400
        107 Lorentz                              60            60        1400
        109 Faviet                              100           100        1700
        110 Chen                                100           100        1700
        111 Sciarra                             100           100        1700

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
        112 Urman                               100           100        1700
        113 Popp                                100           100        1700
        115 Khoo                                 30            30        1700
        116 Baida                                30            30        1700
        117 Tobias                               30            30        1700
        118 Himuro                               30            30        1700
        119 Colmenares                           30            30        1700
        129 Bissot                               50            50        1500
        130 Atkinson                             50            50        1500
        131 Marlow                               50            50        1500
        132 Olson                                50            50        1500

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_ID LOCATION_ID
----------- ------------------------- ------------- ------------- -----------
        150 Tucker                               80            80        2500
        151 Bernstein                            80            80        2500
        152 Hall                                 80            80        2500
        153 Olsen                                80            80        2500
        154 Cambrault                            80            80        2500
        155 Tuvault                              80            80        2500
        184 Sarchand                             50            50        1500
        185 Bull                                 50            50        1500
        186 Dellinger                            50            50        1500
        187 Cabrio                               50            50        1500

32 rows selected.

Joining More than Two Tables

More than two tables join
SQL> SELECT e.employee_id,e.last_name,d.department_id,d.department_name,l.location_id,l.city
  2  FROM employees e JOIN departments d ON (e.department_id = d.department_id) JOIN locations l ON (d.location_id = l.location_id);

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
----------- ------------------------- ------------- -------------------- ----------- ------------------------------
        198 OConnell                             50 Shipping                    1500 South San Francisco
        199 Grant                                50 Shipping                    1500 South San Francisco
        200 Whalen                               10 Administration              1700 Seattle
        201 Hartstein                            20 Marketing                   1800 Toronto
        202 Fay                                  20 Marketing                   1800 Toronto
        203 Mavris                               40 Human Resources             2400 London
        204 Baer                                 70 Public Relations            2700 Munich
        205 Higgins                             110 Accounting                  1700 Seattle
        206 Gietz                               110 Accounting                  1700 Seattle
        100 King                                 90 Executive                   1700 Seattle
        101 Kochhar                              90 Executive                   1700 Seattle

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
----------- ------------------------- ------------- -------------------- ----------- ------------------------------
        102 De Haan                              90 Executive                   1700 Seattle
        103 Hunold                               60 IT                          1400 Southlake
        104 Ernst                                60 IT                          1400 Southlake
        105 Austin                               60 IT                          1400 Southlake
        106 Pataballa                            60 IT                          1400 Southlake
        107 Lorentz                              60 IT                          1400 Southlake
        108 Greenberg                           100 Finance                     1700 Seattle
        109 Faviet                              100 Finance                     1700 Seattle
        110 Chen                                100 Finance                     1700 Seattle
        111 Sciarra                             100 Finance                     1700 Seattle
        112 Urman                               100 Finance                     1700 Seattle

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
----------- ------------------------- ------------- -------------------- ----------- ------------------------------
        113 Popp                                100 Finance                     1700 Seattle
        114 Raphaely                             30 Purchasing                  1700 Seattle
        115 Khoo                                 30 Purchasing                  1700 Seattle
        116 Baida                                30 Purchasing                  1700 Seattle
        117 Tobias                               30 Purchasing                  1700 Seattle
        118 Himuro                               30 Purchasing                  1700 Seattle
        119 Colmenares                           30 Purchasing                  1700 Seattle
        120 Weiss                                50 Shipping                    1500 South San Francisco
        121 Fripp                                50 Shipping                    1500 South San Francisco
        122 Kaufling                             50 Shipping                    1500 South San Francisco
        123 Vollman                              50 Shipping                    1500 South San Francisco

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
----------- ------------------------- ------------- -------------------- ----------- ------------------------------
        124 Mourgos                              50 Shipping                    1500 South San Francisco
        125 Nayer                                50 Shipping                    1500 South San Francisco
        126 Mikkilineni                          50 Shipping                    1500 South San Francisco
        127 Landry                               50 Shipping                    1500 South San Francisco
        128 Markle                               50 Shipping                    1500 South San Francisco
        129 Bissot                               50 Shipping                    1500 South San Francisco
        130 Atkinson                             50 Shipping                    1500 South San Francisco
        131 Marlow                               50 Shipping                    1500 South San Francisco
        132 Olson                                50 Shipping                    1500 South San Francisco
        133 Mallin                               50 Shipping                    1500 South San Francisco
        134 Rogers                               50 Shipping                    1500 South San Francisco

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
----------- ------------------------- ------------- -------------------- ----------- ------------------------------
        135 Gee                                  50 Shipping                    1500 South San Francisco
        136 Philtanker                           50 Shipping                    1500 South San Francisco
        137 Ladwig                               50 Shipping                    1500 South San Francisco
        138 Stiles                               50 Shipping                    1500 South San Francisco
        139 Seo                                  50 Shipping                    1500 South San Francisco
        140 Patel                                50 Shipping                    1500 South San Francisco
        141 Rajs                                 50 Shipping                    1500 South San Francisco
        142 Davies                               50 Shipping                    1500 South San Francisco
        143 Matos                                50 Shipping                    1500 South San Francisco
        144 Vargas                               50 Shipping                    1500 South San Francisco
        145 Russell                              80 Sales                       2500 Oxford

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
----------- ------------------------- ------------- -------------------- ----------- ------------------------------
        146 Partners                             80 Sales                       2500 Oxford
        147 Errazuriz                            80 Sales                       2500 Oxford
        148 Cambrault                            80 Sales                       2500 Oxford
        149 Zlotkey                              80 Sales                       2500 Oxford
        150 Tucker                               80 Sales                       2500 Oxford
        151 Bernstein                            80 Sales                       2500 Oxford
        152 Hall                                 80 Sales                       2500 Oxford

        153 Olsen                                80 Sales                       2500 Oxford
        154 Cambrault                            80 Sales                       2500 Oxford
        155 Tuvault                              80 Sales                       2500 Oxford
        156 King                                 80 Sales                       2500 Oxford

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
----------- ------------------------- ------------- -------------------- ----------- ------------------------------
        157 Sully                                80 Sales                       2500 Oxford
        158 McEwen                               80 Sales                       2500 Oxford
        159 Smith                                80 Sales                       2500 Oxford
        160 Doran                                80 Sales                       2500 Oxford
        161 Sewall                               80 Sales                       2500 Oxford
        162 Vishney                              80 Sales                       2500 Oxford
        163 Greene                               80 Sales                       2500 Oxford
        164 Marvins                              80 Sales                       2500 Oxford
        165 Lee                                  80 Sales                       2500 Oxford
        166 Ande                                 80 Sales                       2500 Oxford
        167 Banda                                80 Sales                       2500 Oxford

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
----------- ------------------------- ------------- -------------------- ----------- ------------------------------
        168 Ozer                                 80 Sales                       2500 Oxford
        169 Bloom                                80 Sales                       2500 Oxford
        170 Fox                                  80 Sales                       2500 Oxford
        171 Smith                                80 Sales                       2500 Oxford
        172 Bates                                80 Sales                       2500 Oxford
        173 Kumar                                80 Sales                       2500 Oxford
        174 Abel                                 80 Sales                       2500 Oxford
        175 Hutton                               80 Sales                       2500 Oxford
        176 Taylor                               80 Sales                       2500 Oxford
        177 Livingston                           80 Sales                       2500 Oxford
        179 Johnson                              80 Sales                       2500 Oxford

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
----------- ------------------------- ------------- -------------------- ----------- ------------------------------
        180 Taylor                               50 Shipping                    1500 South San Francisco
        181 Fleaur                               50 Shipping                    1500 South San Francisco
        182 Sullivan                             50 Shipping                    1500 South San Francisco
        183 Geoni                                50 Shipping                    1500 South San Francisco
        184 Sarchand                             50 Shipping                    1500 South San Francisco
        185 Bull                                 50 Shipping                    1500 South San Francisco
        186 Dellinger                            50 Shipping                    1500 South San Francisco
        187 Cabrio                               50 Shipping                    1500 South San Francisco
        188 Chung                                50 Shipping                    1500 South San Francisco
        189 Dilly                                50 Shipping                    1500 South San Francisco
        190 Gates                                50 Shipping                    1500 South San Francisco

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
----------- ------------------------- ------------- -------------------- ----------- ------------------------------
        191 Perkins                              50 Shipping                    1500 South San Francisco
        192 Bell                                 50 Shipping                    1500 South San Francisco
        193 Everett                              50 Shipping                    1500 South San Francisco
        194 McCain                               50 Shipping                    1500 South San Francisco
        195 Jones                                50 Shipping                    1500 South San Francisco
        196 Walsh                                50 Shipping                    1500 South San Francisco
        197 Feeney                               50 Shipping                    1500 South San Francisco

106 rows selected.

To join n tables togerther,you need a minimum of n-1 join conditions.For example,to join three tables,a minimum of two joins is required.

Oracle Syntax
SQL> SELECT e.employee_id,e.last_name,d.department_id,d.department_name,l.location_id,l.city
  2  FROM employees e,departments d,locations l
  3  WHERE e.department_id = d.department_id AND d.location_id = l.location_id;

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
----------- ------------------------- ------------- -------------------- ----------- ------------------------------
        198 OConnell                             50 Shipping                    1500 South San Francisco
        199 Grant                                50 Shipping                    1500 South San Francisco
        200 Whalen                               10 Administration              1700 Seattle
        201 Hartstein                            20 Marketing                   1800 Toronto
        202 Fay                                  20 Marketing                   1800 Toronto
        203 Mavris                               40 Human Resources             2400 London
        204 Baer                                 70 Public Relations            2700 Munich
        205 Higgins                             110 Accounting                  1700 Seattle
        206 Gietz                               110 Accounting                  1700 Seattle
        100 King                                 90 Executive                   1700 Seattle
        101 Kochhar                              90 Executive                   1700 Seattle

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
----------- ------------------------- ------------- -------------------- ----------- ------------------------------
        102 De Haan                              90 Executive                   1700 Seattle
        103 Hunold                               60 IT                          1400 Southlake
        104 Ernst                                60 IT                          1400 Southlake
        105 Austin                               60 IT                          1400 Southlake
        106 Pataballa                            60 IT                          1400 Southlake
        107 Lorentz                              60 IT                          1400 Southlake
        108 Greenberg                           100 Finance                     1700 Seattle
        109 Faviet                              100 Finance                     1700 Seattle
        110 Chen                                100 Finance                     1700 Seattle
        111 Sciarra                             100 Finance                     1700 Seattle
        112 Urman                               100 Finance                     1700 Seattle

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
----------- ------------------------- ------------- -------------------- ----------- ------------------------------
        113 Popp                                100 Finance                     1700 Seattle
        114 Raphaely                             30 Purchasing                  1700 Seattle
        115 Khoo                                 30 Purchasing                  1700 Seattle
        116 Baida                                30 Purchasing                  1700 Seattle
        117 Tobias                               30 Purchasing                  1700 Seattle
        118 Himuro                               30 Purchasing                  1700 Seattle
        119 Colmenares                           30 Purchasing                  1700 Seattle
        120 Weiss                                50 Shipping                    1500 South San Francisco
        121 Fripp                                50 Shipping                    1500 South San Francisco
        122 Kaufling                             50 Shipping                    1500 South San Francisco
        123 Vollman                              50 Shipping                    1500 South San Francisco

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
----------- ------------------------- ------------- -------------------- ----------- ------------------------------
        124 Mourgos                              50 Shipping                    1500 South San Francisco
        125 Nayer                                50 Shipping                    1500 South San Francisco
        126 Mikkilineni                          50 Shipping                    1500 South San Francisco
        127 Landry                               50 Shipping                    1500 South San Francisco
        128 Markle                               50 Shipping                    1500 South San Francisco
        129 Bissot                               50 Shipping                    1500 South San Francisco
        130 Atkinson                             50 Shipping                    1500 South San Francisco
        131 Marlow                               50 Shipping                    1500 South San Francisco
        132 Olson                                50 Shipping                    1500 South San Francisco
        133 Mallin                               50 Shipping                    1500 South San Francisco
        134 Rogers                               50 Shipping                    1500 South San Francisco

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
----------- ------------------------- ------------- -------------------- ----------- ------------------------------
        135 Gee                                  50 Shipping                    1500 South San Francisco
        136 Philtanker                           50 Shipping                    1500 South San Francisco
        137 Ladwig                               50 Shipping                    1500 South San Francisco
        138 Stiles                               50 Shipping                    1500 South San Francisco
        139 Seo                                  50 Shipping                    1500 South San Francisco
        140 Patel                                50 Shipping                    1500 South San Francisco
        141 Rajs                                 50 Shipping                    1500 South San Francisco
        142 Davies                               50 Shipping                    1500 South San Francisco
        143 Matos                                50 Shipping                    1500 South San Francisco
        144 Vargas                               50 Shipping                    1500 South San Francisco
        145 Russell                              80 Sales                       2500 Oxford

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
----------- ------------------------- ------------- -------------------- ----------- ------------------------------
        146 Partners                             80 Sales                       2500 Oxford
        147 Errazuriz                            80 Sales                       2500 Oxford
        148 Cambrault                            80 Sales                       2500 Oxford
        149 Zlotkey                              80 Sales                       2500 Oxford
        150 Tucker                               80 Sales                       2500 Oxford
        151 Bernstein                            80 Sales                       2500 Oxford
        152 Hall                                 80 Sales                       2500 Oxford
        153 Olsen                                80 Sales                       2500 Oxford
        154 Cambrault                            80 Sales                       2500 Oxford
        155 Tuvault                              80 Sales                       2500 Oxford
        156 King                                 80 Sales                       2500 Oxford

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
----------- ------------------------- ------------- -------------------- ----------- ------------------------------
        157 Sully                                80 Sales                       2500 Oxford
        158 McEwen                               80 Sales                       2500 Oxford
        159 Smith                                80 Sales                       2500 Oxford
        160 Doran                                80 Sales                       2500 Oxford
        161 Sewall                               80 Sales                       2500 Oxford
        162 Vishney                              80 Sales                       2500 Oxford
        163 Greene                               80 Sales                       2500 Oxford
        164 Marvins                              80 Sales                       2500 Oxford
        165 Lee                                  80 Sales                       2500 Oxford
        166 Ande                                 80 Sales                       2500 Oxford
        167 Banda                                80 Sales                       2500 Oxford

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
----------- ------------------------- ------------- -------------------- ----------- ------------------------------
        168 Ozer                                 80 Sales                       2500 Oxford
        169 Bloom                                80 Sales                       2500 Oxford
        170 Fox                                  80 Sales                       2500 Oxford
        171 Smith                                80 Sales                       2500 Oxford
        172 Bates                                80 Sales                       2500 Oxford
        173 Kumar                                80 Sales                       2500 Oxford
        174 Abel                                 80 Sales                       2500 Oxford
        175 Hutton                               80 Sales                       2500 Oxford
        176 Taylor                               80 Sales                       2500 Oxford
        177 Livingston                           80 Sales                       2500 Oxford
        179 Johnson                              80 Sales                       2500 Oxford

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
----------- ------------------------- ------------- -------------------- ----------- ------------------------------
        180 Taylor                               50 Shipping                    1500 South San Francisco
        181 Fleaur                               50 Shipping                    1500 South San Francisco
        182 Sullivan                             50 Shipping                    1500 South San Francisco
        183 Geoni                                50 Shipping                    1500 South San Francisco
        184 Sarchand                             50 Shipping                    1500 South San Francisco
        185 Bull                                 50 Shipping                    1500 South San Francisco
        186 Dellinger                            50 Shipping                    1500 South San Francisco
        187 Cabrio                               50 Shipping                    1500 South San Francisco
        188 Chung                                50 Shipping                    1500 South San Francisco
        189 Dilly                                50 Shipping                    1500 South San Francisco
        190 Gates                                50 Shipping                    1500 South San Francisco

EMPLOYEE_ID LAST_NAME                 DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_ID CITY
----------- ------------------------- ------------- -------------------- ----------- ------------------------------
        191 Perkins                              50 Shipping                    1500 South San Francisco
        192 Bell                                 50 Shipping                    1500 South San Francisco
        193 Everett                              50 Shipping                    1500 South San Francisco
        194 McCain                               50 Shipping                    1500 South San Francisco
        195 Jones                                50 Shipping                    1500 South San Francisco
        196 Walsh                                50 Shipping                    1500 South San Francisco
        197 Feeney                               50 Shipping                    1500 South San Francisco

106 rows selected.

Oracle server多张表进行Join的原理解析:不管多少张表,Oracle Server首先进行前两张表进行Join,产生一张新表,然后将产生的新表和第三张表进行Join,以此类推.

 

 

 

posted @ 2013-04-15 15:38  ArcerZhang  阅读(161)  评论(0编辑  收藏  举报