SQL查询异常：所有员工被分配至每个项目的每个岗位问题排查

我需要生成一个查询结果，列出所有拥有岗位（WonJob不为空）的员工对应的项目名称、姓名、入职日期，并按入职日期升序排序。但当前的查询错误地将所有员工显示在每个项目的每个岗位下，而非他们实际任职的岗位。

CREATE TABLE DEPARTMENT (
  DeptNo CHAR(4) NOT NULL,
  DeptName VARCHAR(25) NOT NULL,
  DeptLocation VARCHAR(30) NULL,
  PRIMARY KEY (DeptNo));

CREATE TABLE EMPLOYEE  (
  EmpNo INTEGER NOT NULL, 
  EmpFirstName VARCHAR(20) NOT NULL,
  EmpLastName VARCHAR(20) NOT NULL,
  DeptNo CHAR(4) NULL,
  PRIMARY KEY (EmpNo),
  FOREIGN KEY (DeptNo) REFERENCES DEPARTMENT (DeptNo));

CREATE TABLE PROJECT (
  ProjNo CHAR(4) NOT NULL,
  ProjName VARCHAR(15) NOT NULL,
  ProjBudget DECIMAL(12,2) NULL,
  PRIMARY KEY (ProjNo));

CREATE TABLE WORKSON (
  EmpNo INTEGER NOT NULL,
  ProjNo CHAR(4) NOT NULL,
  WonJob VARCHAR (15) NULL,
  WonEnterDate DATE NULL,
  PRIMARY KEY (EmpNo, ProjNo),
  FOREIGN KEY (EmpNo) REFERENCES EMPLOYEE (EmpNo),  
  FOREIGN KEY (ProjNo) REFERENCES PROJECT (ProjNo));

INSERT INTO EMPLOYEE VALUES(25348, 'Matthew', 'Smith','d3');
INSERT INTO EMPLOYEE VALUES(10102, 'Ann', 'Jones','d3');
INSERT INTO EMPLOYEE VALUES(18316, 'John', 'Barrimore', 'd1');
INSERT INTO EMPLOYEE VALUES(29346, 'James', 'James', 'd2');
INSERT INTO EMPLOYEE VALUES(9031, 'Elsa', 'Bertoni', 'd2');
INSERT INTO EMPLOYEE VALUES(2581, 'Elke', 'Hansel', 'd2');
INSERT INTO EMPLOYEE VALUES(28559, 'Sybill', 'Moser', 'd1');

INSERT INTO DEPARTMENT VALUES ('d1', 'research','Dallas');
INSERT INTO DEPARTMENT VALUES ('d2', 'accounting', 'Seattle');
INSERT INTO DEPARTMENT VALUES ('d3', 'marketing', 'Dallas');

INSERT INTO PROJECT VALUES ('p1', 'Apollo', 120000.00);
INSERT INTO PROJECT VALUES ('p2', 'Gemini', 95000.00);
INSERT INTO PROJECT VALUES ('p3', 'Mercury', 186500.00);

INSERT INTO WORKSON VALUES (10102,'p1', 'analyst', '2006.10.1');
INSERT INTO WORKSON VALUES (10102, 'p3', 'manager', '2008.1.1');
INSERT INTO WORKSON VALUES (25348, 'p2', 'clerk', '2007.2.15');
INSERT INTO WORKSON VALUES (18316, 'p2', NULL, '2007.6.1');
INSERT INTO WORKSON VALUES (29346, 'p2', NULL, '2006.12.15');
INSERT INTO WORKSON VALUES (2581, 'p3', 'analyst', '2007.10.15');
INSERT INTO WORKSON VALUES (9031, 'p1', 'manager', '2007.4.15');
INSERT INTO WORKSON VALUES (28559, 'p1', NULL, '2007.8.1');
INSERT INTO WORKSON VALUES (28559, 'p2', 'clerk', '2008.2.1');
INSERT INTO WORKSON VALUES (9031, 'p3', 'clerk', '2006.11.15'); 
INSERT INTO WORKSON VALUES (29346, 'p1','clerk', '2007.1.4');

SELECT WORKSON.WonJob, PROJECT.ProjName, EMPLOYEE.EmpFirstName, EMPLOYEE.EmpLastName, WORKSON.WonEnterDate
    FROM WORKSON, PROJECT, EMPLOYEE
    WHERE WonJob IS NOT NULL
    ORDER BY WonEnterDate ASC

查询中未指定表之间的关联条件，导致三个表进行了笛卡尔积连接——每个WORKSON记录都和所有PROJECT、EMPLOYEE记录匹配，因此出现了所有员工对应所有项目岗位的错误结果。

推荐使用显式JOIN语法（可读性更强）：

SELECT 
    w.WonJob,
    p.ProjName,
    e.EmpFirstName,
    e.EmpLastName,
    w.WonEnterDate
FROM WORKSON w
JOIN EMPLOYEE e ON w.EmpNo = e.EmpNo
JOIN PROJECT p ON w.ProjNo = p.ProjNo
WHERE w.WonJob IS NOT NULL
ORDER BY w.WonEnterDate ASC;

也可以使用传统的WHERE关联语法：

SELECT 
    WORKSON.WonJob,
    PROJECT.ProjName,
    EMPLOYEE.EmpFirstName,
    EMPLOYEE.EmpLastName,
    WORKSON.WonEnterDate
FROM WORKSON, EMPLOYEE, PROJECT
WHERE WORKSON.EmpNo = EMPLOYEE.EmpNo
  AND WORKSON.ProjNo = PROJECT.ProjNo
  AND WORKSON.WonJob IS NOT NULL
ORDER BY WORKSON.WonEnterDate ASC;

两种写法都会正确关联员工、项目和任职记录，只返回员工实际任职的岗位信息，并按入职日期升序排序。

内容的提问来源于stack exchange，提问作者William