>> RIGHT OUTER JOIN in SQL

RIGHT OUTER JOIN:

In right outer join , we get all the data from the right side table whether it is fully matching or partially matching with the left side table data.  We get only the matching data from the left side table , we get "null" for the non-matching left side table data .

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 RIGHT OUTER JOIN TABLE2

ON {join condition}

OR

SELECT {DESIRED COLUMN LIST OR ALL COLUMNS }

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

Now, let's do the right outer join with below query:

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 RIGHT 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  RIGHT OUTER  JOIN DEPT_INFO B

ON A.STATE = B.STATE ;

NAME	DEPARTMENT	STATE	LOCATION	EST
DAVID	MARKETING	IL	ROCKFORD	1980
RICHARD	SALES	MD	TOWSON	1988
SMITH	RESOURCES	TX	AUSTIN	1995
RAM	RESOURCES	TX	AUSTIN	1995
ADAM	TECHNICAL	MD	TOWSON	1988
MIKE	EXECUTIVE	IL	ROCKFORD	1980
JAMES	MARKETING	VA	RICHMOND	1990
JULI	EXECUTIVE	VA	RICHMOND	1990
(null)	(null)	(null)	DENVER	2005
(null)	(null)	(null)	LITTLE ROCK	2000

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


.