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
Table 2: DEPT
Table 3: BONUS
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:
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;
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;
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;
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;
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;
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;
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;
