Press "Enter" to skip to content

Oracle Joins With Examples

Oracle joins with examples are given below to describe Oracle Joins using new syntax. Which is very easy to understand and very good in SQL query readability. Because if query is larger and having too many tables with too  many joins in old syntax it becomes complex to understand. So it is better to use these new SQL join syntax for better understanding.

To demonstrate first I would like to show below the table structure with data used in the examples.

Table 1: EMP

Oracle SQL joins new syntax with examples

Table 2: DEPT

Dept Table

Table 3: BONUS

Oracle joins with examples

As you can see above the 3 tables EMP, DEPT and BONUS and for the examples I have changed some data in every table to show the desired results to give Oracle joins with examples, like EMP table having DeptNo 40 which not exists in DEPT table and DEPT table having DeptNo 2 Dname IT which not exists in EMP table and in BONUS table there are only 4 employee records.

INNER JOIN Example

Inner Join is the simple join which returns all the rows from all the tables used in query by matching all the criteria. Example is below:
SELECT e.empno,
e.ename,
e.job,
e.deptno,
d.dname,
e.sal,
e.comm  FROM emp e INNER JOIN dept d ON e.deptno = d.deptno;
Output from the above query:

Oracle SQL Inner Join Example

You can see in above image that all the rows from EMP table and DEPT returned which are matching in both tables but record having DeptNo 40 Ename ALLEN is missing because it does not exists in DEPT table.

LEFT OUTER JOIN Example

Left Outer Join returns all the rows from the Left Side table and the only rows from the right side table who matches used in LEFT OUTER JOIN clause. In the below example it will return all the rows from EMP table even the record having DeptNo 40 which is not exists in DEPT table.
SELECT e.empno,
e.ename,
e.job,
e.deptno,
d.dname,
e.sal,
e.comm
FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;

Oracle SQL Left Outer Join Example new syntax

RIGHT OUTER JOIN Example

Right Outer Join return all the rows from the right side table and rows that matches from left side table. In the below example it will return all the rows from DEPT table including IT Dname which is not exists in EMP table but it will not return the EMP table record which is having DeptNo 40 because it does not exists in DEPT table.
SELECT e.empno,
e.ename,
e.job,
e.deptno,
d.dname,
e.sal,
e.comm
FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;

Oracle SQL Right Outer Join Example new syntax

FULL OUTER JOIN Example

Full Outer Join return all the rows from left side table and right side table even the rows does not matches from both the tables. In the below example it will return all the rows from EMP table including DeptNo 40 record and all the rows from DEPT table including IT Department record.
SELECT e.empno,
e.ename,
e.job,
e.deptno,
d.dname,
e.sal,
e.comm
FROM emp e FULL OUTER JOIN dept d ON e.deptno = d.deptno;

Oracle SQL Full Join Example new syntax

Now in the below examples BONUS table also included to demonstrate some multiple table examples with multiple joins.

INNER JOIN WITH MULTIPLE TABLES Example

In the below example inner joins are used with all three tables and will return only the records which are matching in all the tables.
SELECT e.empno,
e.ename,
e.job,
e.deptno,
d.dname,
e.sal,
e.comm,
b.amount bonus
FROM emp e
INNER JOIN dept d
ON e.deptno = d.deptno
INNER JOIN bonus b
ON e.empno = b.empno;

Oracle SQL Joins Multiple

INNER JOIN WITH LEFT OUTER JOIN WITH MULTIPLE TABLE Example

Below example uses inner join and with EMP and DEPT table and left outer join with BONUS table, means it will return all the rows from EMP and DEPT tables which are matching but only rows from BONUS table which are matching with EMP table.
SELECT e.empno,
e.ename,
e.job,
e.deptno,
d.dname,
e.sal,
e.comm,
b.amount bonus
FROM emp e
INNER JOIN dept d
ON e.deptno = d.deptno
LEFT OUTER JOIN bonus b
ON e.empno = b.empno;

Oracle SQL INNER with LEFT OUTER join example

FULL OUTER JOIN WITH MULTIPLE TABLES Example

Below example uses full outer join for all three tables and will return all the records from all the tables.
SELECT e.empno,
e.ename,
e.job,
e.deptno,
d.dname,
e.sal,
e.comm,
b.amount bonus
FROM emp e
FULL OUTER JOIN dept d
ON e.deptno = d.deptno
FULL OUTER JOIN bonus b
ON e.empno = b.empno;

Oracle SQL Full Outer Join with multiple table example

Finally you can use WHERE clause at the end to specify more criteria as shown below:
SELECT e.empno,
e.ename,
e.job,
e.deptno,
d.dname,
e.sal,
e.comm,
b.amount bonus
FROM emp e
INNER JOIN dept d
ON e.deptno = d.deptno
LEFT OUTER JOIN bonus b
ON e.empno = b.empno
WHERE d.deptno = 30;

Oracle SQL New Joins with Where clause example