Chapter 06-Displaying Data From multiple Tables - 03
Applying Additional Condtions to a Join
Use the AND cluause or the WHERE clause to aplly additional condtions:
AND CLAUSE
SQL> SELECT e.employee_id,e.last_name,d.department_id,d.department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) AND e.manager_id = 149; EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ----------- ------------------------- ------------- -------------------- 174 Abel 80 Sales 175 Hutton 80 Sales 179 Johnson 80 Sales 177 Livingston 80 Sales 176 Taylor 80 Sales
WHERE CLAUSE
SQL> SELECT e.employee_id,e.last_name,d.department_id,d.department_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) WHERE e.manager_id = 149; EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ----------- ------------------------- ------------- -------------------- 174 Abel 80 Sales 175 Hutton 80 Sales 179 Johnson 80 Sales 177 Livingston 80 Sales 176 Taylor 80 Sales
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 AND e.manager_id = 149; EMPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ----------- ------------------------- ------------- -------------------- 174 Abel 80 Sales 175 Hutton 80 Sales 179 Johnson 80 Sales 177 Livingston 80 Sales 176 Taylor 80 Sales
Joining a Table to itself
MANAGER_ID in the WORKER table is equal to EMPLOYEE_ID in the MANGER table.
Self-Joins Using the ON Clause
ON Clause
SQL> SELECT worker.last_name emp,manger.last_name mgr FROM employees worker JOIN employees manger ON (worker.manager_id = manger.employee_id); EMP MGR ------------------------- ------------------------- Smith Cambrault Ozer Cambrault Kumar Cambrault Fox Cambrault Bloom Cambrault Bates Cambrault Hunold De Haan Vishney Errazuriz Marvins Errazuriz Lee Errazuriz Greene Errazuriz EMP MGR ------------------------- ------------------------- Banda Errazuriz Ande Errazuriz Sarchand Fripp Olson Fripp Marlow Fripp Dellinger Fripp Cabrio Fripp Bull Fripp Bissot Fripp Atkinson Fripp Urman Greenberg EMP MGR ------------------------- ------------------------- Sciarra Greenberg Popp Greenberg Faviet Greenberg Chen Greenberg Fay Hartstein Gietz Higgins Pataballa Hunold Lorentz Hunold Ernst Hunold Austin Hunold Rogers Kaufling EMP MGR ------------------------- ------------------------- Philtanker Kaufling Perkins Kaufling Mallin Kaufling Gee Kaufling Gates Kaufling Dilly Kaufling Chung Kaufling Zlotkey King Weiss King Vollman King Russell King EMP MGR ------------------------- ------------------------- Raphaely King Partners King Mourgos King Kochhar King Kaufling King Hartstein King Fripp King Errazuriz King De Haan King Cambrault King Whalen Kochhar EMP MGR ------------------------- ------------------------- Mavris Kochhar Higgins Kochhar Greenberg Kochhar Baer Kochhar Walsh Mourgos Vargas Mourgos Rajs Mourgos OConnell Mourgos Matos Mourgos Grant Mourgos Feeney Mourgos EMP MGR ------------------------- ------------------------- Davies Mourgos Sully Partners Smith Partners Sewall Partners McEwen Partners King Partners Doran Partners Tobias Raphaely Khoo Raphaely Himuro Raphaely Colmenares Raphaely EMP MGR ------------------------- ------------------------- Baida Raphaely Tuvault Russell Tucker Russell Olsen Russell Hall Russell Cambrault Russell Bernstein Russell Stiles Vollman Seo Vollman Patel Vollman McCain Vollman EMP MGR ------------------------- ------------------------- Ladwig Vollman Jones Vollman Everett Vollman Bell Vollman Taylor Weiss Sullivan Weiss Nayer Weiss Mikkilineni Weiss Markle Weiss Landry Weiss Geoni Weiss EMP MGR ------------------------- ------------------------- Fleaur Weiss Taylor Zlotkey Livingston Zlotkey Johnson Zlotkey Hutton Zlotkey Grant Zlotkey Abel Zlotkey 106 rows selected.
Oracle Syntax
SQL> SELECT worker.last_name || ' works for ' || manager.last_name FROM employees worker,employees manager WHERE worker.manager_id = manager.employee_id; WORKER.LAST_NAME||'WORKSFOR'||MANAGER.LAST_NAME ------------------------------------------------------------- Smith works for Cambrault Ozer works for Cambrault Kumar works for Cambrault Fox works for Cambrault Bloom works for Cambrault Bates works for Cambrault Hunold works for De Haan Vishney works for Errazuriz Marvins works for Errazuriz Lee works for Errazuriz Greene works for Errazuriz WORKER.LAST_NAME||'WORKSFOR'||MANAGER.LAST_NAME ------------------------------------------------------------- Banda works for Errazuriz Ande works for Errazuriz Sarchand works for Fripp Olson works for Fripp Marlow works for Fripp Dellinger works for Fripp Cabrio works for Fripp Bull works for Fripp Bissot works for Fripp Atkinson works for Fripp Urman works for Greenberg WORKER.LAST_NAME||'WORKSFOR'||MANAGER.LAST_NAME ------------------------------------------------------------- Sciarra works for Greenberg Popp works for Greenberg Faviet works for Greenberg Chen works for Greenberg Fay works for Hartstein Gietz works for Higgins Pataballa works for Hunold Lorentz works for Hunold Ernst works for Hunold Austin works for Hunold Rogers works for Kaufling WORKER.LAST_NAME||'WORKSFOR'||MANAGER.LAST_NAME ------------------------------------------------------------- Philtanker works for Kaufling Perkins works for Kaufling Mallin works for Kaufling Gee works for Kaufling Gates works for Kaufling Dilly works for Kaufling Chung works for Kaufling Zlotkey works for King Weiss works for King Vollman works for King Russell works for King WORKER.LAST_NAME||'WORKSFOR'||MANAGER.LAST_NAME ------------------------------------------------------------- Raphaely works for King Partners works for King Mourgos works for King Kochhar works for King Kaufling works for King Hartstein works for King Fripp works for King Errazuriz works for King De Haan works for King Cambrault works for King Whalen works for Kochhar WORKER.LAST_NAME||'WORKSFOR'||MANAGER.LAST_NAME ------------------------------------------------------------- Mavris works for Kochhar Higgins works for Kochhar Greenberg works for Kochhar Baer works for Kochhar Walsh works for Mourgos Vargas works for Mourgos Rajs works for Mourgos OConnell works for Mourgos Matos works for Mourgos Grant works for Mourgos Feeney works for Mourgos WORKER.LAST_NAME||'WORKSFOR'||MANAGER.LAST_NAME ------------------------------------------------------------- Davies works for Mourgos Sully works for Partners Smith works for Partners Sewall works for Partners McEwen works for Partners King works for Partners Doran works for Partners Tobias works for Raphaely Khoo works for Raphaely Himuro works for Raphaely Colmenares works for Raphaely WORKER.LAST_NAME||'WORKSFOR'||MANAGER.LAST_NAME ------------------------------------------------------------- Baida works for Raphaely Tuvault works for Russell Tucker works for Russell Olsen works for Russell Hall works for Russell Cambrault works for Russell Bernstein works for Russell Stiles works for Vollman Seo works for Vollman Patel works for Vollman McCain works for Vollman WORKER.LAST_NAME||'WORKSFOR'||MANAGER.LAST_NAME ------------------------------------------------------------- Ladwig works for Vollman Jones works for Vollman Everett works for Vollman Bell works for Vollman Taylor works for Weiss Sullivan works for Weiss Nayer works for Weiss Mikkilineni works for Weiss Markle works for Weiss Landry works for Weiss Geoni works for Weiss WORKER.LAST_NAME||'WORKSFOR'||MANAGER.LAST_NAME ------------------------------------------------------------- Fleaur works for Weiss Taylor works for Zlotkey Livingston works for Zlotkey Johnson works for Zlotkey Hutton works for Zlotkey Grant works for Zlotkey Abel works for Zlotkey 106 rows selected.
Nonequijoins
Create JOB_GRADES
CREATE TABLE JOB_GRADES ( GRADE_LEVEL CHAR(1) PRIMARY KEY, LOWEST_SAL NUMBER(8) NOT NULL, HIGHEST_SAL NUMBER(8) NOT NULL ); INSERT INTO JOB_GRADES VALUES('A',1000,2999); INSERT INTO JOB_GRADES VALUES('B',3000,5999); INSERT INTO JOB_GRADES VALUES('C',6000,9999); INSERT INTO JOB_GRADES VALUES('D',10000,14999); INSERT INTO JOB_GRADES VALUES('E',15000,24999); INSERT INTO JOB_GRADES VALUES('F',25000,40000);
SELECT JOB_GRADES
SQL> SELECT GRADE_LEVEL,LOWEST_SAL,HIGHEST_SAL FROM JOB_GRADES; G LOWEST_SAL HIGHEST_SAL - ---------- ----------- A 1000 2999 B 3000 5999 C 6000 9999 D 10000 14999 E 15000 24999 F 25000 40000 6 rows selected.
JOB_GRADES table defines the LOWEST_SAL and HIGHEST_SAL range of values for each GRADE_LEVEL.Hence,the GRADE_LEVEL column can be used to assign grades to each employee.
Retrieving Records with Nonequijoins
View Code
SQL> SELECT e.employee_id,e.last_name,e.salary,j.grade_level 2 FROM employees e JOIN job_grades j 3 ON e.salary BETWEEN j.lowest_sal AND j.highest_sal; EMPLOYEE_ID LAST_NAME SALARY G ----------- ------------------------- ---------- - 132 Olson 2100 A 136 Philtanker 2200 A 128 Markle 2200 A 127 Landry 2400 A 135 Gee 2400 A 191 Perkins 2500 A 119 Colmenares 2500 A 140 Patel 2500 A 144 Vargas 2500 A 182 Sullivan 2500 A 131 Marlow 2500 A EMPLOYEE_ID LAST_NAME SALARY G ----------- ------------------------- ---------- - 198 OConnell 2600 A 199 Grant 2600 A 118 Himuro 2600 A 143 Matos 2600 A 126 Mikkilineni 2700 A 139 Seo 2700 A 117 Tobias 2800 A 183 Geoni 2800 A 130 Atkinson 2800 A 195 Jones 2800 A 116 Baida 2900 A EMPLOYEE_ID LAST_NAME SALARY G ----------- ------------------------- ---------- - 134 Rogers 2900 A 190 Gates 2900 A 197 Feeney 3000 B 187 Cabrio 3000 B 181 Fleaur 3100 B 196 Walsh 3100 B 115 Khoo 3100 B 142 Davies 3100 B 194 McCain 3200 B 125 Nayer 3200 B 138 Stiles 3200 B EMPLOYEE_ID LAST_NAME SALARY G ----------- ------------------------- ---------- - 180 Taylor 3200 B 133 Mallin 3300 B 129 Bissot 3300 B 186 Dellinger 3400 B 141 Rajs 3500 B 189 Dilly 3600 B 137 Ladwig 3600 B 188 Chung 3800 B 193 Everett 3900 B 192 Bell 4000 B 185 Bull 4100 B EMPLOYEE_ID LAST_NAME SALARY G ----------- ------------------------- ---------- - 107 Lorentz 4200 B 184 Sarchand 4200 B 200 Whalen 4400 B 105 Austin 4800 B 106 Pataballa 4800 B 124 Mourgos 5800 B 202 Fay 6000 C 104 Ernst 6000 C 173 Kumar 6100 C 167 Banda 6200 C 179 Johnson 6200 C EMPLOYEE_ID LAST_NAME SALARY G ----------- ------------------------- ---------- - 166 Ande 6400 C 123 Vollman 6500 C 203 Mavris 6500 C 165 Lee 6800 C 113 Popp 6900 C 155 Tuvault 7000 C 178 Grant 7000 C 161 Sewall 7000 C 164 Marvins 7200 C 172 Bates 7300 C 171 Smith 7400 C EMPLOYEE_ID LAST_NAME SALARY G ----------- ------------------------- ---------- - 154 Cambrault 7500 C 160 Doran 7500 C 111 Sciarra 7700 C 112 Urman 7800 C 122 Kaufling 7900 C 159 Smith 8000 C 153 Olsen 8000 C 120 Weiss 8000 C 121 Fripp 8200 C 110 Chen 8200 C 206 Gietz 8300 C EMPLOYEE_ID LAST_NAME SALARY G ----------- ------------------------- ---------- - 177 Livingston 8400 C 176 Taylor 8600 C 175 Hutton 8800 C 109 Faviet 9000 C 103 Hunold 9000 C 152 Hall 9000 C 158 McEwen 9000 C 157 Sully 9500 C 151 Bernstein 9500 C 163 Greene 9500 C 170 Fox 9600 C EMPLOYEE_ID LAST_NAME SALARY G ----------- ------------------------- ---------- - 204 Baer 10000 D 169 Bloom 10000 D 156 King 10000 D 150 Tucker 10000 D 162 Vishney 10500 D 149 Zlotkey 10500 D 148 Cambrault 11000 D 174 Abel 11000 D 114 Raphaely 11000 D 168 Ozer 11500 D 147 Errazuriz 12000 D EMPLOYEE_ID LAST_NAME SALARY G ----------- ------------------------- ---------- - 108 Greenberg 12008 D 205 Higgins 12008 D 201 Hartstein 13000 D 146 Partners 13500 D 145 Russell 14000 D 102 De Haan 17000 E 101 Kochhar 17000 E 100 King 24000 E 107 rows selected.
Retrieving Records with Nonequijoins Oracle Syntax
Oracle Syntax
SQL> SELECT e.employee_id,e.last_name,e.salary,j.grade_level 2 FROM employees e ,job_grades j 3 WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal; EMPLOYEE_ID LAST_NAME SALARY G ----------- ------------------------- ---------- - 132 Olson 2100 A 136 Philtanker 2200 A 128 Markle 2200 A 127 Landry 2400 A 135 Gee 2400 A 191 Perkins 2500 A 119 Colmenares 2500 A 140 Patel 2500 A 144 Vargas 2500 A 182 Sullivan 2500 A 131 Marlow 2500 A EMPLOYEE_ID LAST_NAME SALARY G ----------- ------------------------- ---------- - 198 OConnell 2600 A 199 Grant 2600 A 118 Himuro 2600 A 143 Matos 2600 A 126 Mikkilineni 2700 A 139 Seo 2700 A 117 Tobias 2800 A 183 Geoni 2800 A 130 Atkinson 2800 A 195 Jones 2800 A 116 Baida 2900 A EMPLOYEE_ID LAST_NAME SALARY G ----------- ------------------------- ---------- - 134 Rogers 2900 A 190 Gates 2900 A 197 Feeney 3000 B 187 Cabrio 3000 B 181 Fleaur 3100 B 196 Walsh 3100 B 115 Khoo 3100 B 142 Davies 3100 B 194 McCain 3200 B 125 Nayer 3200 B 138 Stiles 3200 B EMPLOYEE_ID LAST_NAME SALARY G ----------- ------------------------- ---------- - 180 Taylor 3200 B 133 Mallin 3300 B 129 Bissot 3300 B 186 Dellinger 3400 B 141 Rajs 3500 B 189 Dilly 3600 B 137 Ladwig 3600 B 188 Chung 3800 B 193 Everett 3900 B 192 Bell 4000 B 185 Bull 4100 B EMPLOYEE_ID LAST_NAME SALARY G ----------- ------------------------- ---------- - 107 Lorentz 4200 B 184 Sarchand 4200 B 200 Whalen 4400 B 105 Austin 4800 B 106 Pataballa 4800 B 124 Mourgos 5800 B 202 Fay 6000 C 104 Ernst 6000 C 173 Kumar 6100 C 167 Banda 6200 C 179 Johnson 6200 C EMPLOYEE_ID LAST_NAME SALARY G ----------- ------------------------- ---------- - 166 Ande 6400 C 123 Vollman 6500 C 203 Mavris 6500 C 165 Lee 6800 C 113 Popp 6900 C 155 Tuvault 7000 C 178 Grant 7000 C 161 Sewall 7000 C 164 Marvins 7200 C 172 Bates 7300 C 171 Smith 7400 C EMPLOYEE_ID LAST_NAME SALARY G ----------- ------------------------- ---------- - 154 Cambrault 7500 C 160 Doran 7500 C 111 Sciarra 7700 C 112 Urman 7800 C 122 Kaufling 7900 C 159 Smith 8000 C 153 Olsen 8000 C 120 Weiss 8000 C 121 Fripp 8200 C 110 Chen 8200 C 206 Gietz 8300 C EMPLOYEE_ID LAST_NAME SALARY G ----------- ------------------------- ---------- - 177 Livingston 8400 C 176 Taylor 8600 C 175 Hutton 8800 C 109 Faviet 9000 C 103 Hunold 9000 C 152 Hall 9000 C 158 McEwen 9000 C 157 Sully 9500 C 151 Bernstein 9500 C 163 Greene 9500 C 170 Fox 9600 C EMPLOYEE_ID LAST_NAME SALARY G ----------- ------------------------- ---------- - 204 Baer 10000 D 169 Bloom 10000 D 156 King 10000 D 150 Tucker 10000 D 162 Vishney 10500 D 149 Zlotkey 10500 D 148 Cambrault 11000 D 174 Abel 11000 D 114 Raphaely 11000 D 168 Ozer 11500 D 147 Errazuriz 12000 D EMPLOYEE_ID LAST_NAME SALARY G ----------- ------------------------- ---------- - 108 Greenberg 12008 D 205 Higgins 12008 D 201 Hartstein 13000 D 146 Partners 13500 D 145 Russell 14000 D 102 De Haan 17000 E 101 Kochhar 17000 E 100 King 24000 E 107 rows selected.
Returning Records with No Direct Match with Outer Joins
INNER Versus OUTER Joins
In SQL 1999,the join of two tables returning only matched rows is called an inner join.
A join between two tables that returns the results of the inner join as well as the unmatched rows from the left(or right) table is called a left (or right) outer join.
A join between two tables that returns the results of an inner join as well as the results of a left and right join is a full outer join.
LEFT | RIGHT | FULL OUTER JOIN
INNER JOIN (106 Records)
SQL> SELECT e.last_name, e.department_id,d.department_name 2 FROM employees e INNER JOIN departments d 3 ON (e.department_id = d.department_id); LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Whalen 10 Administration Fay 20 Marketing Hartstein 20 Marketing Tobias 30 Purchasing Colmenares 30 Purchasing Baida 30 Purchasing Raphaely 30 Purchasing Khoo 30 Purchasing Himuro 30 Purchasing Mavris 40 Human Resources Feeney 50 Shipping LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Fleaur 50 Shipping Fripp 50 Shipping Gates 50 Shipping Gee 50 Shipping Geoni 50 Shipping Grant 50 Shipping Jones 50 Shipping Kaufling 50 Shipping Ladwig 50 Shipping Everett 50 Shipping Dilly 50 Shipping LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Dellinger 50 Shipping Davies 50 Shipping Chung 50 Shipping Cabrio 50 Shipping Bull 50 Shipping Bissot 50 Shipping Bell 50 Shipping Atkinson 50 Shipping Landry 50 Shipping Weiss 50 Shipping Walsh 50 Shipping LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Vollman 50 Shipping Vargas 50 Shipping Taylor 50 Shipping Mallin 50 Shipping Markle 50 Shipping Marlow 50 Shipping Matos 50 Shipping McCain 50 Shipping Mikkilineni 50 Shipping Mourgos 50 Shipping Nayer 50 Shipping LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- OConnell 50 Shipping Olson 50 Shipping Patel 50 Shipping Perkins 50 Shipping Philtanker 50 Shipping Rajs 50 Shipping Rogers 50 Shipping Sarchand 50 Shipping Seo 50 Shipping Stiles 50 Shipping Sullivan 50 Shipping LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Austin 60 IT Pataballa 60 IT Ernst 60 IT Lorentz 60 IT Hunold 60 IT Baer 70 Public Relations Abel 80 Sales Ande 80 Sales Banda 80 Sales Bates 80 Sales Bernstein 80 Sales LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Bloom 80 Sales Cambrault 80 Sales Cambrault 80 Sales Doran 80 Sales Errazuriz 80 Sales Fox 80 Sales Greene 80 Sales Hall 80 Sales Hutton 80 Sales Johnson 80 Sales King 80 Sales LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Kumar 80 Sales Lee 80 Sales Livingston 80 Sales Marvins 80 Sales McEwen 80 Sales Olsen 80 Sales Ozer 80 Sales Partners 80 Sales Russell 80 Sales Sewall 80 Sales Smith 80 Sales LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Smith 80 Sales Sully 80 Sales Taylor 80 Sales Tucker 80 Sales Tuvault 80 Sales Vishney 80 Sales Zlotkey 80 Sales Kochhar 90 Executive King 90 Executive De Haan 90 Executive Popp 100 Finance LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Urman 100 Finance Chen 100 Finance Faviet 100 Finance Sciarra 100 Finance Greenberg 100 Finance Gietz 110 Accounting Higgins 110 Accounting 106 rows selected.
LEFT OUTER JOIN (107 Records)
SQL> SELECT e.last_name, e.department_id,d.department_name 2 FROM employees e LEFT OUTER JOIN departments d 3 ON (e.department_id = d.department_id); LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Whalen 10 Administration Fay 20 Marketing Hartstein 20 Marketing Colmenares 30 Purchasing Himuro 30 Purchasing Tobias 30 Purchasing Baida 30 Purchasing Khoo 30 Purchasing Raphaely 30 Purchasing Mavris 40 Human Resources Feeney 50 Shipping LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Walsh 50 Shipping Jones 50 Shipping McCain 50 Shipping Everett 50 Shipping Bell 50 Shipping Perkins 50 Shipping Gates 50 Shipping Dilly 50 Shipping Chung 50 Shipping Cabrio 50 Shipping Dellinger 50 Shipping LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Bull 50 Shipping Sarchand 50 Shipping Geoni 50 Shipping Sullivan 50 Shipping Fleaur 50 Shipping Taylor 50 Shipping Vargas 50 Shipping Matos 50 Shipping Davies 50 Shipping Rajs 50 Shipping Patel 50 Shipping LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Seo 50 Shipping Stiles 50 Shipping Ladwig 50 Shipping Philtanker 50 Shipping Gee 50 Shipping Rogers 50 Shipping Mallin 50 Shipping Olson 50 Shipping Marlow 50 Shipping Atkinson 50 Shipping Bissot 50 Shipping LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Markle 50 Shipping Landry 50 Shipping Mikkilineni 50 Shipping Nayer 50 Shipping Mourgos 50 Shipping Vollman 50 Shipping Kaufling 50 Shipping Fripp 50 Shipping Weiss 50 Shipping Grant 50 Shipping OConnell 50 Shipping LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Lorentz 60 IT Pataballa 60 IT Austin 60 IT Ernst 60 IT Hunold 60 IT Baer 70 Public Relations Johnson 80 Sales Livingston 80 Sales Taylor 80 Sales Hutton 80 Sales Abel 80 Sales LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Kumar 80 Sales Bates 80 Sales Smith 80 Sales Fox 80 Sales Bloom 80 Sales Ozer 80 Sales Banda 80 Sales Ande 80 Sales Lee 80 Sales Marvins 80 Sales Greene 80 Sales LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Vishney 80 Sales Sewall 80 Sales Doran 80 Sales Smith 80 Sales McEwen 80 Sales Sully 80 Sales King 80 Sales Tuvault 80 Sales Cambrault 80 Sales Olsen 80 Sales Hall 80 Sales LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Bernstein 80 Sales Tucker 80 Sales Zlotkey 80 Sales Cambrault 80 Sales Errazuriz 80 Sales Partners 80 Sales Russell 80 Sales De Haan 90 Executive Kochhar 90 Executive King 90 Executive Popp 100 Finance LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Urman 100 Finance Sciarra 100 Finance Chen 100 Finance Faviet 100 Finance Greenberg 100 Finance Gietz 110 Accounting Higgins 110 Accounting Grant 107 rows selected.
RIGHT OUTER JOIN (122 Records)
SQL> SELECT e.last_name, e.department_id,d.department_name 2 FROM employees e RIGHT OUTER JOIN departments d 3 ON (e.department_id = d.department_id); LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Whalen 10 Administration Fay 20 Marketing Hartstein 20 Marketing Tobias 30 Purchasing Colmenares 30 Purchasing Baida 30 Purchasing Raphaely 30 Purchasing Khoo 30 Purchasing Himuro 30 Purchasing Mavris 40 Human Resources Feeney 50 Shipping LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Fleaur 50 Shipping Fripp 50 Shipping Gates 50 Shipping Gee 50 Shipping Geoni 50 Shipping Grant 50 Shipping Jones 50 Shipping Kaufling 50 Shipping Ladwig 50 Shipping Everett 50 Shipping Dilly 50 Shipping LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Dellinger 50 Shipping Davies 50 Shipping Chung 50 Shipping Cabrio 50 Shipping Bull 50 Shipping Bissot 50 Shipping Bell 50 Shipping Atkinson 50 Shipping Landry 50 Shipping Weiss 50 Shipping Walsh 50 Shipping LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Vollman 50 Shipping Vargas 50 Shipping Taylor 50 Shipping Mallin 50 Shipping Markle 50 Shipping Marlow 50 Shipping Matos 50 Shipping McCain 50 Shipping Mikkilineni 50 Shipping Mourgos 50 Shipping Nayer 50 Shipping LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- OConnell 50 Shipping Olson 50 Shipping Patel 50 Shipping Perkins 50 Shipping Philtanker 50 Shipping Rajs 50 Shipping Rogers 50 Shipping Sarchand 50 Shipping Seo 50 Shipping Stiles 50 Shipping Sullivan 50 Shipping LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Austin 60 IT Pataballa 60 IT Ernst 60 IT Lorentz 60 IT Hunold 60 IT Baer 70 Public Relations Abel 80 Sales Ande 80 Sales Banda 80 Sales Bates 80 Sales Bernstein 80 Sales LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Bloom 80 Sales Cambrault 80 Sales Cambrault 80 Sales Doran 80 Sales Errazuriz 80 Sales Fox 80 Sales Greene 80 Sales Hall 80 Sales Hutton 80 Sales Johnson 80 Sales King 80 Sales LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Kumar 80 Sales Lee 80 Sales Livingston 80 Sales Marvins 80 Sales McEwen 80 Sales Olsen 80 Sales Ozer 80 Sales Partners 80 Sales Russell 80 Sales Sewall 80 Sales Smith 80 Sales LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Smith 80 Sales Sully 80 Sales Taylor 80 Sales Tucker 80 Sales Tuvault 80 Sales Vishney 80 Sales Zlotkey 80 Sales Kochhar 90 Executive King 90 Executive De Haan 90 Executive Popp 100 Finance LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Urman 100 Finance Chen 100 Finance Faviet 100 Finance Sciarra 100 Finance Greenberg 100 Finance Gietz 110 Accounting Higgins 110 Accounting Treasury Corporate Tax Control And Credit Shareholder Services LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Benefits Manufacturing Construction Contracting Operations IT Support NOC IT Helpdesk Government Sales Retail Sales Recruiting LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Payroll 122 rows selected.
FULL OUTER JOIN (Records 123)
SQL> SELECT e.last_name, e.department_id,d.department_name 2 FROM employees e FULL OUTER JOIN departments d 3 SQL> SELECT e.last_name, e.department_id,d.department_name 2 FROM employees e FULL OUTER JOIN departments d 3 ON (e.department_id = d.department_id); LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- OConnell 50 Shipping Grant 50 Shipping Whalen 10 Administration Hartstein 20 Marketing Fay 20 Marketing Mavris 40 Human Resources Baer 70 Public Relations Higgins 110 Accounting Gietz 110 Accounting King 90 Executive Kochhar 90 Executive LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- De Haan 90 Executive Hunold 60 IT Ernst 60 IT Austin 60 IT Pataballa 60 IT Lorentz 60 IT Greenberg 100 Finance Faviet 100 Finance Chen 100 Finance Sciarra 100 Finance Urman 100 Finance LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Popp 100 Finance Raphaely 30 Purchasing Khoo 30 Purchasing Baida 30 Purchasing Tobias 30 Purchasing Himuro 30 Purchasing Colmenares 30 Purchasing Weiss 50 Shipping Fripp 50 Shipping Kaufling 50 Shipping Vollman 50 Shipping LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Mourgos 50 Shipping Nayer 50 Shipping Mikkilineni 50 Shipping Landry 50 Shipping Markle 50 Shipping Bissot 50 Shipping Atkinson 50 Shipping Marlow 50 Shipping Olson 50 Shipping Mallin 50 Shipping Rogers 50 Shipping LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Gee 50 Shipping Philtanker 50 Shipping Ladwig 50 Shipping Stiles 50 Shipping Seo 50 Shipping Patel 50 Shipping Rajs 50 Shipping Davies 50 Shipping Matos 50 Shipping Vargas 50 Shipping Russell 80 Sales LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Partners 80 Sales Errazuriz 80 Sales Cambrault 80 Sales Zlotkey 80 Sales Tucker 80 Sales Bernstein 80 Sales Hall 80 Sales Olsen 80 Sales Cambrault 80 Sales Tuvault 80 Sales King 80 Sales LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Sully 80 Sales McEwen 80 Sales Smith 80 Sales Doran 80 Sales Sewall 80 Sales Vishney 80 Sales Greene 80 Sales Marvins 80 Sales Lee 80 Sales Ande 80 Sales Banda 80 Sales LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Ozer 80 Sales Bloom 80 Sales Fox 80 Sales Smith 80 Sales Bates 80 Sales Kumar 80 Sales Abel 80 Sales Hutton 80 Sales Taylor 80 Sales Livingston 80 Sales Grant LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Johnson 80 Sales Taylor 50 Shipping Fleaur 50 Shipping Sullivan 50 Shipping Geoni 50 Shipping Sarchand 50 Shipping Bull 50 Shipping Dellinger 50 Shipping Cabrio 50 Shipping Chung 50 Shipping Dilly 50 Shipping LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Gates 50 Shipping Perkins 50 Shipping Bell 50 Shipping Everett 50 Shipping McCain 50 Shipping Jones 50 Shipping Walsh 50 Shipping Feeney 50 Shipping NOC Manufacturing Government Sales LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- IT Support Benefits Shareholder Services Retail Sales Control And Credit Recruiting Operations Treasury Payroll Corporate Tax Construction LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME ------------------------- ------------- -------------------- Contracting IT Helpdesk 123 rows selected.
OUTER JOIN:Oracle Syntax
- You use an outer join to see rows that do not meet the join condition.
- The outer join operator is the plus sign (+).
右连接语法:
SELECT table1.column,table2.column FROM table1,table2 WHERE table1.column(+) = table2.column;
左连接语法:
SELECT table1.column,table2.column FROM table1,table2 WHERE table1.column = table2.column(+);
Oracle Syntax:INNER JOIN(Records 106)
SQL> SELECT e.employee_id,e.last_name,d.department_name 2 FROM employees e,departments d 3 WHERE e.department_id=d.department_id; EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 200 Whalen Administration 201 Hartstein Marketing 202 Fay Marketing 114 Raphaely Purchasing 119 Colmenares Purchasing 115 Khoo Purchasing 116 Baida Purchasing 117 Tobias Purchasing 118 Himuro Purchasing 203 Mavris Human Resources 198 OConnell Shipping EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 199 Grant Shipping 120 Weiss Shipping 121 Fripp Shipping 122 Kaufling Shipping 123 Vollman Shipping 124 Mourgos Shipping 125 Nayer Shipping 126 Mikkilineni Shipping 127 Landry Shipping 128 Markle Shipping 129 Bissot Shipping EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 130 Atkinson Shipping 131 Marlow Shipping 132 Olson Shipping 133 Mallin Shipping 134 Rogers Shipping 135 Gee Shipping 136 Philtanker Shipping 137 Ladwig Shipping 138 Stiles Shipping 139 Seo Shipping 140 Patel Shipping EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 141 Rajs Shipping 142 Davies Shipping 143 Matos Shipping 144 Vargas Shipping 180 Taylor Shipping 181 Fleaur Shipping 182 Sullivan Shipping 183 Geoni Shipping 184 Sarchand Shipping 185 Bull Shipping 186 Dellinger Shipping EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 187 Cabrio Shipping 188 Chung Shipping 189 Dilly Shipping 190 Gates Shipping 191 Perkins Shipping 192 Bell Shipping 193 Everett Shipping 194 McCain Shipping 195 Jones Shipping 196 Walsh Shipping 197 Feeney Shipping EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 104 Ernst IT 103 Hunold IT 107 Lorentz IT 106 Pataballa IT 105 Austin IT 204 Baer Public Relations 176 Taylor Sales 177 Livingston Sales 179 Johnson Sales 175 Hutton Sales 174 Abel Sales EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 173 Kumar Sales 172 Bates Sales 171 Smith Sales 170 Fox Sales 169 Bloom Sales 168 Ozer Sales 145 Russell Sales 146 Partners Sales 147 Errazuriz Sales 148 Cambrault Sales 149 Zlotkey Sales EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 150 Tucker Sales 151 Bernstein Sales 152 Hall Sales 153 Olsen Sales 154 Cambrault Sales 155 Tuvault Sales 156 King Sales 157 Sully Sales 158 McEwen Sales 159 Smith Sales 160 Doran Sales EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 161 Sewall Sales 162 Vishney Sales 163 Greene Sales 164 Marvins Sales 165 Lee Sales 166 Ande Sales 167 Banda Sales 101 Kochhar Executive 100 King Executive 102 De Haan Executive 110 Chen Finance EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 108 Greenberg Finance 111 Sciarra Finance 112 Urman Finance 113 Popp Finance 109 Faviet Finance 206 Gietz Accounting 205 Higgins Accounting 106 rows selected.
Oracle Syntax:Left outer join(Records 107)
SQL> SELECT e.employee_id,e.last_name,d.department_name 2 FROM employees e,departments d 3 WHERE e.department_id=d.department_id(+); EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 200 Whalen Administration 202 Fay Marketing 201 Hartstein Marketing 119 Colmenares Purchasing 118 Himuro Purchasing 117 Tobias Purchasing 116 Baida Purchasing 115 Khoo Purchasing 114 Raphaely Purchasing 203 Mavris Human Resources 197 Feeney Shipping EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 196 Walsh Shipping 195 Jones Shipping 194 McCain Shipping 193 Everett Shipping 192 Bell Shipping 191 Perkins Shipping 190 Gates Shipping 189 Dilly Shipping 188 Chung Shipping 187 Cabrio Shipping 186 Dellinger Shipping EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 185 Bull Shipping 184 Sarchand Shipping 183 Geoni Shipping 182 Sullivan Shipping 181 Fleaur Shipping 180 Taylor Shipping 144 Vargas Shipping 143 Matos Shipping 142 Davies Shipping 141 Rajs Shipping 140 Patel Shipping EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 139 Seo Shipping 138 Stiles Shipping 137 Ladwig Shipping 136 Philtanker Shipping 135 Gee Shipping 134 Rogers Shipping 133 Mallin Shipping 132 Olson Shipping 131 Marlow Shipping 130 Atkinson Shipping 129 Bissot Shipping EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 128 Markle Shipping 127 Landry Shipping 126 Mikkilineni Shipping 125 Nayer Shipping 124 Mourgos Shipping 123 Vollman Shipping 122 Kaufling Shipping 121 Fripp Shipping 120 Weiss Shipping 199 Grant Shipping 198 OConnell Shipping EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 107 Lorentz IT 106 Pataballa IT 105 Austin IT 104 Ernst IT 103 Hunold IT 204 Baer Public Relations 179 Johnson Sales 177 Livingston Sales 176 Taylor Sales 175 Hutton Sales 174 Abel Sales EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 173 Kumar Sales 172 Bates Sales 171 Smith Sales 170 Fox Sales 169 Bloom Sales 168 Ozer Sales 167 Banda Sales 166 Ande Sales 165 Lee Sales 164 Marvins Sales 163 Greene Sales EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 162 Vishney Sales 161 Sewall Sales 160 Doran Sales 159 Smith Sales 158 McEwen Sales 157 Sully Sales 156 King Sales 155 Tuvault Sales 154 Cambrault Sales 153 Olsen Sales 152 Hall Sales EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 151 Bernstein Sales 150 Tucker Sales 149 Zlotkey Sales 148 Cambrault Sales 147 Errazuriz Sales 146 Partners Sales 145 Russell Sales 102 De Haan Executive 101 Kochhar Executive 100 King Executive 113 Popp Finance EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 112 Urman Finance 111 Sciarra Finance 110 Chen Finance 109 Faviet Finance 108 Greenberg Finance 206 Gietz Accounting 205 Higgins Accounting 178 Grant 107 rows selected.
Oracle Syntax:Righer outer join(Records 122)
SQL> SELECT e.employee_id,e.last_name,d.department_name 2 FROM employees e,departments d 3 WHERE e.department_id(+)=d.department_id; EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 200 Whalen Administration 201 Hartstein Marketing 202 Fay Marketing 114 Raphaely Purchasing 119 Colmenares Purchasing 115 Khoo Purchasing 116 Baida Purchasing 117 Tobias Purchasing 118 Himuro Purchasing 203 Mavris Human Resources 198 OConnell Shipping EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 199 Grant Shipping 120 Weiss Shipping 121 Fripp Shipping 122 Kaufling Shipping 123 Vollman Shipping 124 Mourgos Shipping 125 Nayer Shipping 126 Mikkilineni Shipping 127 Landry Shipping 128 Markle Shipping 129 Bissot Shipping EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 130 Atkinson Shipping 131 Marlow Shipping 132 Olson Shipping 133 Mallin Shipping 134 Rogers Shipping 135 Gee Shipping 136 Philtanker Shipping 137 Ladwig Shipping 138 Stiles Shipping 139 Seo Shipping 140 Patel Shipping EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 141 Rajs Shipping 142 Davies Shipping 143 Matos Shipping 144 Vargas Shipping 180 Taylor Shipping 181 Fleaur Shipping 182 Sullivan Shipping 183 Geoni Shipping 184 Sarchand Shipping 185 Bull Shipping 186 Dellinger Shipping EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 187 Cabrio Shipping 188 Chung Shipping 189 Dilly Shipping 190 Gates Shipping 191 Perkins Shipping 192 Bell Shipping 193 Everett Shipping 194 McCain Shipping 195 Jones Shipping 196 Walsh Shipping 197 Feeney Shipping EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 104 Ernst IT 103 Hunold IT 107 Lorentz IT 106 Pataballa IT 105 Austin IT 204 Baer Public Relations 176 Taylor Sales 177 Livingston Sales 179 Johnson Sales 175 Hutton Sales 174 Abel Sales EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 173 Kumar Sales 172 Bates Sales 171 Smith Sales 170 Fox Sales 169 Bloom Sales 168 Ozer Sales 145 Russell Sales 146 Partners Sales 147 Errazuriz Sales 148 Cambrault Sales 149 Zlotkey Sales EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 150 Tucker Sales 151 Bernstein Sales 152 Hall Sales 153 Olsen Sales 154 Cambrault Sales 155 Tuvault Sales 156 King Sales 157 Sully Sales 158 McEwen Sales 159 Smith Sales 160 Doran Sales EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 161 Sewall Sales 162 Vishney Sales 163 Greene Sales 164 Marvins Sales 165 Lee Sales 166 Ande Sales 167 Banda Sales 101 Kochhar Executive 100 King Executive 102 De Haan Executive 110 Chen Finance EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ 108 Greenberg Finance 111 Sciarra Finance 112 Urman Finance 113 Popp Finance 109 Faviet Finance 206 Gietz Accounting 205 Higgins Accounting Treasury Corporate Tax Control And Credit Shareholder Services EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ Benefits Manufacturing Construction Contracting Operations IT Support NOC IT Helpdesk Government Sales Retail Sales Recruiting EMPLOYEE_ID LAST_NAME DEPARTMENT_NAME ----------- ------------------------- ------------------------------ Payroll 122 rows selected.
Oracle Syntax:Full outer join,如何表示？
Cartesian Products
- A Cartesian product is formed when:
- -A join conditioin is omitted
- -A join condtions is invalid
- -All rows in the first table are joined to all rows in the second table
- To avoid a Cartesian product,always include a valid join condtion.
- 一般在产生大量的测试数据时，会用到.
Generating a Cartesian Product
Creating Cross Joins
The CROSS JOIN clause produces the cross-product of two tables.
This is also called a Cartesian product between the two tables.
View Code
SQL> SELECT last_name,department_name 2 FROM employees CROSS JOIN departments; ...... ...... ......
Oracle Syntax:Cross Joins
SQL> SELECT last_name,department_name FROM employees,departments; ...... ...... ......
Summary
In this lesson,you should have learned how to use joins to display data from multiple tables by using:
- Equijoins
- Nonequijoins
- Outer joins
- Self-joins
- Cross joins
- Natural joins
- Full (or two-sided) outer joins.
