본문 바로가기
Database/Oracle

[Oracle] Oracle Join 종류 정리 #2

kindlove 2025. 11. 5. 16:02

테이블 3개에 대해서 Join결과를 확인해 본다. 아래와 같이 테스트 테이블 3개가 존재한다.

 

Employees 테이블 전체 행 (PK: EMPLOYEE_ID, FK: DEPARTMENT_ID)

 - 88번 row는 department_id가 null

NO EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL DEPARTMENT_ID
1 198 Donald OConnell DOCONNEL 50
2 199 Douglas Grant DGRANT 50
3 200 Jennifer Whalen JWHALEN 10
4 201 Michael Hartstein MHARTSTE 20
5 202 Pat Fay PFAY 20
6 203 Susan Mavris SMAVRIS 40
7 204 Hermann Baer HBAER 70
8 205 Shelley Higgins SHIGGINS 110
9 206 William Gietz WGIETZ 110
10 100 Steven King SKING 90
11 101 Neena Kochhar NKOCHHAR 90
12 102 Lex De Haan LDEHAAN 90
13 103 Alexander Hunold AHUNOLD 60
14 104 Bruce Ernst BERNST 60
15 105 David Austin DAUSTIN 60
16 106 Valli Pataballa VPATABAL 60
17 107 Diana Lorentz DLORENTZ 60
18 108 Nancy Greenberg NGREENBE 100
19 109 Daniel Faviet DFAVIET 100
20 110 John Chen JCHEN 100
21 111 Ismael Sciarra ISCIARRA 100
22 112 Jose Manuel Urman JMURMAN 100
23 113 Luis Popp LPOPP 100
24 114 Den Raphaely DRAPHEAL 30
25 115 Alexander Khoo AKHOO 30
26 116 Shelli Baida SBAIDA 30
27 117 Sigal Tobias STOBIAS 30
28 118 Guy Himuro GHIMURO 30
29 119 Karen Colmenares KCOLMENA 30
30 120 Matthew Weiss MWEISS 50
31 121 Adam Fripp AFRIPP 50
32 122 Payam Kaufling PKAUFLIN 50
33 123 Shanta Vollman SVOLLMAN 50
34 124 Kevin Mourgos KMOURGOS 50
35 125 Julia Nayer JNAYER 50
36 126 Irene Mikkilineni IMIKKILI 50
37 127 James Landry JLANDRY 50
38 128 Steven Markle SMARKLE 50
39 129 Laura Bissot LBISSOT 50
40 130 Mozhe Atkinson MATKINSO 50
41 131 James Marlow JAMRLOW 50
42 132 TJ Olson TJOLSON 50
43 133 Jason Mallin JMALLIN 50
44 134 Michael Rogers MROGERS 50
45 135 Ki Gee KGEE 50
46 136 Hazel Philtanker HPHILTAN 50
47 137 Renske Ladwig RLADWIG 50
48 138 Stephen Stiles SSTILES 50
49 139 John Seo JSEO 50
50 140 Joshua Patel JPATEL 50
51 141 Trenna Rajs TRAJS 50
52 142 Curtis Davies CDAVIES 50
53 143 Randall Matos RMATOS 50
54 144 Peter Vargas PVARGAS 50
55 145 John Russell JRUSSEL 80
56 146 Karen Partners KPARTNER 80
57 147 Alberto Errazuriz AERRAZUR 80
58 148 Gerald Cambrault GCAMBRAU 80
59 149 Eleni Zlotkey EZLOTKEY 80
60 150 Peter Tucker PTUCKER 80
61 151 David Bernstein DBERNSTE 80
62 152 Peter Hall PHALL 80
63 153 Christopher Olsen COLSEN 80
64 154 Nanette Cambrault NCAMBRAU 80
65 155 Oliver Tuvault OTUVAULT 80
66 156 Janette King JKING 80
67 157 Patrick Sully PSULLY 80
68 158 Allan McEwen AMCEWEN 80
69 159 Lindsey Smith LSMITH 80
70 160 Louise Doran LDORAN 80
71 161 Sarath Sewall SSEWALL 80
72 162 Clara Vishney CVISHNEY 80
73 163 Danielle Greene DGREENE 80
74 164 Mattea Marvins MMARVINS 80
75 165 David Lee DLEE 80
76 166 Sundar Ande SANDE 80
77 167 Amit Banda ABANDA 80
78 168 Lisa Ozer LOZER 80
79 169 Harrison Bloom HBLOOM 80
80 170 Tayler Fox TFOX 80
81 171 William Smith WSMITH 80
82 172 Elizabeth Bates EBATES 80
83 173 Sundita Kumar SKUMAR 80
84 174 Ellen Abel EABEL 80
85 175 Alyssa Hutton AHUTTON 80
86 176 Jonathon Taylor JTAYLOR 80
87 177 Jack Livingston JLIVINGS 80
88 178 Kimberely Grant KGRANT  
89 179 Charles Johnson CJOHNSON 80
90 180 Winston Taylor WTAYLOR 50
91 181 Jean Fleaur JFLEAUR 50
92 182 Martha Sullivan MSULLIVA 50
93 183 Girard Geoni GGEONI 50
94 184 Nandita Sarchand NSARCHAN 50
95 185 Alexis Bull ABULL 50
96 186 Julia Dellinger JDELLING 50
97 187 Anthony Cabrio ACABRIO 50
98 188 Kelly Chung KCHUNG 50
99 189 Jennifer Dilly JDILLY 50
100 190 Timothy Gates TGATES 50
101 191 Randall Perkins RPERKINS 50
102 192 Sarah Bell SBELL 50
103 193 Britney Everett BEVERETT 50
104 194 Samuel McCain SMCCAIN 50
105 195 Vance Jones VJONES 50
106 196 Alana Walsh AWALSH 50
107 197 Kevin Feeney KFEENEY 50

 

