SQL Join and Join Types in Oracle SQL | Oracle SQL Tutorials -19

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

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *