Right Join in SQL
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.
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
Login/Signup to comment