Departments 테이블 전체 행(PK: DEPARTMENT_ID)

NO DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
1 10 Administration 200 1700
2 20 Marketing 201 1800
3 30 Purchasing 114 1700
4 40 Human Resources 203 2400
5 50 Shipping 121 1500
6 60 IT 103 1400
7 70 Public Relations 204 2700
8 80 Sales 145 2500
9 90 Executive 100 1700
10 100 Finance 108 1700
11 110 Accounting 205 1700
12 120 Treasury   1700
13 130 Corporate Tax   1700
14 140 Control And Credit   1700
15 150 Shareholder Services   1700
16 160 Benefits   1700
17 170 Manufacturing   1700
18 180 Construction   1700
19 190 Contracting   1700
20 200 Operations   1700
21 210 IT Support   1700
22 220 NOC   1700
23 230 IT Helpdesk   1700
24 240 Government Sales   1700
25 250 Retail Sales   1700
26 260 Recruiting   1700
27 270 Payroll   1700

 

LOCATIONS 테이블

NO LOCATION_ID STREET_ADDRESS POSTAL_CODE CITY
1 1000 1297 Via Cola di Rie 00989 Roma
2 1100 93091 Calle della Testa 10934 Venice
3 1200 2017 Shinjuku-ku 1689 Tokyo
4 1300 9450 Kamiya-cho 6823 Hiroshima
5 1400 2014 Jabberwocky Rd 26192 Southlake
6 1500 2011 Interiors Blvd 99236 South San Francisco
7 1600 2007 Zagora St 50090 South Brunswick
8 1700 2004 Charade Rd 98199 Seattle
9 1800 147 Spadina Ave M5V 2L7 Toronto
10 1900 6092 Boxwood St YSW 9T2 Whitehorse
11 2000 40-5-12 Laogianggen 190518 Beijing
12 2100 1298 Vileparle (E) 490231 Bombay
13 2200 12-98 Victoria Street 2901 Sydney
14 2300 198 Clementi North 540198 Singapore
15 2400 8204 Arthur St   London
16 2500 Magdalen Centre, The Oxford Science Park OX9 9ZB Oxford
17 2600 9702 Chester Road 09629850293 Stretford
18 2700 Schwanthalerstr. 7031 80925 Munich
19 2800 Rua Frei Caneca 1360  01307-002 Sao Paulo
20 2900 20 Rue des Corps-Saints 1730 Geneva
21 3000 Murtenstrasse 921 3095 Bern
22 3100 Pieter Breughelstraat 837 3029SK Utrecht
23 3200 Mariano Escobedo 9991 11932 Mexico City

 

