>> FULL OUTER JOIN or FULL JOIN in SQL

FULL OUTER JOIN or FULL JOIN 

In this join, all the records are returned from both left side and right side tables whether the matching condition are met or not. For the non matching records , we get "null" .

Syntax:

SELECT {DESIRED COLUMN LIST OR ALL COLUMNS }

FROM TABLE1 FULL  OUTER JOIN  TABLE2

ON {join condition}

OR

SELECT {DESIRED COLUMN LIST OR ALL COLUMNS }

FROM TABLE1 OUTER 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

Let's  see the employee information , Name, Position, Salary, Location , State, Establishment year.

SELECT A.NAME, A.POSITION, A.SALARY, A.STATE as LEFT_STATE, B.STATE as RIGHT_STATE , B.LOCATION, B.ESTD
FROM EMP_INFO A FULL  OUTER JOIN DEPT_INFO B
ON A.STATE = B.STATE ;

OR

SELECT A.NAME, A.POSITION, A.SALARY, A.STATE as LEFT_STATE, B.STATE as RIGHT_STATE , B.LOCATION, B.ESTD
FROM EMP_INFO A FULL  JOIN DEPT_INFO B
ON A.STATE = B.STATE ;

Output:

NAME	POSITION	SALARY	LEFT_STATE	RIGHT_STATE	LOCATION	ESTD
DAVID	MANAGER	2000	IL	IL	ROCKFORD	1980
RICHARD	SALESMAN	1000	MD	MD	TOWSON	1988
SMITH	HR	1500	TX	TX	AUSTIN	1995
RAM	ASSISTANT	1200	TX	TX	AUSTIN	1995
GOPI	MARKETER	1200	FL	(null)	(null)	(null)
BRITNI	ASSISTANT	1800	PA	(null)	(null)	(null)
ADAM	LEAD	2200	MD	MD	TOWSON	1988
MIKE	DIRECTOR	2500	IL	IL	ROCKFORD	1980
JAMES	ACCOUNTANT	1200	VA	VA	RICHMOND	1990
JULI	MANAGER	2200	VA	VA	RICHMOND	1990
(null)	(null)	(null)	(null)	CO	DENVER	2005
(null)	(null)	(null)	(null)	AR	LITTLE ROCK	2000

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

NEXT

Go To Main SQL Page

.