NON- EQUI JOIN :-
- Joining two tables not based on common column it’s called Non-equi join.
Syntax :- Select <collist> from <tablelist> Where <join cond> [and<join cond> and<cond>]
Here join condition is based on other than equal operator,so it is Called non-equi join
- Emp salary grade
- Empno ename sal Grade lowsal highsal
- 1 A 300 1 100 200
- 2 B 200 2 201 300
- 3 C 100 3 301 400
- 4 D 400 4 401 501
Display empnames and their grades:
Select e.ename,g.grade From emp e, salgrade g Where e.sal between g.lowsal and g.highsal
Display the names of employee whos grade=3
Select e.ename From emp e,salgrade g Where e.sal Between g.lowsal and g.highsal And g.grade=3 .
Display ename,dname,grade
Select e.ename,.dname,g.grade From emp e,dept d,salgrade g Where e.deptno=d.dno And e.sal between g.lowsal and g.highsal
Note: {here we are used both equi & non-equi joins}
Student
Sno sname s1 s2 s3
result
Result Lavg Havg
Dist 70 100
First 60 69.99
Second 50 59.99
Third 35 49.99
Display sname & result :
Select s.sname ,r.result From student s ,result r Where (s.s1+s.s2+s.s3)/3 between r.Lavg and r.Havg.
ANSI :-
Select e.ename, g.grade From emp e inner join g.lowsal and g.highsal.