Right Join in DBMS

Right Join in DBMS

 

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

A join is a query that combines rows from two or more tables, views or materialized views.

Definition: A right join is an outer join where it returns all the records from the right table and matching records from the left table.

Learn more about Join in DBMS here on this page.

Right Join in DBMS

Three points about right outer join

  • Tables are join based on some condition
  • Only matched records from left side table and all records from right side table are displayed
  • The result is Null if there is no match from left side table.

 

Syntax:

select col1,col2,col3....
from <table 1>
RIGHT JOIN <table 2>
on <table 1>.common_column = <table 2>.common_column;

 

Right Join Example

  • Consider two tables emp and dept

Emp table

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
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 rownum,Empno,Ename,Emp.Deptno,Dname, sal*12 annsal,loc,job
from 
Emp
RIGHT JOIN
Dept
on Emp.Deptno=Dept.deptno and dname='SALES';

O/P

ROWNUMEMPNOENAMEDEPTNODNAMEANNUALLOCJOB
17698BLAKE30SALES34200CHICAGOMANAGER
27499ALLEN30SALES19200CHICAGOSALESMAN
37521WARD30SALES15000CHICAGOSALESMAN
47654MARTIN30SALES15000CHICAGOSALESMAN
57844TURNER30SALES18000CHICAGOSALESMAN
67900JAMES30SALES11400CHICAGOCLERK
7OPERATIONSBOSTON
8ACCOUNTINGNEW YORK
9RESEARCHDALLAS

 

There are 6 matched records with deptno=30 and name =’SALES’ from dept table with emp table and 3 records from right side table ‘dept’ are unmatched are displayed, if there is no data to be added on unmatched records null values are added .hence total 9 rows are displayed