











Joins in SQL
- Joins are used to retrieve data from multiple tables
- In all database, if you are joining n tables then we are using n -1 join condition
SQL has the following types of join
Equijoin or inner join
Based on equality Condition for retrieving data from multiple tables
Here condition columns must belong to the same data type then only you are allowed to perform an inner join operation
Equijoin Example Query
select rownum dname,sal
from emp,dept
where emp.deptno=dept.deptno AND dname ='SALES';




Consider two sample tables
Emp table
Dept table
O/P(Equi Join)
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 |
DEPTNO | DNAME | LOC |
10 | ACCOUNTING | NEW YORK |
20 | RESEARCH | DALLAS |
30 | SALES | CHICAGO |
40 | OPERATIONS | BOSTON |
ROWNUM | DNAME | SAL |
---|---|---|
1 | SALES | 2850 |
2 | SALES | 1600 |
3 | SALES | 1250 |
4 | SALES | 1250 |
5 | SALES | 1500 |
6 | SALES | 950 |
Nonequi joins
Same like inner join, Based on a condition other than equality condition(<,<=,>,>=,<>,between,–)select ename,loc from emp join dept on emp.deptno<dept.deptno where loc='CHICAGO';
O/P
ENAME | LOC |
---|---|
KING | CHICAGO |
CLARK | CHICAGO |
JONES | CHICAGO |
SCOTT | CHICAGO |
FORD | CHICAGO |
SMITH | CHICAGO |
ADAMS | CHICAGO |
MILLER | CHICAGO |
Self-join
- Self-join is nothing but joining a table with itself
- This type of join is used when we want to compare two different column values within the same table then we must use self join but here the condition column must belong to Same data type
- Before we are using self join we must create table alias names.
- This alias name must be the different name which is also known as reference names
- alias Names internally behaves like exact another table
self join SQL Query
select rownum,e1.ename "employees",e2.ename "manager" from emp e1,emp e2 where e1.mgr=e2.empno;O/P
ROWNUM | employees | manager |
---|---|---|
1 | BLAKE | KING |
2 | CLARK | KING |
3 | JONES | KING |
4 | ALLEN | BLAKE |
5 | WARD | BLAKE |
6 | MARTIN | BLAKE |
7 | TURNER | BLAKE |
8 | JAMES | BLAKE |
9 | MILLER | CLARK |
10 | SCOTT | JONES |
11 | FORD | JONES |
12 | ADAMS | SCOTT |
13 | SMITH | FORD |
Outer join
- This join is used to retrieve all rows from one table and matching rows from another table
- If you want to retrieve nonmatching rows then we are using join operator (+) within the join condition (left side or right side) this is called Oracle 8i outer join
Left outer join
The join operator(+) is added at the left side table of the join condition so that all records from the left side and matching records from the right table are displayedLeft Join SQL query
select rownum,ename,sal,d.deptno,dname,loc from emp e,dept d where e.deptno(+)=d.deptnoO/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 |
Right outer join
Join operator(+) is appended at the right side table of the join condition so that all records from the right side table and matched records from right side table are displayedRight join SQL query
select rownum,ename,sal,d.deptno,dname,loc from emp e,dept d where e.deptno=d.deptno(+);O/P
ROWNUM | ENAME | SAL | DEPTNO | DNAME | LOC |
---|---|---|---|---|---|
1 | KING | 5000 | 10 | ACCOUNTING | NEW YORK |
2 | CLARK | 2450 | 10 | ACCOUNTING | NEW YORK |
3 | MILLER | 1300 | 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 | ADAMS | 1100 | 20 | RESEARCH | DALLAS |
9 | BLAKE | 2850 | 30 | SALES | CHICAGO |
10 | ALLEN | 1600 | 30 | SALES | CHICAGO |
11 | WARD | 1250 | 30 | SALES | CHICAGO |
12 | MARTIN | 1250 | 30 | SALES | CHICAGO |
13 | TURNER | 1500 | 30 | SALES | CHICAGO |
14 | JAMES | 950 | 30 | SALES | CHICAGO |
Full outer join
- Prior to Oracle 9i if you want to retrieve both matching and unmatched rows as well, can we perform Union operation between left and right joins
- so that matched rows from two tables with the join condition are satisfied, and unmatched rows from the left side table, as well as a right side table, also displayed
Full Join SQL query
select rownum,ename,sal,d.deptno,dname,loc from emp e,dept d where e.deptno(+)=d.deptno ; union select rownum,ename,sal,d.deptno,dname,loc from emp e,dept d where 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