my sql

My sql work bench

inner join,
self join, : from same tables
outer join

inner: all the matching information from two tables
left outer: all the information from left table+ matching information from right table


select e.emp_id, e.hire date ,m.lastname
from emp e left outer join location m              equi join
on e.empid=m.location id
and e.lastname=;xxx'


select e.emp_id, e.hire date ,m.lastname
from emp e left outer join emp m
on e.empid  >  m.location id                               non equi join
and e.lastname=;xxx'



group by:

avg, wont count null, so use nvl


select max(salary), deptid,jobid,count(jobid)
from employee
groupby deptid,jobid
orderby deptid

having max(salary)>6000