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;
......
......
......
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.
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
