IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
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;
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
