Right Join in DBMS

February 2, 2023

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
DBMS desktop gif
DBMS mobile adv gif

Functional Dependencies

Joins in DBMS

Relational Algebra

Deadlock 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
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 Prime
DBMS Tutorial banner

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

Get Prime
Course List
Course List

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout
Checkout