IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
Hi,
I will explain SQL Join and Join Types in Oracle SQL in this post of Oracle SQL Tutorial series.
Read the previous post of this tutorial series before this.
EXISTS and NOT EXISTS Operator in Oracle SQL | Oracle SQL Tutorials -18
The JOIN Clause
The JOIN is very popular clause in Oracle SQL that is used to combine or join two or more tables according to a related column between tables.
You should use the Table ALIAS when join the multiple tables. If ALIAS is not used, wrong results can be produced.
You should define the Join column before use it.
You should use the only related column in Select and avoid the select * from clause.
JOIN TYPES
There are four types of the JOINs in Oracle SQL as follows.
- (INNER) JOIN: it lists the records when matching values exist in both tables
- LEFT (OUTER) JOIN: it lists the records from the left table and the matched records from the right table.
- RIGHT (OUTER) JOIN: it lists the records from the right table and the matched records from the left table.
- FULL (OUTER) JOIN: it lists the records when there is a match in either first or other table.
When you decided to Join multiple tables, you should define the Join Column firstly.
For Example; I want to join hr.departments and hr.locations table, lets check it if there is a common column or not as follows.
SQL> desc hr.departments Name Null? Type ----------------------------------------- -------- ---------------------------- DEPARTMENT_ID NOT NULL NUMBER(4) DEPARTMENT_NAME NOT NULL VARCHAR2(30) MANAGER_ID NUMBER(6) LOCATION_ID NUMBER(4) SQL> SQL> desc hr.locations Name Null? Type ----------------------------------------- -------- ---------------------------- LOCATION_ID NOT NULL NUMBER(4) STREET_ADDRESS VARCHAR2(40) POSTAL_CODE VARCHAR2(12) CITY NOT NULL VARCHAR2(30) STATE_PROVINCE VARCHAR2(25) COUNTRY_ID CHAR(2) SQL>
There is a common column named location_id between two tables, so we can join these tables with this column as follows.
You can find the city name of department when you join the hr.departments and hr.locations tables with location_id column.
SQL> SELECT department_id, department_name, city FROM hr.departments d, hr.locations l where d.location_id=l.location_id;
DEPARTMENT_ID DEPARTMENT_NAME CITY
------------- ------------------------------ ------------------------------
60 IT Southlake
50 Shipping South San Francisco
10 Administration Seattle
30 Purchasing Seattle
90 Executive Seattle
100 Finance Seattle
110 Accounting Seattle
120 Treasury Seattle
130 Corporate Tax Seattle
140 Control And Credit Seattle
150 Shareholder Services Seattle
DEPARTMENT_ID DEPARTMENT_NAME CITY
------------- ------------------------------ ------------------------------
160 Benefits Seattle
170 Manufacturing Seattle
180 Construction Seattle
190 Contracting Seattle
200 Operations Seattle
210 IT Support Seattle
220 NOC Seattle
230 IT Helpdesk Seattle
240 Government Sales Seattle
250 Retail Sales Seattle
260 Recruiting Seattle
DEPARTMENT_ID DEPARTMENT_NAME CITY
------------- ------------------------------ ------------------------------
270 Payroll Seattle
11 Computer Engineering Seattle
20 Marketing Toronto
40 Human Resources London
80 Sales Oxford
70 Public Relations Munich
28 rows selected.
SQL>
You can find the first_name, last name and city name of employees and department name when you join the hr.departments and hr.locations tables with location_id column.
SQL> SELECT employee_id, first_name,last_name,city, department_name
2 FROM hr.employees e
3 JOIN hr.departments d
4 ON d.department_id = e.department_id
5 JOIN hr.locations l
6 ON d.location_id = l.location_id where l.location_id>2400;
EMPLOYEE_ID FIRST_NAME LAST_NAME CITY DEPARTMENT_NAME
----------- -------------------- ------------------------- ------------------------------ ------------------------------
145 John Russell Oxford Sales
146 Karen Partners Oxford Sales
147 Alberto Errazuriz Oxford Sales
148 Gerald Cambrault Oxford Sales
149 Eleni Zlotkey Oxford Sales
150 Peter Tucker Oxford Sales
151 David Bernstein Oxford Sales
152 Peter Hall Oxford Sales
153 Christopher Olsen Oxford Sales
154 Nanette Cambrault Oxford Sales
155 Oliver Tuvault Oxford Sales
EMPLOYEE_ID FIRST_NAME LAST_NAME CITY DEPARTMENT_NAME
----------- -------------------- ------------------------- ------------------------------ ------------------------------
156 Janette King Oxford Sales
157 Patrick Sully Oxford Sales
158 Allan McEwen Oxford Sales
159 Lindsey Smith Oxford Sales
160 Louise Doran Oxford Sales
161 Sarath Sewall Oxford Sales
162 Clara Vishney Oxford Sales
163 Danielle Greene Oxford Sales
164 Mattea Marvins Oxford Sales
165 David Lee Oxford Sales
166 Sundar Ande Oxford Sales
EMPLOYEE_ID FIRST_NAME LAST_NAME CITY DEPARTMENT_NAME
----------- -------------------- ------------------------- ------------------------------ ------------------------------
167 Amit Banda Oxford Sales
168 Lisa Ozer Oxford Sales
169 Harrison Bloom Oxford Sales
170 Tayler Fox Oxford Sales
171 William Smith Oxford Sales
172 Elizabeth Bates Oxford Sales
173 Sundita Kumar Oxford Sales
174 Ellen Abel Oxford Sales
175 Alyssa Hutton Oxford Sales
176 Jonathon Taylor Oxford Sales
177 Jack Livingston Oxford Sales
EMPLOYEE_ID FIRST_NAME LAST_NAME CITY DEPARTMENT_NAME
----------- -------------------- ------------------------- ------------------------------ ------------------------------
179 Charles Johnson Oxford Sales
204 Hermann Baer Munich Public Relations
35 rows selected.
SQL>
Do you want to learn Oracle SQL Tutorial for Beginners, then read the following articles.
Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course
