Right Join in SQL

Right join in SQL

 

In this article, we will learn about Right Join in SQL.

Join enables data merging from tables i.e used to query data from multiple tables

How right join works

  • Right Join always Returns all rows from the right table
    Matching records from the left side table
  • Null values in the place of non-matching rows in another table.

 

Learn more about Joins in SQL here on this page.

Right Join in SQL

Syntax

select col1,col2.... 
from 
tab1 
RIGHT JOIN 
tab2 
on tab1.colname=tab2.colname

 

Right Join Example

Consider two tables emp and dept table :

Emp table

EMPNOENAMEJOBMGRHIREDATE  SAL 
COMMDEPTNO
7839KINGPRESIDENT17-NOV-81500010
7698BLAKEMANAGER783901-MAY-81285030
7782CLARKMANAGER783909-JUN-81245010
7566JONESMANAGER783902-APR-81297520
7788SCOTTANALYST756619-APR-87300020
7902FORDANALYST756603-DEC-81300020
7369SMITHCLERK790217-DEC-8080020
7499ALLENSALESMAN769820-FEB-81160030030
7521WARDSALESMAN769822-FEB-81125050030
7654MARTINSALESMAN769828-SEP-811250140030
7844TURNERSALESMAN769808-SEP-811500030
7876ADAMSCLERK778823-MAY-87110020
7900JAMESCLERK769803-DEC-8195030
7934MILLERCLERK778223-JAN-82130010

 

Dept table

DEPTNODNAMELOC
10ACCOUNTINGNEW YORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON

 

Right join SQL query

 

select ename,sal,d.deptno,dname,loc
from 
emp e 
RIGHT outer join
dept d
on e.deptno=d.deptno;

O/P

ROWNUMENAMESALDEPTNODNAMELOC
1KING500010ACCOUNTINGNEW YORK
2BLAKE285030SALESCHICAGO
3CLARK245010ACCOUNTINGNEW YORK
4JONES297520RESEARCHDALLAS
5SCOTT300020RESEARCHDALLAS
6FORD300020RESEARCHDALLAS
7SMITH80020RESEARCHDALLAS
8ALLEN160030SALESCHICAGO
9WARD125030SALESCHICAGO
10MARTIN125030SALESCHICAGO
11TURNER150030SALESCHICAGO
12ADAMS110020RESEARCHDALLAS
13JAMES95030SALESCHICAGO
14MILLER130010ACCOUNTINGNEW YORK
1540OPERATIONSBOSTON