Inner join is the most common type of Join which is used to combine the rows from two tables and create a result set containing only such records that are present in both the tables based on the joining condition (predicate).
Inner join returns rows when there is at least one match in both tables
If none of the record matches between two tables, then INNER JOIN will return a NULL set. Below is an example of INNER JOIN and the resulting set.
SELECT dept.name DEPARTMENT, emp.name EMPLOYEE FROM DEPT dept, EMPLOYEE emp WHERE emp.dept_id = dept.id
| Department | Employee |
|---|---|
| HR | Inno |
| HR | Privy |
| Engineering | Robo |
| Engineering | Hash |
| Engineering | Anno |
| Engineering | Darl |
| Marketing | Pete |
| Marketing | Meme |
| Sales | Tomiti |
| Sales | Bhuti |
Outer Join, on the other hand, will return matching rows from both tables as well as any unmatched rows from one or both the tables (based on whether it is single outer or full outer join respectively).
Outer Join can be full outer or single outer
Notice in our record set that there is no employee in the department 5 (Logistics). Because of this if we perform inner join, then Department 5 does not appear in the above result. However in the below query we perform an outer join (dept left outer join emp), and we can see this department.
SELECT dept.name DEPARTMENT, emp.name EMPLOYEE FROM DEPT dept, EMPLOYEE emp WHERE dept.id = emp.dept_id (+)
| Department | Employee |
|---|---|
| HR | Inno |
| HR | Privy |
| Engineering | Robo |
| Engineering | Hash |
| Engineering | Anno |
| Engineering | Darl |
| Marketing | Pete |
| Marketing | Meme |
| Sales | Tomiti |
| Sales | Bhuti |
| Logistics |
The (+) sign on the emp side of the predicate indicates that emp is the outer table here. The above SQL can be alternatively written as below (will yield the same result as above):
SELECT dept.name DEPARTMENT, emp.name EMPLOYEE FROM DEPT dept LEFT OUTER JOIN EMPLOYEE emp ON dept.id = emp.dept_id