이렇게 3개의 테이블에 대해서 아래 SQL은 어떻게 동작할까?

SELECT *
FROM employees E, departments D , locations L
WHERE E.department_id(+) = D.department_id
AND D.location_id = L.location_id;

 

우선은 employee, departmentes를 outer join후 locations 테이블을 inner join하게 된다.

 

결과는 다음과 같다.

NO EMPLOYEE_ID FIRST_NAME LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID STREET_ADDRESS
1 198 Donald OConnell 50 Shipping 1500 2011 Interiors Blvd
2 199 Douglas Grant 50 Shipping 1500 2011 Interiors Blvd
3 200 Jennifer Whalen 10 Administration 1700 2004 Charade Rd
4 201 Michael Hartstein 20 Marketing 1800 147 Spadina Ave
5 202 Pat Fay 20 Marketing 1800 147 Spadina Ave
6 203 Susan Mavris 40 Human Resources 2400 8204 Arthur St
7 204 Hermann Baer 70 Public Relations 2700 Schwanthalerstr. 7031
8 205 Shelley Higgins 110 Accounting 1700 2004 Charade Rd
9 206 William Gietz 110 Accounting 1700 2004 Charade Rd
10 100 Steven King 90 Executive 1700 2004 Charade Rd
11 101 Neena Kochhar 90 Executive 1700 2004 Charade Rd
12 102 Lex De Haan 90 Executive 1700 2004 Charade Rd
13 103 Alexander Hunold 60 IT 1400 2014 Jabberwocky Rd
14 104 Bruce Ernst 60 IT 1400 2014 Jabberwocky Rd
15 105 David Austin 60 IT 1400 2014 Jabberwocky Rd
16 106 Valli Pataballa 60 IT 1400 2014 Jabberwocky Rd
17 107 Diana Lorentz 60 IT 1400 2014 Jabberwocky Rd
18 108 Nancy Greenberg 100 Finance 1700 2004 Charade Rd
19 109 Daniel Faviet 100 Finance 1700 2004 Charade Rd
20 110 John Chen 100 Finance 1700 2004 Charade Rd
21 111 Ismael Sciarra 100 Finance 1700 2004 Charade Rd
22 112 Jose Manuel Urman 100 Finance 1700 2004 Charade Rd
23 113 Luis Popp 100 Finance 1700 2004 Charade Rd
24 114 Den Raphaely 30 Purchasing 1700 2004 Charade Rd
25 115 Alexander Khoo 30 Purchasing 1700 2004 Charade Rd
26 116 Shelli Baida 30 Purchasing 1700 2004 Charade Rd
27 117 Sigal Tobias 30 Purchasing 1700 2004 Charade Rd
28 118 Guy Himuro 30 Purchasing 1700 2004 Charade Rd
29 119 Karen Colmenares 30 Purchasing 1700 2004 Charade Rd
30 120 Matthew Weiss 50 Shipping 1500 2011 Interiors Blvd
31 121 Adam Fripp 50 Shipping 1500 2011 Interiors Blvd
32 122 Payam Kaufling 50 Shipping 1500 2011 Interiors Blvd
33 123 Shanta Vollman 50 Shipping 1500 2011 Interiors Blvd
34 124 Kevin Mourgos 50 Shipping 1500 2011 Interiors Blvd
35 125 Julia Nayer 50 Shipping 1500 2011 Interiors Blvd
36 126 Irene Mikkilineni 50 Shipping 1500 2011 Interiors Blvd
37 127 James Landry 50 Shipping 1500 2011 Interiors Blvd
38 128 Steven Markle 50 Shipping 1500 2011 Interiors Blvd
39 129 Laura Bissot 50 Shipping 1500 2011 Interiors Blvd
40 130 Mozhe Atkinson 50 Shipping 1500 2011 Interiors Blvd
41 131 James Marlow 50 Shipping 1500 2011 Interiors Blvd
42 132 TJ Olson 50 Shipping 1500 2011 Interiors Blvd
43 133 Jason Mallin 50 Shipping 1500 2011 Interiors Blvd
44 134 Michael Rogers 50 Shipping 1500 2011 Interiors Blvd
45 135 Ki Gee 50 Shipping 1500 2011 Interiors Blvd
46 136 Hazel Philtanker 50 Shipping 1500 2011 Interiors Blvd
47 137 Renske Ladwig 50 Shipping 1500 2011 Interiors Blvd
48 138 Stephen Stiles 50 Shipping 1500 2011 Interiors Blvd
49 139 John Seo 50 Shipping 1500 2011 Interiors Blvd
50 140 Joshua Patel 50 Shipping 1500 2011 Interiors Blvd
51 141 Trenna Rajs 50 Shipping 1500 2011 Interiors Blvd
52 142 Curtis Davies 50 Shipping 1500 2011 Interiors Blvd
53 143 Randall Matos 50 Shipping 1500 2011 Interiors Blvd
54 144 Peter Vargas 50 Shipping 1500 2011 Interiors Blvd
55 145 John Russell 80 Sales 2500 Magdalen Centre, The Oxford Science Park
56 146 Karen Partners 80 Sales 2500 Magdalen Centre, The Oxford Science Park
57 147 Alberto Errazuriz 80 Sales 2500 Magdalen Centre, The Oxford Science Park
58 148 Gerald Cambrault 80 Sales 2500 Magdalen Centre, The Oxford Science Park
59 149 Eleni Zlotkey 80 Sales 2500 Magdalen Centre, The Oxford Science Park
60 150 Peter Tucker 80 Sales 2500 Magdalen Centre, The Oxford Science Park
61 151 David Bernstein 80 Sales 2500 Magdalen Centre, The Oxford Science Park
62 152 Peter Hall 80 Sales 2500 Magdalen Centre, The Oxford Science Park
63 153 Christopher Olsen 80 Sales 2500 Magdalen Centre, The Oxford Science Park
64 154 Nanette Cambrault 80 Sales 2500 Magdalen Centre, The Oxford Science Park
65 155 Oliver Tuvault 80 Sales 2500 Magdalen Centre, The Oxford Science Park
66 156 Janette King 80 Sales 2500 Magdalen Centre, The Oxford Science Park
67 157 Patrick Sully 80 Sales 2500 Magdalen Centre, The Oxford Science Park
68 158 Allan McEwen 80 Sales 2500 Magdalen Centre, The Oxford Science Park
69 159 Lindsey Smith 80 Sales 2500 Magdalen Centre, The Oxford Science Park
70 160 Louise Doran 80 Sales 2500 Magdalen Centre, The Oxford Science Park
71 161 Sarath Sewall 80 Sales 2500 Magdalen Centre, The Oxford Science Park
72 162 Clara Vishney 80 Sales 2500 Magdalen Centre, The Oxford Science Park
73 163 Danielle Greene 80 Sales 2500 Magdalen Centre, The Oxford Science Park
74 164 Mattea Marvins 80 Sales 2500 Magdalen Centre, The Oxford Science Park
75 165 David Lee 80 Sales 2500 Magdalen Centre, The Oxford Science Park
76 166 Sundar Ande 80 Sales 2500 Magdalen Centre, The Oxford Science Park
77 167 Amit Banda 80 Sales 2500 Magdalen Centre, The Oxford Science Park
78 168 Lisa Ozer 80 Sales 2500 Magdalen Centre, The Oxford Science Park
79 169 Harrison Bloom 80 Sales 2500 Magdalen Centre, The Oxford Science Park
80 170 Tayler Fox 80 Sales 2500 Magdalen Centre, The Oxford Science Park
81 171 William Smith 80 Sales 2500 Magdalen Centre, The Oxford Science Park
82 172 Elizabeth Bates 80 Sales 2500 Magdalen Centre, The Oxford Science Park
83 173 Sundita Kumar 80 Sales 2500 Magdalen Centre, The Oxford Science Park
84 174 Ellen Abel 80 Sales 2500 Magdalen Centre, The Oxford Science Park
85 175 Alyssa Hutton 80 Sales 2500 Magdalen Centre, The Oxford Science Park
86 176 Jonathon Taylor 80 Sales 2500 Magdalen Centre, The Oxford Science Park
87 177 Jack Livingston 80 Sales 2500 Magdalen Centre, The Oxford Science Park
88 179 Charles Johnson 80 Sales 2500 Magdalen Centre, The Oxford Science Park
89 180 Winston Taylor 50 Shipping 1500 2011 Interiors Blvd
90 181 Jean Fleaur 50 Shipping 1500 2011 Interiors Blvd
91 182 Martha Sullivan 50 Shipping 1500 2011 Interiors Blvd
92 183 Girard Geoni 50 Shipping 1500 2011 Interiors Blvd
93 184 Nandita Sarchand 50 Shipping 1500 2011 Interiors Blvd
94 185 Alexis Bull 50 Shipping 1500 2011 Interiors Blvd
95 186 Julia Dellinger 50 Shipping 1500 2011 Interiors Blvd
96 187 Anthony Cabrio 50 Shipping 1500 2011 Interiors Blvd
97 188 Kelly Chung 50 Shipping 1500 2011 Interiors Blvd
98 189 Jennifer Dilly 50 Shipping 1500 2011 Interiors Blvd
99 190 Timothy Gates 50 Shipping 1500 2011 Interiors Blvd
100 191 Randall Perkins 50 Shipping 1500 2011 Interiors Blvd
101 192 Sarah Bell 50 Shipping 1500 2011 Interiors Blvd
102 193 Britney Everett 50 Shipping 1500 2011 Interiors Blvd
103 194 Samuel McCain 50 Shipping 1500 2011 Interiors Blvd
104 195 Vance Jones 50 Shipping 1500 2011 Interiors Blvd
105 196 Alana Walsh 50 Shipping 1500 2011 Interiors Blvd
106 197 Kevin Feeney 50 Shipping 1500 2011 Interiors Blvd
107         IT Support 1700 2004 Charade Rd
108         Payroll 1700 2004 Charade Rd
109         Operations 1700 2004 Charade Rd
110         Construction 1700 2004 Charade Rd
111         Retail Sales 1700 2004 Charade Rd
112         Government Sales 1700 2004 Charade Rd
113         Contracting 1700 2004 Charade Rd
114         Recruiting 1700 2004 Charade Rd
115         Control And Credit 1700 2004 Charade Rd
116         NOC 1700 2004 Charade Rd
117         Treasury 1700 2004 Charade Rd
118         Manufacturing 1700 2004 Charade Rd
119         Corporate Tax 1700 2004 Charade Rd
120         IT Helpdesk 1700 2004 Charade Rd
121         Shareholder Services 1700 2004 Charade Rd
122         Benefits 1700 2004 Charade Rd

 

