多表連接查詢

– 显示所有员工的姓名,名门名称,部门所在地址
– 员工如果分配的部门就显示,如果没分配部门就显示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.epartment_id = d.id);