dokuwiki

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)

Join über mehrere Tabellen hinweg

SELECT
    first_name,
    last_name,
    coalesce(department_name, '[kein department]'),
    city,
    country_name,
    region_name
FROM
        hr.employees
 left   JOIN hr.departments  ON employees.department_id = departments.department_id
 left   join hr.locations    on departments.location_id = locations.location_id
 left   join hr.countries    on locations.country_id = countries.country_id
 left   join hr.regions      on countries.region_id = regions.region_id
 where region_name = 'Europe'

SELECT
    first_name,
    last_name,
    e.department_id     aktuelle_department_id,
    jh.department_id    jh_department_id,
    e.job_id            aktuelle_job_id,
    jh.job_id           jh_job_id,
    start_date,
    end_date
FROM
         employees e left JOIN job_history jh          
         ON e.employee_id = jh.employee_id
ORDER BY
    coalesce(e.employee_id, jh.employee_id)