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_IDEMP_NAMEHIRE_DATESALARYDEPT_ID
1Roger Fed2001-05-0150004
2Djoker Nova2002-07-1565001
3Stef Graph2005-10-1880005
4Mart Hingis2007-01-0372003
5Andre Duplesi2008-06-245600null

DEPARTMENT Table has

DEPT_IDDEPT_NAME
1French Open
2Wimbledon
3US Open
4Aus Open
5Olympics 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