위 결과는 아래 Location 테이블에 대해서 outer join을 한 결과와 같다.현재는 department의 모든 location 이 맵핑을 가지고 있으므로 위 Inner Join과 아래 Outer Join결과가 같아지는 것이다.

SELECT *
FROM employees E, departments D , locations L
WHERE E.department_id(+) = D.department_id
AND D.location_id = L.location_id(+);

 

그럼 만일 아래 Locations의 Location_id의 조인 조건을 departments 테이블 방향으로 Outer Join 을 하면 어떻게 될까?

SELECT *
FROM employees E, departments D , locations L
WHERE E.department_id(+) = D.department_id
AND D.location_id(+) = L.location_id;

 

결과

NO EMPLOYEE_ID FIRST_NAME LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID STREET_ADDRESS
1 198 Donald OConnell 50 Shipping 1500 2011 Interiors Blvd
2 199 Douglas Grant 50 Shipping 1500 2011 Interiors Blvd
3 200 Jennifer Whalen 10 Administration 1700 2004 Charade Rd
4 201 Michael Hartstein 20 Marketing 1800 147 Spadina Ave
5 202 Pat Fay 20 Marketing 1800 147 Spadina Ave
6 203 Susan Mavris 40 Human Resources 2400 8204 Arthur St
7 204 Hermann Baer 70 Public Relations 2700 Schwanthalerstr. 7031
8 205 Shelley Higgins 110 Accounting 1700 2004 Charade Rd
9 206 William Gietz 110 Accounting 1700 2004 Charade Rd
10 100 Steven King 90 Executive 1700 2004 Charade Rd
11 101 Neena Kochhar 90 Executive 1700 2004 Charade Rd
12 102 Lex De Haan 90 Executive 1700 2004 Charade Rd
13 103 Alexander Hunold 60 IT 1400 2014 Jabberwocky Rd
14 104 Bruce Ernst 60 IT 1400 2014 Jabberwocky Rd
15 105 David Austin 60 IT 1400 2014 Jabberwocky Rd
16 106 Valli Pataballa 60 IT 1400 2014 Jabberwocky Rd
17 107 Diana Lorentz 60 IT 1400 2014 Jabberwocky Rd
18 108 Nancy Greenberg 100 Finance 1700 2004 Charade Rd
19 109 Daniel Faviet 100 Finance 1700 2004 Charade Rd
20 110 John Chen 100 Finance 1700 2004 Charade Rd
21 111 Ismael Sciarra 100 Finance 1700 2004 Charade Rd
22 112 Jose Manuel Urman 100 Finance 1700 2004 Charade Rd
23 113 Luis Popp 100 Finance 1700 2004 Charade Rd
24 114 Den Raphaely 30 Purchasing 1700 2004 Charade Rd
25 115 Alexander Khoo 30 Purchasing 1700 2004 Charade Rd
26 116 Shelli Baida 30 Purchasing 1700 2004 Charade Rd
27 117 Sigal Tobias 30 Purchasing 1700 2004 Charade Rd
28 118 Guy Himuro 30 Purchasing 1700 2004 Charade Rd
29 119 Karen Colmenares 30 Purchasing 1700 2004 Charade Rd
30 120 Matthew Weiss 50 Shipping 1500 2011 Interiors Blvd
31 121 Adam Fripp 50 Shipping 1500 2011 Interiors Blvd
32 122 Payam Kaufling 50 Shipping 1500 2011 Interiors Blvd
33 123 Shanta Vollman 50 Shipping 1500 2011 Interiors Blvd
34 124 Kevin Mourgos 50 Shipping 1500 2011 Interiors Blvd
35 125 Julia Nayer 50 Shipping 1500 2011 Interiors Blvd
36 126 Irene Mikkilineni 50 Shipping 1500 2011 Interiors Blvd
37 127 James Landry 50 Shipping 1500 2011 Interiors Blvd
38 128 Steven Markle 50 Shipping 1500 2011 Interiors Blvd
39 129 Laura Bissot 50 Shipping 1500 2011 Interiors Blvd
40 130 Mozhe Atkinson 50 Shipping 1500 2011 Interiors Blvd
41 131 James Marlow 50 Shipping 1500 2011 Interiors Blvd
42 132 TJ Olson 50 Shipping 1500 2011 Interiors Blvd
43 133 Jason Mallin 50 Shipping 1500 2011 Interiors Blvd
44 134 Michael Rogers 50 Shipping 1500 2011 Interiors Blvd
45 135 Ki Gee 50 Shipping 1500 2011 Interiors Blvd
46 136 Hazel Philtanker 50 Shipping 1500 2011 Interiors Blvd
47 137 Renske Ladwig 50 Shipping 1500 2011 Interiors Blvd
48 138 Stephen Stiles 50 Shipping 1500 2011 Interiors Blvd
49 139 John Seo 50 Shipping 1500 2011 Interiors Blvd
50 140 Joshua Patel 50 Shipping 1500 2011 Interiors Blvd
51 141 Trenna Rajs 50 Shipping 1500 2011 Interiors Blvd
52 142 Curtis Davies 50 Shipping 1500 2011 Interiors Blvd
53 143 Randall Matos 50 Shipping 1500 2011 Interiors Blvd
54 144 Peter Vargas 50 Shipping 1500 2011 Interiors Blvd
55 145 John Russell 80 Sales 2500 Magdalen Centre, The Oxford Science Park
56 146 Karen Partners 80 Sales 2500 Magdalen Centre, The Oxford Science Park
57 147 Alberto Errazuriz 80 Sales 2500 Magdalen Centre, The Oxford Science Park
58 148 Gerald Cambrault 80 Sales 2500 Magdalen Centre, The Oxford Science Park
59 149 Eleni Zlotkey 80 Sales 2500 Magdalen Centre, The Oxford Science Park
60 150 Peter Tucker 80 Sales 2500 Magdalen Centre, The Oxford Science Park
61 151 David Bernstein 80 Sales 2500 Magdalen Centre, The Oxford Science Park
62 152 Peter Hall 80 Sales 2500 Magdalen Centre, The Oxford Science Park
63 153 Christopher Olsen 80 Sales 2500 Magdalen Centre, The Oxford Science Park
64 154 Nanette Cambrault 80 Sales 2500 Magdalen Centre, The Oxford Science Park
65 155 Oliver Tuvault 80 Sales 2500 Magdalen Centre, The Oxford Science Park
66 156 Janette King 80 Sales 2500 Magdalen Centre, The Oxford Science Park
67 157 Patrick Sully 80 Sales 2500 Magdalen Centre, The Oxford Science Park
68 158 Allan McEwen 80 Sales 2500 Magdalen Centre, The Oxford Science Park
69 159 Lindsey Smith 80 Sales 2500 Magdalen Centre, The Oxford Science Park
70 160 Louise Doran 80 Sales 2500 Magdalen Centre, The Oxford Science Park
71 161 Sarath Sewall 80 Sales 2500 Magdalen Centre, The Oxford Science Park
72 162 Clara Vishney 80 Sales 2500 Magdalen Centre, The Oxford Science Park
73 163 Danielle Greene 80 Sales 2500 Magdalen Centre, The Oxford Science Park
74 164 Mattea Marvins 80 Sales 2500 Magdalen Centre, The Oxford Science Park
75 165 David Lee 80 Sales 2500 Magdalen Centre, The Oxford Science Park
76 166 Sundar Ande 80 Sales 2500 Magdalen Centre, The Oxford Science Park
77 167 Amit Banda 80 Sales 2500 Magdalen Centre, The Oxford Science Park
78 168 Lisa Ozer 80 Sales 2500 Magdalen Centre, The Oxford Science Park
79 169 Harrison Bloom 80 Sales 2500 Magdalen Centre, The Oxford Science Park
80 170 Tayler Fox 80 Sales 2500 Magdalen Centre, The Oxford Science Park
81 171 William Smith 80 Sales 2500 Magdalen Centre, The Oxford Science Park
82 172 Elizabeth Bates 80 Sales 2500 Magdalen Centre, The Oxford Science Park
83 173 Sundita Kumar 80 Sales 2500 Magdalen Centre, The Oxford Science Park
84 174 Ellen Abel 80 Sales 2500 Magdalen Centre, The Oxford Science Park
85 175 Alyssa Hutton 80 Sales 2500 Magdalen Centre, The Oxford Science Park
86 176 Jonathon Taylor 80 Sales 2500 Magdalen Centre, The Oxford Science Park
87 177 Jack Livingston 80 Sales 2500 Magdalen Centre, The Oxford Science Park
88 179 Charles Johnson 80 Sales 2500 Magdalen Centre, The Oxford Science Park
89 180 Winston Taylor 50 Shipping 1500 2011 Interiors Blvd
90 181 Jean Fleaur 50 Shipping 1500 2011 Interiors Blvd
91 182 Martha Sullivan 50 Shipping 1500 2011 Interiors Blvd
92 183 Girard Geoni 50 Shipping 1500 2011 Interiors Blvd
93 184 Nandita Sarchand 50 Shipping 1500 2011 Interiors Blvd
94 185 Alexis Bull 50 Shipping 1500 2011 Interiors Blvd
95 186 Julia Dellinger 50 Shipping 1500 2011 Interiors Blvd
96 187 Anthony Cabrio 50 Shipping 1500 2011 Interiors Blvd
97 188 Kelly Chung 50 Shipping 1500 2011 Interiors Blvd
98 189 Jennifer Dilly 50 Shipping 1500 2011 Interiors Blvd
99 190 Timothy Gates 50 Shipping 1500 2011 Interiors Blvd
100 191 Randall Perkins 50 Shipping 1500 2011 Interiors Blvd
101 192 Sarah Bell 50 Shipping 1500 2011 Interiors Blvd
102 193 Britney Everett 50 Shipping 1500 2011 Interiors Blvd
103 194 Samuel McCain 50 Shipping 1500 2011 Interiors Blvd
104 195 Vance Jones 50 Shipping 1500 2011 Interiors Blvd
105 196 Alana Walsh 50 Shipping 1500 2011 Interiors Blvd
106 197 Kevin Feeney 50 Shipping 1500 2011 Interiors Blvd
107         IT Support 1700 2004 Charade Rd
108         Payroll 1700 2004 Charade Rd
109         Operations 1700 2004 Charade Rd
110         Construction 1700 2004 Charade Rd
111         Retail Sales 1700 2004 Charade Rd
112         Government Sales 1700 2004 Charade Rd
113         Contracting 1700 2004 Charade Rd
114         Recruiting 1700 2004 Charade Rd
115         Control And Credit 1700 2004 Charade Rd
116         NOC 1700 2004 Charade Rd
117             1297 Via Cola di Rie
118             93091 Calle della Testa
119             2017 Shinjuku-ku
120             9450 Kamiya-cho
121             2007 Zagora St
122             6092 Boxwood St
123             40-5-12 Laogianggen
124             1298 Vileparle (E)
125             12-98 Victoria Street
126             198 Clementi North
127             9702 Chester Road
128             Rua Frei Caneca 1360 
129             20 Rue des Corps-Saints
130             Murtenstrasse 921
131             Pieter Breughelstraat 837
132             Mariano Escobedo 9991
133         Treasury 1700 2004 Charade Rd
134         Manufacturing 1700 2004 Charade Rd
135         Corporate Tax 1700 2004 Charade Rd
136         IT Helpdesk 1700 2004 Charade Rd
137         Shareholder Services 1700 2004 Charade Rd
138         Benefits 1700 2004 Charade Rd

 

