Joins are used to retrieve data from multiple tables.
Consider two tables emp and dept.
EMP
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
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
7566
JONES
MANAGER
7839
02-APR-81
2975
20
7654
MARTIN
SALESMAN
7698
28-SEP-81
1250
1,400
30
7698
BLAKE
MANAGER
7839
01-MAY-81
2850
30
7782
CLARK
MANAGER
7839
09-JUN-81
2450
10
7788
SCOTT
ANALYST
7566
09-DEC-82
3000
20
7839
KING
PRESIDENT
17-NOV-81
5000
10
7844
TURNER
SALESMAN
7698
08-SEP-81
1500
0
30
7876
ADAMS
CLERK
7788
12-JAN-83
1100
20
7900
JAMES
CLERK
7698
03-DEC-81
950
30
7902
FORD
ANALYST
7566
03-DEC-81
3000
20
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
I want the following output:
EMPNO
ENAME
DNAME
LOC
7369
SMITH
RESEARCH
DALLAS
7499
ALLEN
SALES
CHICAGO
7521
WARD
SALES
CHICAGO
7566
JONES
RESEARCH
DALLAS
7654
MARTIN
SALES
CHICAGO
7698
BLAKE
SALES
CHICAGO
7782
CLARK
ACCOUNTING
NEW YORK
7788
SCOTT
RESEARCH
DALLAS
7839
KING
ACCOUNTING
NEW YORK
7844
TURNER
SALES
CHICAGO
7876
ADAMS
RESEARCH
DALLAS
7900
JAMES
SALES
CHICAGO
7902
FORD
RESEARCH
DALLAS
7934
MILLER
ACCOUNTING
NEW YORK
Look at the above output,
Empno, ename are from emp table,
Dname ,loc are from dept table.
So, we need to get the data from two table, which can be done by using joins.
Types of Joins:
1) Equi Join / Inner Join
2) Non Equi Join
3) Self Join
4) Outer Join ---- Right Outer Join
Left Outer Join
Full Outer Join
Equi Joins:
-------------
Consider emp and dept tables, there is a common column i.e. deptno.
When tables are joined basing on common column , it is called Equi join.
In Equi Joins , we always use = ( equality operator ) in join condition.
Ex:
Select empno,ename , dname, loc
from emp, dept
where emp.deptno = dept.deptno;
In the above query emp.deptno = dept.deptno is called as join condition.
We mention join condition in where clause.
Ex2:
I want the following output.
EMPNO
ENAME
JOB
SAL
DNAME
LOC
7369
SMITH
CLERK
800
RESEARCH
DALLAS
7499
ALLEN
SALESMAN
1600
SALES
CHICAGO
7521
WARD
SALESMAN
1250
SALES
CHICAGO
7566
JONES
MANAGER
2975
RESEARCH
DALLAS
7654
MARTIN
SALESMAN
1250
SALES
CHICAGO
7698
BLAKE
MANAGER
2850
SALES
CHICAGO
7782
CLARK
MANAGER
2450
ACCOUNTING
NEW YORK
7788
SCOTT
ANALYST
3000
RESEARCH
DALLAS
7839
KING
PRESIDENT
5000
ACCOUNTING
NEW YORK
7844
TURNER
SALESMAN
1500
SALES
CHICAGO
7876
ADAMS
CLERK
1100
RESEARCH
DALLAS
7900
JAMES
CLERK
950
SALES
CHICAGO
7902
FORD
ANALYST
3000
RESEARCH
DALLAS
7934
MILLER
CLERK
1300
ACCOUNTING
NEW YORK
Try writing query by yourself.
In select clause , list out all the columns you want.
In from clause, mention the table from which you want to pull the data.
In where clause, write the join condition.
You query should look like this,
Select empno, ename , job, sal, dname , loc
From emp, dept
Where emp.deptno = dept.deptno;
Ex 3:
I want the following output.
EMPNO
ENAME
JOB
SAL
DEPTNO
DNAME
LOC
7369
SMITH
CLERK
800
20
RESEARCH
DALLAS
7499
ALLEN
SALESMAN
1600
30
SALES
CHICAGO
7521
WARD
SALESMAN
1250
30
SALES
CHICAGO
7566
JONES
MANAGER
2975
20
RESEARCH
DALLAS
7654
MARTIN
SALESMAN
1250
30
SALES
CHICAGO
7698
BLAKE
MANAGER
2850
30
SALES
CHICAGO
7782
CLARK
MANAGER
2450
10
ACCOUNTING
NEW YORK
7788
SCOTT
ANALYST
3000
20
RESEARCH
DALLAS
7839
KING
PRESIDENT
5000
10
ACCOUNTING
NEW YORK
7844
TURNER
SALESMAN
1500
30
SALES
CHICAGO
7876
ADAMS
CLERK
1100
20
RESEARCH
DALLAS
7900
JAMES
CLERK
950
30
SALES
CHICAGO
7902
FORD
ANALYST
3000
20
RESEARCH
DALLAS
7934
MILLER
CLERK
1300
10
ACCOUNTING
NEW YORK
It you look at the above requirement, it is almost similar to previous query
Try writing query by yourself.
In select clause , list out all the columns you want.
In from clause, mention the table from which you want to pull the data.
In where clause, write the join condition.
Select empno, ename , job, sal, deptno, dname , loc
From emp , dept
Where emp.deptno = deptno.deptno; -- error
But, the above query will give an error.
The problem with the column deptno.
As deptno column is present in both emp and dept tables.
There will be ambiguity, from which table it needs to pull the data.
There is no ambiguity problem for columns like empno, ename, job, dname ,loc
As it is either available from emp or from dept table.
How can we resolve the ambiguity?
We can resolve the ambiguity by mentioning the <table_name> . <col_name> in the select clause.
Ex:
Select empno, ename , job, sal, emp.deptno, dname , loc
From emp , dept
Where emp.deptno = deptno.deptno;
The common column ie deptno can also be retrieved from dept table also.
Ex:
Select empno, ename , job, sal, emp.deptno, dname , loc
From emp , dept
Where emp.deptno = deptno.deptno;
The above two queries will give the same result.
Remember:
--------------------
We need to mention <table_name>. < col_name> in select clause to resolve the ambiguity.
Can we mention <table_name> . < col_name> for all the columns in the select clause.
Why not, definitely Yes
You query will look like this:
Select emp.empno, emp.ename, emp.job, emp.sal, emp.deptno, dept.dname , dept.loc
From emp, dept
Where emp.deptno = dept.deptno;
So, compare the following two queries
Query A
Select empno, ename , job, sal, emp.deptno, dname , loc
From emp , dept
Where emp.deptno = deptno.deptno;
Query B
Select emp.empno, emp.ename, emp.job, emp.sal, emp.deptno, dept.dname , dept.loc
From emp, dept
Where emp.deptno = dept.deptno;
Both Query A and Query B will give the same result.
Developer tend to prefer writing Query A rather than Query B, as the length of the query is small.
But from performance point of view
Query B will run faster than Query A
So, according to coding standards, we should mention < table_name> . < col_name> for all the columns which helps in performance.
But when we mention < table_name> . <col_name> for all the columns , the length of the query will be long.
To overcome the length problem, we use the table alias.
Table alias helps in reducing the length of the query and at the same time, performance is maintained.
Table alias are created in from clause, can be used in select and where clause.
Ex:
---
Select e.empno, e.ename , e.job, e.sal, e.deptno, d.dname , d.loc
From emp e, dept d
Where e.deptno = d.deptno;
e is table alias for emp table.
d is table alias for dept table.
Not only two tables , we can join three for n table .
Consider the following tables
EMP Table
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
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
7566
JONES
MANAGER
7839
02-APR-81
2975
20
7654
MARTIN
SALESMAN
7698
28-SEP-81
1250
1,400
30
7698
BLAKE
MANAGER
7839
01-MAY-81
2850
30
7782
CLARK
MANAGER
7839
09-JUN-81
2450
10
7788
SCOTT
ANALYST
7566
09-DEC-82
3000
20
7839
KING
PRESIDENT
17-NOV-81
5000
10
7844
TURNER
SALESMAN
7698
08-SEP-81
1500
0
30
7876
ADAMS
CLERK
7788
12-JAN-83
1100
20
7900
JAMES
CLERK
7698
03-DEC-81
950
30
7902
FORD
ANALYST
7566
03-DEC-81
3000
20
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
Areas table
CITY
STATE
NEW YORK
NEW YORK
DALLAS
TEXAS
CHICAGO
ILLINOIS
BOSTON
MASSACHUSETTS
I want the following output,
EMPNO
ENAME
DEPTNO
DNAME
LOC
STATE
7369
SMITH
20
RESEARCH
DALLAS
TEXAS
7499
ALLEN
30
SALES
CHICAGO
ILLINOIS
7521
WARD
30
SALES
CHICAGO
ILLINOIS
7566
JONES
20
RESEARCH
DALLAS
TEXAS
7654
MARTIN
30
SALES
CHICAGO
ILLINOIS
7698
BLAKE
30
SALES
CHICAGO
ILLINOIS
7782
CLARK
10
ACCOUNTING
NEW YORK
NEW YORK
7788
SCOTT
20
RESEARCH
DALLAS
TEXAS
7839
KING
10
ACCOUNTING
NEW YORK
NEW YORK
7844
TURNER
30
SALES
CHICAGO
ILLINOIS
7876
ADAMS
20
RESEARCH
DALLAS
TEXAS
7900
JAMES
30
SALES
CHICAGO
ILLINOIS
7902
FORD
20
RESEARCH
DALLAS
TEXAS
7934
MILLER
10
ACCOUNTING
NEW YORK
NEW YORK
Total no of columns in the output : 6
Empno, Ename, deptno -- from Emp table
Dname , Loc -- from Dept table
State -- from Areas table
We know, we can join emp and dept tables by using the common column deptno.
We can join dept and Areas table by using the common column Loc and city.
Remember:
For joining table, it is not the column name which should match.
The column values should match.
The Query to get the above output.
Select e.empno, e.ename, e.deptno, d,dname, d.loc, a.state
From emp e, dept d, areas a
Where e.deptno = d.deptno and d.loc = a.city.
Note:
To join 2 tables, we need 1 condition
To join 3 tables, we need 2 conditions
To join n tables, we need n-1 conditions.
Note:
Equi joins always use = ( Equality operator) in join condition.
Non Equi-Join
-------------------------
When tables are joined without using = ( equality operator ) , it is called Non Equi-join.
Consider following tables
Emp Table
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
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
7566
JONES
MANAGER
7839
02-APR-81
2975
20
7654
MARTIN
SALESMAN
7698
28-SEP-81
1250
1,400
30
7698
BLAKE
MANAGER
7839
01-MAY-81
2850
30
7782
CLARK
MANAGER
7839
09-JUN-81
2450
10
7788
SCOTT
ANALYST
7566
09-DEC-82
3000
20
7839
KING
PRESIDENT
17-NOV-81
5000
10
7844
TURNER
SALESMAN
7698
08-SEP-81
1500
0
30
7876
ADAMS
CLERK
7788
12-JAN-83
1100
20
7900
JAMES
CLERK
7698
03-DEC-81
950
30
7902
FORD
ANALYST
7566
03-DEC-81
3000
20
7934
MILLER
CLERK
7782
23-JAN-82
1300
10
Salgrade Table
GRADE
LOSAL
HISAL
1
700
1200
2
1201
1400
3
1401
2000
4
2001
3000
5
3000
9999
All the employees are categorized into grades basing on sal.
What is the grade of SMITH?
SMITH sal is 800. His sal is falling between 700 and 1200, he is grade 1 employee.
What is the grade of CLARK?
CLARK sal is 2450. His sal is falling between 2001 and 3000, so he is grade 4 employee.
I want the following output.
EMPNO
ENAME
SAL
GRADE
7369
SMITH
800
1
7499
ALLEN
1600
3
7521
WARD
1250
2
7566
JONES
2975
4
7654
MARTIN
1250
2
7698
BLAKE
2850
4
7782
CLARK
2450
4
7788
SCOTT
3000
4
7839
KING
5000
5
7844
TURNER
1500
3
7876
ADAMS
1100
1
7900
JAMES
950
1
7902
FORD
3000
4
7934
MILLER
1300
2
Empno, ename and sal we need to get it from EMP table.
Grade we need to get it from SALGRADE table.
To retrieve the data from multiple tables , we need joins.
So, we need to join EMP and SALGRADE table.
Observe, there is no common column between EMP and SALGRADE.
Hence we cannot use = ( equality ) operator.
When sal is falls between LOSAL and HISAL, we can get the GRADE,
We need to use between operator.
Ex:
----------
Select e.empno, e.ename , e.sal, s.grade
From emp e, salgrade s
Where e.sal between s.losal and s.hisal;
Note:
When = ( equality operator ) is not used, it is called NON EQUI-JOIN
3) Self join:
When a table is joined to itself, it is called Self join.
I want the following output
EMPNO
ENAME
SAL
MGR
MGR NAME
7369
SMITH
800
7902
FORD
7499
ALLEN
1600
7698
BLAKE
7521
WARD
1250
7698
BLAKE
7566
JONES
2975
7839
KING
7654
MARTIN
1250
7698
BLAKE
7698
BLAKE
2850
7839
KING
7782
CLARK
2450
7839
KING
7788
SCOTT
3000
7566
JONES
7844
TURNER
1500
7698
BLAKE
7876
ADAMS
1100
7788
SCOTT
7900
JAMES
950
7698
BLAKE
7902
FORD
3000
7566
JONES
7934
MILLER
1300
7782
CLARK
Empno, ename , sal,mgr we can straight forward get from EMP table.
Mgr name, are also available in EMP table.
We can get Mgr name by comparing mgr with empno column.
We need to join EMP table with EMP table.
When a table is joined to itself, it is called SELF JOIN.
Ex:
Select e.empno, e.ename , e.sal, e.mgr, m.ename
From emp e, emp m
where e.mgr = m.empno;
Note:
In self joins, we create two table aliases for the same table.
Outer Joins:
Outer joins are extensions of equi-join.
In equi-joins, we get only the matching data.
In outer joins, we get matching and non matching data.
Look at the equi-join query
Select e.empno, e.ename , e.job, e.sal, e.deptno, d.dname , d.loc
From emp e, dept d
Where e.deptno = d.deptno;
Output of the above query:
EMPNO
ENAME
JOB
SAL
DEPTNO
DNAME
LOC
7369
SMITH
CLERK
800
20
RESEARCH
DALLAS
7499
ALLEN
SALESMAN
1600
30
SALES
CHICAGO
7521
WARD
SALESMAN
1250
30
SALES
CHICAGO
7566
JONES
MANAGER
2975
20
RESEARCH
DALLAS
7654
MARTIN
SALESMAN
1250
30
SALES
CHICAGO
7698
BLAKE
MANAGER
2850
30
SALES
CHICAGO
7782
CLARK
MANAGER
2450
10
ACCOUNTING
NEW YORK
7788
SCOTT
ANALYST
3000
20
RESEARCH
DALLAS
7839
KING
PRESIDENT
5000
10
ACCOUNTING
NEW YORK
7844
TURNER
SALESMAN
1500
30
SALES
CHICAGO
7876
ADAMS
CLERK
1100
20
RESEARCH
DALLAS
7900
JAMES
CLERK
950
30
SALES
CHICAGO
7902
FORD
ANALYST
3000
20
RESEARCH
DALLAS
7934
MILLER
CLERK
1300
10
ACCOUNTING
NEW YORK
In the output, we are not getting dname OPERATIONS and loc BOSTON.
As deptno 40 is not present in the emp table.
Compare EMP and DEPT tables with respect to distinct values of the common column deptno.
EMP table
DEPT table
deptno no column
deptno column
10
10
20
20
30
30
40
As there is no value 40 in emp table, we are not getting OPERATIONS and BOSTON in the output of equi join.
Outer Joins will give 14 + 1 = 15 rows in the output.
(+) is called OUTER JOIN OPERATOR.
As we have deficiency of data in EMP table, we use the outer join operator towards the deficiency side.
Ex of OUTER JOINS
Select e.empno, e.ename , e.job, e.sal, e.deptno, d.dname , d.loc
From emp e, dept d
Where e.deptno (+) = d.deptno;
Compare equi-join and outer-join queries, the only difference is
the OUTER JOIN OPERATOR i.e. (+)