sql - 从一个表中选择在第二个表中没有相关条目的行

Question

这是我使用 PostgreSQL 9.4 使用 SQL 的第一天，我对一些事情感到迷茫。我认为我已经接近但还不够:

表定义:

CREATE TABLE DOCTOR (
    Doc_Number INTEGER,
    Name    VARCHAR(50) NOT NULL,
    Specialty   VARCHAR(50) NOT NULL,
    Address VARCHAR(50) NOT NULL,
    City    VARCHAR(30) NOT NULL,
    Phone   VARCHAR(10) NOT NULL,
    Salary  DECIMAL(8,2) NOT NULL,
    DNI     VARCHAR(10) UNIQUE
    CONSTRAINT pk_Doctor PRIMARY KEY (Doc_Number)
  );

CREATE TABLE VISIT (
    Doc_Number    INTEGER,
    Pat_Number    INTEGER,
    Visit_Date    DATE,
    Price           DECIMAL(7,2),
    CONSTRAINT Visit_pk PRIMARY KEY (Doc_Number, Pat_Number, Visit_Date),
    CONSTRAINT Visit_Doctor_fk FOREIGN KEY (Doc_Number) REFERENCES DOCTOR(Doc_Number),
    CONSTRAINT Visit_PATIENT_fk FOREIGN KEY (Pat_Number) REFERENCES PATIENT(Pat_Number)
  );

我需要如何将这两个查询合并为一个:

SELECT d.City, d.Name
FROM DOCTOR d, VISIT v
WHERE d.Specialty = 'family and comunity' 
ORDER BY d.Name;

SELECT * FROM VISIT
WHERE DATE (Visit_Date)<'01/01/2012' 
            OR DATE(Visit_Date)>'31/12/2013';

我试过类似的方法，但它不起作用。我需要2012年和2013年没有来过的那个专业的医生。

SELECT City, Name
FROM DOCTOR d
WHERE d.Specialty = 'family and comunity' 
      AND NOT IN(SELECT *
        FROM VISIT 
        WHERE Visit_Date BETWEEN '2012-01-01' and '2013-12-31')
ORDER BY d.Name;

有人能帮忙吗？

Answer 1

替代 LEFT JOIN ... WHERE NULL 结构的是普通的 WHERE NOT EXISTS(...) 反连接。 [完全等同于erwin的查询]

SELECT d.name, d.city
FROM   doctor  d
WHERE  d.specialty = 'family and community'
AND NOT EXISTS (
    SELECT 13
    FROM visit v WHERE v.doc_number = d.doc_number
                   AND v.visit_date BETWEEN '2012-01-01' AND '2013-12-31'
    )
ORDER  BY d.name;