위의 결과 (122) 대비 결과 138로 더 많은 row가 출력 되었다. join할때 기본적으로 앞의 테이블 연산을 하고 뒤에 연산을 이어서 하기 때문에 department, employee outer 연산 이후에 Locations 기준으로 outer join을 수행해서 null인 데이터가 더 붙게 되었다.

 

3개 테이블을 Join할때 Join조건을 명시하지 않은 테이블은 기본적으로 앞의 결과에 Cartesian 곱을 하게 된다.

아래 쿼리를 실행하면 2806개의 결과가 되는데 employee와 departments outer join 결과 122에 locations 총 23개 122 * 23 = 2806 개의 Cartesian 곱의 결과가 나타나게 된다.

SELECT *
FROM employees E, departments D , locations L
WHERE E.department_id(+) = D.department_id;

 

-- The End --

'Database > Oracle' 카테고리의 다른 글

[Oracle] Partition by로 그룹별 순서기반으로 집계하기  (0) 2025.11.20
[Oracle] SQL Performance - View 사용 주의  (0) 2025.11.06
[Oracle] and system_cd(+) = 'COM' 과 같이 상수값 비교 조건에도 (+)가 붙는 이유  (0) 2025.11.05
[Oracle] Oracle Join 종류 정리  (0) 2025.11.05
[DB] Oracle Docker 이미지로 CDB, PDB 기반 설치하기  (0) 2025.11.03

'Database/Oracle' Related Articles

티스토리툴바