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