Right Join in DBMS

About right join in DBMS

In this article, we will learn about right join in DBMS.
Right Join in DBMS is used to retrieve data from tables that is common top both the tables along with the complete data of the second table i.e. the right table.

 

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.

Right join in DBMS-1

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
RIGHT JOIN on.common_column =.common_column;

Right Join Example

  • Consider two tables emp and dept
Emp table
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
Dept table
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

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

ROWNUM EMPNO ENAME DEPTNO DNAME ANNUAL LOC JOB
1 7698 BLAKE 30 SALES 34200 CHICAGO MANAGER
2 7499 ALLEN 30 SALES 19200 CHICAGO SALESMAN
3 7521 WARD 30 SALES 15000 CHICAGO SALESMAN
4 7654 MARTIN 30 SALES 15000 CHICAGO SALESMAN
5 7844 TURNER 30 SALES 18000 CHICAGO SALESMAN
6 7900 JAMES 30 SALES 11400 CHICAGO CLERK
7 OPERATIONS BOSTON
8 ACCOUNTING NEW YORK
9 RESEARCH DALLAS
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

Prime Course Trailer

Related Banners

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription

Get over 200+ course One Subscription

Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription