SQL Inner Join in Oracle SQL | Oracle SQL Tutorials -20

Hi,

I will explain SQL Inner Join in Oracle SQL in this post of Oracle SQL Tutorial series.

Read the previous post of this tutorial series before this.

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

 

 

 

The (INNER) Join

Inner Join or only Join keyword is used to list the records when matching values exist in both tables

If you do not specify the join type with words like inner , right , left in the query , inner join is the default.

 

(INNER) JOIN Syntax

Inner join syntax is as follows. You can use the (INNER) Join with 3 types of syntax as follows.

Using INNER is not necessary, you can skip this clause.

If you want to use INNER Join, you can skip both INNER and Join clause as follows.

 

SELECT column1,column2,column(n)...
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
SELECT column1,column2,column(n)...
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

SELECT column1,column2,column(n)...
FROM table1, table2
where table1.column_name = table2.column_name;
You can review the following example. 3 SQL Statements have the same result as follows.
SQL> select first_name,last_name,DEPARTMENT_NAME from hr.employees e inner join hr.departments d on e.department_id=d.department_id where e.salary>13000;

FIRST_NAME           LAST_NAME                 DEPARTMENT_NAME
-------------------- ------------------------- ------------------------------
Neena                Kochhar                   Executive
Lex                  De Haan                   Executive
Nancy                Greenberg                 Finance
John                 Russell                   Sales
Karen                Partners                  Sales
Michael              Hartstein                 Marketing
Shelley              Higgins                   Accounting

7 rows selected.

SQL>  select first_name,last_name,DEPARTMENT_NAME from hr.employees e join hr.departments d on e.department_id=d.department_id where e.salary>13000;

FIRST_NAME           LAST_NAME                 DEPARTMENT_NAME
-------------------- ------------------------- ------------------------------
Neena                Kochhar                   Executive
Lex                  De Haan                   Executive
Nancy                Greenberg                 Finance
John                 Russell                   Sales
Karen                Partners                  Sales
Michael              Hartstein                 Marketing
Shelley              Higgins                   Accounting

7 rows selected.

SQL> select first_name,last_name,DEPARTMENT_NAME from hr.employees e,hr.departments d where e.department_id=d.department_id and e.salary>13000;

FIRST_NAME           LAST_NAME                 DEPARTMENT_NAME
-------------------- ------------------------- ------------------------------
Neena                Kochhar                   Executive
Lex                  De Haan                   Executive
Nancy                Greenberg                 Finance
John                 Russell                   Sales
Karen                Partners                  Sales
Michael              Hartstein                 Marketing
Shelley              Higgins                   Accounting

7 rows selected.

SQL>

 

Mostly developers don’t use the Join or Inner Join keyword, they prefer the third usage as follows.

SQL> select first_name,last_name,DEPARTMENT_NAME from hr.employees e,hr.departments d where e.department_id=d.department_id and e.salary>13000;

FIRST_NAME           LAST_NAME                 DEPARTMENT_NAME
-------------------- ------------------------- ------------------------------
Neena                Kochhar                   Executive
Lex                  De Haan                   Executive
Nancy                Greenberg                 Finance
John                 Russell                   Sales
Karen                Partners                  Sales
Michael              Hartstein                 Marketing
Shelley              Higgins                   Accounting

7 rows selected.

SQL>

 

 

You can use multiple join in a SQL Statement as follows.

SQL> select first_name,last_name,DEPARTMENT_NAME,JOB_TITLE from hr.employees e inner join hr.departments d on e.department_id=d.department_id join hr.jobs j on e.job_id=j.job_id where e.salary>13000;
FIRST_NAME           LAST_NAME                 DEPARTMENT_NAME                JOB_TITLE
-------------------- ------------------------- ------------------------------ -----------------------------------
Neena                Kochhar                   Executive                      Administration Vice President
Lex                  De Haan                   Executive                      Administration Vice President
Nancy                Greenberg                 Finance                        Finance Manager
John                 Russell                   Sales                          Sales Manager
Karen                Partners                  Sales                          Sales Manager
Michael              Hartstein                 Marketing                      Marketing Manager
Shelley              Higgins                   Accounting                     Accounting Manager

7 rows selected.

SQL> 



You can run the above INNER Join without INNER Join Clause as follows.

SQL> select first_name,last_name,DEPARTMENT_NAME,JOB_TITLE from hr.employees e,hr.departments d,hr.jobs j where e.department_id=d.department_id and e.job_id=j.job_id and e.salary>13000;
FIRST_NAME           LAST_NAME                 DEPARTMENT_NAME                JOB_TITLE
-------------------- ------------------------- ------------------------------ -----------------------------------
Neena                Kochhar                   Executive                      Administration Vice President
Lex                  De Haan                   Executive                      Administration Vice President
Nancy                Greenberg                 Finance                        Finance Manager
John                 Russell                   Sales                          Sales Manager
Karen                Partners                  Sales                          Sales Manager
Michael              Hartstein                 Marketing                      Marketing Manager
Shelley              Higgins                   Accounting                     Accounting Manager

7 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

 588 views last month,  2 views today

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

Do NOT follow this link or you will be banned from the site!