Right Join in SQL

February 2, 2023

About right join in SQL

In this article, we will learn about right join in SQL.
Right Join in SQL 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 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.

Functional Dependencies

Joins in DBMS

Relational Algebra

Deadlock in DBMS

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

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 ename,sal,d.deptno,dname,loc
from 
emp e 
RIGHT outer join
dept d
on e.deptno=d.deptno;

O/P

ROWNUM	ENAME	SAL	DEPTNO	DNAME	LOC
1	KING	5000	10	ACCOUNTING	NEW YORK
2	BLAKE	2850	30	SALES	CHICAGO
3	CLARK	2450	10	ACCOUNTING	NEW YORK
4	JONES	2975	20	RESEARCH	DALLAS
5	SCOTT	3000	20	RESEARCH	DALLAS
6	FORD	3000	20	RESEARCH	DALLAS
7	SMITH	800	20	RESEARCH	DALLAS
8	ALLEN	1600	30	SALES	CHICAGO
9	WARD	1250	30	SALES	CHICAGO
10	MARTIN	1250	30	SALES	CHICAGO
11	TURNER	1500	30	SALES	CHICAGO
12	ADAMS	1100	20	RESEARCH	DALLAS
13	JAMES	950	30	SALES	CHICAGO
14	MILLER	1300	10	ACCOUNTING	NEW YORK
15	–	–	40	OPERATIONS	BOSTON

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