Non Equi Join in SQL Server – SQL Server Tutorial

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.

Related Projects

Leave a Reply

Your email address will not be published. Required fields are marked *