SQL LEFT JOIN or LEFT OUTER JOIN

It returns all rows from left table and matched rows from right table. If no matching rows from right table, then it will return NULL values

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

LEFT JOIN DEPARTMENTS

ON E.DEPT_ID =D.DEPT_ID; 

The Output will be like this:

DEPT_ID	DEPT_NAME	EMP_NAME	SALARY
1	French Open	Roger Fed	5000
2	Wimbledon	Roger Fed	5000
3	US Open	Roger Fed	5000
4	Aus Open	Roger Fed	5000
4	Aus Open	Roger Fed	5000
4	Aus Open	Roger Fed	5000
4	Aus Open	Roger Fed	5000
4	Aus Open	Roger Fed	5000
5	Olympics Games	Roger Fed	5000

Learn more : SQL Tutorial , SQL Interview Questions