-- 显示所有员工的姓名,名门名称,部门所在地址

-- 员工如果分配的部门就显示,如果没分配部门就显示null

CREATE TABLE employee

(

  ID NUMBER(4),

  NAME VARCHAR2(15),

  department_id NUMBER(2)

);

INSERT INTO employee(ID, NAME, department_id) VALUES(1, 'NameA', 1);

INSERT INTO employee(ID, NAME, department_id) VALUES(2, 'NameB', 1);

INSERT INTO employee(ID, NAME, department_id) VALUES(3, 'NameC', 2);

INSERT INTO employee(ID, NAME, department_id) VALUES(4, 'NameD', 3);

INSERT INTO employee(ID, NAME, department_id) VALUES(5, 'NameA', NULL);

CREATE TABLE department

(

  ID NUMBER(2),

  NAME VARCHAR2(15),

  location_id NUMBER(2)

);

INSERT INTO department(ID, NAME, location_id) VALUES(1, 'DepartmentA', 1);

INSERT INTO department(ID, NAME, location_id) VALUES(2, 'DepartmentB', 2);

INSERT INTO department(ID, NAME, location_id) VALUES(3, 'DepartmentC', 3);

CREATE TABLE LOCATION

(

  ID NUMBER(2),

  province VARCHAR2(10),

  city VARCHAR2(10),

  address VARCHAR2(50)

);

INSERT INTO LOCATION(ID, province, city, address) VALUES(1, 'ProvinceA', 'CityA', 'AddressA');

INSERT INTO LOCATION(ID, province, city, address) VALUES(2, 'ProvinceB', 'CityB', 'AddressB');

INSERT INTO LOCATION(ID, province, city, address) VALUES(3, 'ProvinceC', 'CityC', 'AddressC');

SELECT e.name, d.name, l.address

FROM department d INNER JOIN LOCATION l ON(d.location_id = l.id)

RIGHT OUTER JOIN employee e ON(e.department_id = d.id);