SQL FULL JOIN or FULL OUTER JOIN

It will return all rows when there is a match in either of the table.

For example EMPLOYEE Table has

EMP_ID	EMP_NAME	HIRE_DATE	SALARY	DEPT_ID
1	Roger Fed	2001-05-01	5000	4
2	Djoker Nova	2002-07-15	6500	1
3	Stef Graph	2005-10-18	8000	5
4	Mart Hingis	2007-01-03	7200	3
5	Andre Duplesi	2008-06-24	5600	null

DEPARTMENT Table has

DEPT_ID	DEPT_NAME
1	French Open
2	Wimbledon
3	US Open
4	Aus Open
5	Olympics Games

Now lets write LEFT JOIN query:

SELECT D.DEPT_ID, D.DEPT_NAME, E.EMP_NAME, E.SALARY  

FROM  EMPLOYEE E, DEPARTMENT D

FULL JOIN DEPARTMENTS

ON E.DEPT_ID =D.DEPT_ID; 

If FULL JOIN does not work in some Databases then you can try the below UNION ALL query

SELECT  D.DEPT_ID, D.DEPT_NAME, E.EMP_NAME, E.SALARY  

   FROM EMPLOYEE E, DEPARTMENT D

   LEFT JOIN DEPARTMENT

   ON E.DEPT_ID =D.DEPT_ID; 

UNION ALL

   SELECT  D.DEPT_ID, D.DEPT_NAME, E.EMP_NAME, E.SALARY  

   FROM EMPLOYEE E, DEPARTMENT D

   RIGHT JOIN DEPARTMENT

   ON E.DEPT_ID =D.DEPT_ID; 

Learn more : SQL Tutorial , SQL Interview Questions