sql - 带有左连接条件的sql查询

Question

下面是一个 SQL 查询：

SELECT ID, NAME FROM TABLE1 A
LEFT JOIN TABLE2 B
ON A.COLUMN1=B.COLUMN1
WHERE (
A.NAME = 'TEST'
OR
(NOT EXISTS (SELECT * FROM TABLE3 C 
WHERE C.COLUMN1=A.COLUMN2
AND C.COLUMN2=B.COLUMN2)
AND 
NOT EXISTS (SELECT * FROM TABLE4 D 
WHERE D.COLUMN1=A.COLUMN2
AND D.COLUMN2=B.COLUMN2)));

目前似乎不支持，是否有不同的方法？

我已经尝试过的替代方案，但给出了不同的结果：

A.COLUMN2 NOT IN (SELECT DISTINCT COLUMN1 FROM TABLE3) 

标签： sqlpostgresql

Answer 1

您可以尝试重构以使用左反连接而不是存在子查询：

SELECT ID, NAME
FROM TABLE1 A
LEFT JOIN TABLE2 B
    ON A.COLUMN1 = B.COLUMN1
LEFT JOIN TABLE3 C
    ON C.COLUMN1 = A.COLUMN2 AND
       C.COLUMN2 = B.COLUMN2
LEFT JOIN TABLE4 D 
    ON D.COLUMN1 = A.COLUMN2 AND
       D.COLUMN2 = B.COLUMN2
WHERE
    A.NAME = 'TEST' AND
    C.COLUMN1 IS NULL AND
    D.COLUMN1 IS NULL;