Using Outer Joins in PLSQL Query

https://plsqlquery.blogspot.com/2018/05/using-outer-joins-in-plsql-query.html

We use Outer Join when mismatched Values to be displayed      or
Using Outer Joins to Return Records with No Direct Match

The missing rows can be returned if an outer join operator is used in the join condition. The Operator is a Plus sign enclosed in parenthesis (+) and it is placed on the "side" of the join that is deficient in information. The Operator has the effect of creating one or more null rows, to which one or more rows from the non-deficient table can be joined.

In the syntax:

table1.column = is the condition that joins (or relates) the tables together
table2.column(+) = is the outer join symbol, which can be placed either side of the WHERE clause  condition,but not on both sides.
(Place the outer join symbol following the name of the column in the table without the matching rows.)



select E.EMPNO, E.ENAME, E.SAL, E.COMM, G.DEPTNO, G.DNAME from EMP E, DEPT G
where E.DEPTNO = G.DEPTNO(+)

EMPNO	ENAME	SAL	COMM	DEPTNO	DNAME
7934	MILLER	1300	(null)	10	ACCOUNTING
7782	CLARK	2450	(null)	10	ACCOUNTING
7839	KING	5000	(null)	10	ACCOUNTING
7876	ADAMS	1100	(null)	20	RESEARCH
7369	SMITH	800	(null)	20	RESEARCH
7902	FORD	3000	(null)	20	RESEARCH
7788	SCOTT	3000	(null)	20	RESEARCH
7566	JONES	2975	(null)	20	RESEARCH
7900	JAMES	950	(null)	30	SALES
7844	TURNER	1500	0	30	SALES
7654	MARTIN	1250	1400	30	SALES
7521	WARD	1250	500	30	SALES
7499	ALLEN	1600	300	30	SALES
7698	BLAKE	2850	(null)	30	SALES
1	ASLAM	1200	(null)	(null)	(null)

Placed (+) on the "side" of the join that is deficient (missing) in information. Aslam's record exists in EMP table, but NULL DEPTNO does not exists in DEPT table, so put (+) on DEPT side because DEPT side is missing information

----------------------------------------------------------------------------------------------------------

select E.EMPNO, E.ENAME, E.SAL, E.COMM, G.DEPTNO, G.DNAME from EMP E, DEPT G
where E.DEPTNO(+) = G.DEPTNO

EMPNO	ENAME	SAL	COMM	DEPTNO	DNAME
7782	CLARK	2450	(null)	10	ACCOUNTING
7934	MILLER	1300	(null)	10	ACCOUNTING
7839	KING	5000	(null)	10	ACCOUNTING
7788	SCOTT	3000	(null)	20	RESEARCH
7876	ADAMS	1100	(null)	20	RESEARCH
7566	JONES	2975	(null)	20	RESEARCH
7902	FORD	3000	(null)	20	RESEARCH
7369	SMITH	800	(null)	20	RESEARCH
7900	JAMES	950	(null)	30	SALES
7844	TURNER	1500	0	30	SALES
7654	MARTIN	1250	1400	30	SALES
7521	WARD	1250	500	30	SALES
7499	ALLEN	1600	300	30	SALES
7698	BLAKE	2850	(null)	30	SALES
(null)	(null)	(null)	(null)	40	OPERATIONS

Placed (+) on the "side" of the join that is deficient (missing) in information. Operations department DEPTNO 40 exist in DEPT table, but DEPTNO 40 is not assigned to any employee, so put (+) on EMP side because EMP side is missing information