mysql - 具有外部约束的sql查询

Question

按照教科书“SQL in a Nutshell”的要求进行查询

For the employees with an email address display their age and gender.

在以下数据库上:

CREATE TABLE EMPLOYEES(
   PID SMALLINT NOT NULL,
   PRIMARY KEY(PID),
   CONSTRAINT EMPLOYEES_FK FOREIGN KEY (PID) REFERENCES PERSONS (PID)
);

CREATE TABLE PERSONS(
 PID SMALLINT NOT NULL,
 NAME VARCHAR(20) NOT NULL,
 GENDER CHAR(1) NOT NULL CHECK (GENDER IN ('M','F')),
 AGE SMALLINT NOT NULL CHECK (AGE BETWEEN 0 AND 120),
 BIRTHDATE DATE NOT NULL,
 PRIMARY KEY(PID)
);

CREATE TABLE HASE(
 PID SMALLINT NOT NULL,
 EADDR VARCHAR(30) NOT NULL,
 PRIMARY KEY(PID,EADDR),
 CONSTRAINT HASE_FK FOREIGN KEY (PID) REFERENCES PERSONS (PID)
);

我对 SQL 查询非常陌生，所以我的尝试是:

SELECT GENDER, AGE
FROM PERSONS AND EMPLOYEES
WHERE EXISTS HASE.EADDR
;

但我不认为我与外键的关系正确。我意识到这是非常基本的，但我认为一旦我了解了查询的方式，它将对我处理其他问题有所帮助。

Answer 1

您可以使用 EXISTS 来获取结果

SELECT Gender, Age
FROM Persons
  JOIN Employees ON Persons.PID = Employees.PID
WHERE Exists (SELECT PID
              FROM Hase
              WHERE Persons.PID = Hase.PID)

您也可以使用 IN，但 EXISTS 查询会更快

SELECT Gender, Age
FROM Persons
  JOIN Employees ON Persons.PID = Employees.PID
WHERE Persons.PID IN  (SELECT PID
                       FROM Hase)