>> LEFT OUTER JOIN in SQL

 LEFT OUTER JOIN:

In this join, we get data based on the left outer join condition. The data on left hand side table and right hand side table might be 100%  matching or might be matching partially . In which ever situation(100% matching or partially matching), we get all data from the left side table.  For the non-matching data on the right side table,  we  get NULL on the right hand  side of the result.

When we need all the left side table data and only the matching data from right side table , we can use left outer join.  

Syntax:

SELECT {DESIRED COLUMN LIST OR ALL COLUMNS }

FROM TABLE1, TABLE2, ...

WHERE TABLE1_COLUMN = TABLE2_COLUMN(+)

The above syntax can also be written in a different way as below  :

SELECT {DESIRED COLUMN LIST OR ALL COLUMNS }

FROM TABLE1 LEFT OUTER JOIN TABLE2

ON {join condition}

OR

SELECT {DESIRED COLUMN LIST OR ALL COLUMNS }

FROM TABLE1 LEFT JOIN TABLE2

ON {join condition}

Let's have a table EMP_INFO as below:

NAME	DEPARTMENT	SALARY	POSITION	STATE	HIREDATE
DAVID	MARKETING	2000	MANAGER	IL	12-Dec-00
RICHARD	SALES	1000	SALESMAN	MD	1-Jan-08
SMITH	RESOURCES	1500	HR	TX	5-Jan-06
RAM	RESOURCES	1200	ASSISTANT	TX	5-Jun-06
GOPI	SALES	1200	MARKETER	FL	9-Feb-08
BRITNI	TECHNICAL	1800	ASSISTANT	PA	1-Feb-09
ADAM	TECHNICAL	2200	LEAD	MD	1-Mar-10
MIKE	EXECUTIVE	2500	DIRECTOR	IL	1-Aug-10
JAMES	MARKETING	1200	ACCOUNTANT	VA	20-Aug-14
JULI	EXECUTIVE	2200	MANAGER	VA	21-Aug-12

And DEPT_INFO table as below:

STATE	LOCATION	ESTD
VA	RICHMOND	1990
MD	TOWSON	1988
TX	AUSTIN	1995
IL	ROCKFORD	1980
AR	LITTLE ROCK	2000
CO	DENVER	2005

For Example:

Let's find out the employee name, their Department, the LOCATION, STATE and the respective establishment year.

SELECT A.NAME, A.DEPARTMENT , A.STATE , B.LOCATION , B.ESTD

FROM EMP_INFO A , DEPT_INFO B

WHERE A.STATE = B.STATE(+) ;

OR

SELECT A.NAME, A.DEPARTMENT , A.STATE , B.LOCATION , B.ESTD

FROM EMP_INFO A LEFT JOIN  DEPT_INFO B

ON A.STATE = B.STATE ;

OR

SELECT A.NAME, A.DEPARTMENT , A.STATE , B.LOCATION , B.ESTD

FROM EMP_INFO A  LEFT OUTER  JOIN DEPT_INFO B

ON A.STATE = B.STATE ;

NAME	DEPARTMENT	STATE	LOCATION	EST
JULI	EXECUTIVE	VA	RICHMOND	1990
JAMES	MARKETING	VA	RICHMOND	1990
ADAM	TECHNICAL	MD	TOWSON	1988
RICHARD	SALES	MD	TOWSON	1988
RAM	RESOURCES	TX	AUSTIN	1995
SMITH	RESOURCES	TX	AUSTIN	1995
MIKE	EXECUTIVE	IL	ROCKFORD	1980
DAVID	MARKETING	IL	ROCKFORD	1980
GOPI	SALES	FL	(null)	(null)
BRITNI	TECHNICAL	PA	(null)	(null)

Note: A, B in above queries are aliases. Aliases are temporary names given to table. An alias is used in SQL queries only and exists only  until the duration of the SQL execution. If there is more than one tables involved in the queries or if you want to give more relevant/readable name or to shorten the long name or to give an unique name replacing the table name in the query, you can use aliases.

NEXT

Go To Main SQL Page


.