我正在学习 SQL 并尝试使用简单的数据库。但是我有一个查询问题。我有两张表部门和员工。
CREATE TABLE DEPARTMENT
(
ID INTEGER PRIMARY KEY,
NAME VARCHAR(14),
LOCATION VARCHAR(13)
);
CREATE TABLE EMPLOYEE
(
ID INTEGER PRIMARY KEY,
FIRSTNAME VARCHAR(10),
LASTNAME VARCHAR(10),
MIDDLENAME VARCHAR(10),
POSITION VARCHAR(9),
MANAGER INTEGER,
HIREDATE DATE,
SALARY DOUBLE,
DEPARTMENT INTEGER,
CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPARTMENT) REFERENCES DEPARTMENT (ID)
);
如果有,我需要选择所有员工和相应的部门名称。我使用下一个查询。
select lastname, salary, department.name as depname
from employee, department
where employee.department = department.id or employee.department is null
结果,我有一名员工在 db 中没有对应任何部门,他应该在部门区域为“null”,但他有部门。我不知道为什么会发生。
"CLARK - 2450.0 - ACCOUNTING",
"MILLER - 1300.0 - ACCOUNTING",
"ALLEN - 1600.0 - SALES",
"BLAKE - 2850.0 - SALES",
"TURNER - 1500.0 - SALES",
"SMITH - 800.0 - RESEARCH",
"ADAMS - 1100.0 - RESEARCH",
"SCOTT - 3000.0 - RESEARCH",
"WARD - 1250.0 - SALES",
"KING - 5000.0 - null", in this area I have not null
"MARTIN - 1250.0 - SALES",
"FORD - 3000.0 - RESEARCH",
"JONES - 2975.0 - RESEARCH",
"JAMES - 950.0 - SALES"
请帮助我:) 谢谢你,对不起我的英语不好。
你想JOIN
在一般情况下使用。总是。
特别是在这种情况下,您需要LEFT JOIN
:
select e.lastname, e.salary, d.name as depname
from employee e left join
department d
on e.department = d.id;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句