Right Join in SQL

May 10, 2021

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
7839KINGPRESIDENT–17-NOV-815000–10
7698BLAKEMANAGER783901-MAY-812850–30
7782CLARKMANAGER783909-JUN-812450–10
7566JONESMANAGER783902-APR-812975–20
7788SCOTTANALYST756619-APR-873000–20
7902FORDANALYST756603-DEC-813000–20
7369SMITHCLERK790217-DEC-80800–20
7499ALLENSALESMAN769820-FEB-81160030030
7521WARDSALESMAN769822-FEB-81125050030
7654MARTINSALESMAN769828-SEP-811250140030
7844TURNERSALESMAN769808-SEP-811500030
7876ADAMSCLERK778823-MAY-871100–20
7900JAMESCLERK769803-DEC-81950–30
7934MILLERCLERK778223-JAN-821300–10

 

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
15––40OPERATIONSBOSTON