dokuwiki

This is an old revision of the document!

---

ON

select first_name, last_name, department_name from employees join departments
on employees.department_id = departments.department_id
order by last_name, first_name; 

WHERE
select first_name, last_name, department_name from employees, departments
where employees.department_id = departments.department_id
order by last_name, first_name; 

USING
select first_name, last_name, department_name from employees join departments
using (department_id)
order by last_name, first_name;

LEFT/RIGHT/OUTER
select first_name, last_name, department_name from employees, departments
where employees.department_id = departments.department_id(+)
order by department_name desc, last_name, first_name; 

select first_name, last_name, department_name from employees full outer join departments
on employees.department_id = departments.department_id
order by department_name desc, last_name, first_name; 

SELECT EMPLOYEES.FIRST_NAME, 
       EMPLOYEES.LAST_NAME, 
       DEPARTMENTS.DEPARTMENT_NAME, 
       LOCATIONS.CITY 
FROM HR.EMPLOYEES left outer join HR.DEPARTMENTS on 
DEPARTMENTS.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
left outer join  HR.LOCATIONS  on
  LOCATIONS.LOCATION_ID = DEPARTMENTS.LOCATION_ID
order by department_name desc

SELF JOIN
select e.first_name, e.last_name, e.employee_id,  
       m.first_name, m.last_name, m.employee_id from employees e, employees m
       where 
       e.employee_id(+) = m.manager_id
order by   m.employee_id     

SELECT EMPLOYEES.FIRST_NAME, 
       EMPLOYEES.LAST_NAME, 
       MANAGER.FIRST_NAME "MANAGER FIRST_NAME", 
       MANAGER.LAST_NAME  "MANAGER LAST_NAME" 
FROM HR.EMPLOYEES right join EMPLOYEES MANAGER 
on ( (EMPLOYEES.MANAGER_ID = MANAGER.EMPLOYEE_ID) )
where employees.employee_id is null
(89 rows)