-- 显示所有员工的姓名,名门名称,部门所在地址 -- 员工如果分配的部门就显示,如果没分配部门就显示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);
|
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17013648/viewspace-1150598/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17013648/viewspace-1150598/