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 |
Login/Signup to comment