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.

 

 

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