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，以此类推.
